Replacement
 

Hi, If the number in column 3 is 3 and the 1st digit of the number i
column 5 is 2 I have to replace this with 1
the following is an excerpt of my code which isnt working, Many Thanks

For i = 1 To lastrow
If cells(i,3) = 3 and left(Cells(i, 5),1) = 1 Then
Cells(i,5) = Replace(Cells(i,5), 1, 1, "1")
'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=56373

Replacement
 

Cells(i, 5) = "1" & Mid(Cells(i, 5), 2)


"T De Villiers" wrote:


Hi, If the number in column 3 is 3 and the 1st digit of the number in
column 5 is 2 I have to replace this with 1
the following is an excerpt of my code which isnt working, Many Thanks

For i = 1 To lastrow
If cells(i,3) = 3 and left(Cells(i, 5),1) = 1 Then
Cells(i,5) = Replace(Cells(i,5), 1, 1, "1")
'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=563734

Replacement
 

Hi,

Try the following:


Sub test()
For i = 1 To 10 <<<Change to lastrow for your needs
If Cells(i, 3) = 3 And Left(Cells(i, 5), 1) = 2 Then
Cells(i, 5).Replace What:="2", Replacement:="1",
LookAt:=xlPart
End If
Next
End Sub

Please note that if column 5 contains an number such as 212345 both the
2's will be replaced but you should be able to amend the code to suit.

Regards

Michael beckinsale

Replacement
 

Sorry, there was a problem I didn't see. Let me do the whole loop for you:

For i = 1 To lastrow
If Cells(i, 3) = 3 and left(Cells(i, 5), 1) = "2" Then
Cells(i, 5) = "1" & Mid(Cells(i, 5), 2)
End If
Next i


"Charlie" wrote:

Cells(i, 5) = "1" & Mid(Cells(i, 5), 2)


"T De Villiers" wrote:


Hi, If the number in column 3 is 3 and the 1st digit of the number in
column 5 is 2 I have to replace this with 1
the following is an excerpt of my code which isnt working, Many Thanks

For i = 1 To lastrow
If cells(i,3) = 3 and left(Cells(i, 5),1) = 1 Then
Cells(i,5) = Replace(Cells(i,5), 1, 1, "1")
'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=563734