change cell value depending on current value

[ghynes]

i have a multiple rows of data and i need to loop through every B cell and change the value depending on the following criteria. can anyone help with a simple macro code for this?
thanks

if B1 = car1 rename to Toyota
if B1 = car2 rename to Ford
if B1 = car3 rename to Opel
if B1 = car4 rename to Nissan

and then i need to loop through every B cell until the end of the rows.

 

[Peter_SSs]

Looping is a relatively slow process. See if this does what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Replace_Text()<br>    <SPAN style="color:#00007F">Dim</SPAN> Cars<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Cars = Array("car1", "Toyota", "car2", "Ford", "car3", "Opel", "car4", "Nissan")<br>    <SPAN style="color:#00007F">With</SPAN> Columns("B")<br>        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Cars) - 1 <SPAN style="color:#00007F">Step</SPAN> 2<br>            .Replace What:=Cars(i), Replacement:=Cars(i + 1), LookAt:=xlWhole, _<br>                MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, ReplaceFormat:=False<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ghynes]

thanks for the help. i think i mite use the following code;

Set myrange = range("B1:B100")
 
    For Each Cell In myrange
    
    If Cell.Value = "car1" Then
       Cell.Value = "Toyota"
    End If
        
    If Cell.Value = "car2" Then
       Cell.Value = "Ford"
    End If

    If Cell.Value = "car3" Then
       Cell.Value = "Opel"
    End If
       If Cell.Value = "car4" Then
       Cell.Value = "Nissan"
    End If

Next 
End Sub

but i need help with determining the range as the number of rows change every day.
do you know of a way i can set the range to the exact number of rows in the data?

 

[ghynes]

think i figured out how to do this.

Dim myrange As range

Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set myrange = range("B1:B" & Lastrow)
    For Each Cell In myrange

seems to work. hoping its right anyhow

 

[Peter_SSs]

Say you have 100 rows. Your code will have to do 400 processes. Did you try my code that will need just 4? Can't really see why you would deliberately choose a method that requires 100 times the work!

 

[ghynes]

indeed. point taken. im just learning. thanks again