Concatenating a zero
 

Hi,

Writiing a macro which goes through each cell 9they are numbers) i
column 5, if the length of the cell is 1 a zero is added to the start

Below is an excerpt of the code, not quite working, many thks

Dim i
lastrow = Range("A50000").End(xlUp).Row
For i = 1 To lastrow
If Len(Cells(i, 5)) = 1 Then
Cells(i, 5) = "0" & Cells(i, 5)
End If
Nex

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56371

Concatenating a zero
 

Sub sdfsdf()
Dim i
lastrow = Range("A50000").End(xlUp).Row
For i = 1 To lastrow
If Len(CStr(Cells(i, 1))) = 1 Then
Cells(i, 1).NumberFormat = "@"
Cells(i, 1) = CStr("0" & Cells(i, 1))
End If
Next
End Su

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=56371

Concatenating a zero
 

this worked for me

Sub test()
Dim i
Dim lastrow As Long
lastrow = Range("A50000").End(xlUp).Row
For i = 1 To lastrow
If Len(Cells(i, 5)) = 1 Then
Cells(i, 5).Value = "0" & Cells(i, 5).Value
End If
Next

End Sub

--


Gary


"T De Villiers"
wrote in message
news:T.De.Villiers.2bauo4_1153492208.9364@excelfor um-nospam.com...

Hi,

Writiing a macro which goes through each cell 9they are numbers) in
column 5, if the length of the cell is 1 a zero is added to the start.

Below is an excerpt of the code, not quite working, many thks

Dim i
lastrow = Range("A50000").End(xlUp).Row
For i = 1 To lastrow
If Len(Cells(i, 5)) = 1 Then
Cells(i, 5) = "0" & Cells(i, 5)
End If
Next


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=563715

Concatenating a zero
 

Thanks a lot Matt


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=563715

Concatenating a zero
 

Thanks a lot Matt


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=563715

Concatenating a zero
 

Thanks a lot Matt


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=563715

Concatenating a zero
 

Thanks a lot Matt


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=563715