Microsoft Excel - Change 0 to a 1 - Asked By Dan on 22-Mar-12 09:18 AM

Earn up to 10 extra points for answering this tough question.
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
D Company replied to Dan on 22-Mar-12 09:39 AM
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

Dan replied to D Company on 22-Mar-12 10:14 AM
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.
D Company replied to Dan on 22-Mar-12 10:22 AM
Ok.

here good examples are given , give a try to these solutions
http://www.rondebruin.nl/values.htm
Dan replied to D Company on 22-Mar-12 10:35 AM
Not what I am trying to do.  I do not want to copy and paste.  Thank you anyway.
Pichart Y. replied to Dan on 22-Mar-12 12:05 PM
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.

Parag Satpute replied to Dan on 24-Mar-12 06:55 AM
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
Parag Satpute replied to Dan on 24-Mar-12 06:59 AM
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.
Need Macro Help - Dan replied to Pichart Y. on 24-Mar-12 12:15 PM
Thank you all.  Prichart Y, the code is working for me.  I really appreciate everyones help.