Looping VBA with Substituting Format

[Read_This]

I wonder if you can help.

I'm looking for some simple VBA to allow me to loop through column A in a worksheet. If the cell in column A contains any data, then format its adjacent cell in column B.
Also, should that adjacent cell in column B 'not' have any data to format, then continue to loop through column A until the cells in column A are found empty - then stop.


The format I am looking for in column B is
=IF(A1="",TEXT(RIGHT(SUBSTITUTE($B1," ",""),10),"00000 000000"),TEXT(RIGHT(SUBSTITUTE($B1," ",""),10),"00000 000000"))



[this will format a number into a 12 digit UK telephone number that begins with a zero and has a space after the 5th digit]

Help and pointers would be appreciate.

 

[Rick Rothstein]

I think this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub FormatColumnBifColumnAhasData()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("IF(LEN(" & .Address & "),TEXT(RIGHT(SUBSTITUTE(#,"" "",""""),10),""00000 000000""),IF(#="""","""",#))", "#", .Offset(, 1).Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Read_This]

Perfect - thank you!