Microsoft Excel - Change 0 to a 1

I want to change any cell in C:C that is a 0 to a 1.  I have the code that find the last row with data, but I am having problems figuring out how to change any cell in C:C with a 0 to a 1.  Any help will be appreciated.

Dan

Give a try to this VBA code

Private Sub Cell_Change(ByVal Target As Range)
  
If Target.Cells.Value==0
  Then 
Target.Cells.Value=1
End If
End  Sub

It does not work.  I would prefer not to use a private sub.  I guess it is not working because it does not know the range, which is Range("C1:C" & Lastrow).  I just need to figure out how to change this range to 1's only if there are zero's in the cells.

Ok.

here good examples are given , give a try to these solutions
http://www.rondebruin.nl/values.htm

Not what I am trying to do.  I do not want to copy and paste.  Thank you anyway.

Hi Dan,

Try this code...

• Sub Replace0with1()
  lastRow = Range("A" & Rows.Count).End(xlUp).Row
  For Each x In Range("C1:C" & lastRow)
  If x.Value = 0 Then
  x.Offset(0, 0) = 1
  End If
  Next x
  MsgBox "Done"
  End Sub
• sample data -->AddDate.zip

Hope this help.

pichart Y.

This is the most simple and short code yet very speedy to achieve your goal, This will check the last row and then check and replace all Zero's to One in Column C,

Sub changeToZero()
Dim counter As Double

counter = ActiveSheet.Range("C1048576").End(xlUp).Row

For i = 1 To counter
    If Range("C" & i).Value = 0 Then
      Range("C" & i).Value = 1
    End If
Next i
End Sub

This is the most simple and short code yet very speedy to achieve your goal, This will check the last row and then check and replace all Zero's to One in Column C,

Sub changeToZero()
Dim counter As Double

counter = ActiveSheet.Range("C1048576").End(xlUp).Row

For i = 1 To counter
    If Range("C" & i).Value = 0 Then
    Range("C" & i).Value = 1
    End If
Next i
End Sub

Please Note:
'If you are working on Excel 2003 then change 1048576 to 65535 else on Excel 2003 you might get an error as the row limitation of Excel 2003 is 65535.

Thank you all.  Prichart Y, the code is working for me.  I really appreciate everyones help.