Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value Replacement | Excel Worksheet Functions | |||
#N/A Replacement | Excel Discussion (Misc queries) | |||
Replacement | Excel Programming | |||
Replacement | Excel Programming | |||
Replacement CD | Excel Programming |