none
Changing all "0" to "1" but keeping "10, 20, 30" etc as "10, 20, 30"

    Question

  • Hi All,

    This may seem like a silly question and there may be a simple solution but I have the following Data:

    Carton Quantity

    0

    1

    25

    40

    30

    168

    400

    1240

    0

    0

    976

    90

    Is there a bit of code that I could write that would change the "0" to "1" but not change the other Zero's? There are 60,000 lines of data and using the find & replace function didn't work as it changed all of the "0" to "1".

    I initially thought of using the following statement: If(A45=0,1,) but that didn't work.

    Thanks!

    Thursday, December 12, 2013 2:14 PM

Answers

  • You can use a macro - here is a quick and dirty one that checks every cell in column a from 1-60 and, if it isn't empty, and it's value is 0, changes it to 1:

    Sub ChangeData()
    For i = 1 To 60
        If Cells(i, 1).Value <> vbNullString Then
            If Cells(i, 1).Value = 0 Then Cells(i, 1).Value = 1
        End If
    Next i
    End Sub

    Good luck!

    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:17 PM
    Thursday, December 12, 2013 2:56 PM
  • I tested that macro in Excel 2010 and it seems to work for me.

    Now: for column AO you would have to change the macro's Cell references from 'Cell(i,1).Value' to 'Cell(i,41).Value' since AO is the 41st column - did you do that?

    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:17 PM
    Thursday, December 12, 2013 3:11 PM
  • By dirty I meant it may not be an exact solution to your particular problem and may require refinement. I did add the line checking for empty cells to prevent it from filling them with 1's, but it doesn't do any other state or error checking, so be careful in case your data set has any odd features hidden in the middle of it!
    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:34 PM
    Thursday, December 12, 2013 3:33 PM

All replies

  • You can use a macro - here is a quick and dirty one that checks every cell in column a from 1-60 and, if it isn't empty, and it's value is 0, changes it to 1:

    Sub ChangeData()
    For i = 1 To 60
        If Cells(i, 1).Value <> vbNullString Then
            If Cells(i, 1).Value = 0 Then Cells(i, 1).Value = 1
        End If
    Next i
    End Sub

    Good luck!

    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:17 PM
    Thursday, December 12, 2013 2:56 PM
  • Hi Bud,

    I couldn't get that code to work. At the moment the data is in Cell AO. There is data before and after that. I tried to assign that to a button but I wasn't able to get it to work.

    Thanks!

    Thursday, December 12, 2013 3:02 PM
  • You may want to check that you have macros enabled. You do have to create a macro with the code you want to use before you can run it, in case you haven't done that...

    What version of Excel are you using?

    Thursday, December 12, 2013 3:05 PM
  • Macros are enabled Bud. I'm using Excel 2010.

    Thursday, December 12, 2013 3:07 PM
  • I tested that macro in Excel 2010 and it seems to work for me.

    Now: for column AO you would have to change the macro's Cell references from 'Cell(i,1).Value' to 'Cell(i,41).Value' since AO is the 41st column - did you do that?

    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:17 PM
    Thursday, December 12, 2013 3:11 PM
  • Got it working :). 

    • Edited by ExcelNoob89 Thursday, December 12, 2013 3:19 PM
    Thursday, December 12, 2013 3:17 PM
  • Great! (I did say it was quick AND dirty!)

    Thursday, December 12, 2013 3:23 PM
  • When you say dirty what do you mean? The data pretty much looks correct! 
    Thursday, December 12, 2013 3:23 PM
  • By dirty I meant it may not be an exact solution to your particular problem and may require refinement. I did add the line checking for empty cells to prevent it from filling them with 1's, but it doesn't do any other state or error checking, so be careful in case your data set has any odd features hidden in the middle of it!
    • Marked as answer by ExcelNoob89 Thursday, December 12, 2013 3:34 PM
    Thursday, December 12, 2013 3:33 PM
  • Hi,

    No it's worked great! Every cell has atleast a "0" in it. There are no blanks what so ever :) but it works great! Thanks a million!

    Thursday, December 12, 2013 3:34 PM