Better way to change the value in a column rather than loop

[vonguyenphu]

Hi all,

Can i change the value of columns (>10000 cells) rather than loops which slow my macro.
I need to add "#" to each cell in first column, and in second one i need to add "000" or "00" to increase the number of letter to 5
eg: 567 =>#567 (first column)
20 =>00020(second column)
300 => 00300(second column)
Thks all!

 

[Rick Rothstein]

See if this macro does what you want...

Sub FixNumberInColumnsAandB()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("""#""&" & .Address)
    With .Offset(, 1)
      .NumberFormat = "@"
      .Value = Evaluate("IF({1},TEXT(" & .Address & ",""00000""))")
    End With
  End With
End Sub

 

[vonguyenphu]

Fascinating Mr Rothstein, thank you so much! That worked like a magic :D
I searched on google and saw a similar solution with evaluate method to concatenate 2 columns into third column, but i don't know how to solve my problem.
Can you give me more details or reference about how this evaluate method works. I searched on google but still not clear, escpecially how to place quote " inside the parentheses.