Multiple VBA Statements

[DrumLad]

Hi,

I am new to VBA and would like someone to help me on this little query?

I need an IF statement using VBA that looks at the following cells in col "A" and creates the result in col "C":

If cell A1 = "Tokyo", then "East" , A2 = "London", then "West" A3 = "Johannesburg" then "South" A4 = "Oslo" then "North"

Of course this would be part of a large sheet with maybe over 2000 lines so the macro would need to loop down until it reaches the last line....

I really would appreciate if someone can do this for me and explain what they did so I can understand the logic!

 

[Scott T]

Try

Sub drumlad()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row 'finds the last row used in column A
For x = 1 To lr 'starts loop from 1 to lr
Select Case UCase(Cells(x, "A")) 'test what the cell has and enter the text in column C
    Case UCase("TOKYO")
        Cells(x, "C") = "East"
    Case UCase("LONDON")
        Cells(x, "C") = "West"
    Case UCase("JOHANNESBURG")
        Cells(x, "C") = "South"
    Case UCase("OSLO")
        Cells(x, "C") = "North"
        
        
End Select
Next x

End Sub

 

[JackDanIce]

Try:

Sub Directions()

    Dim arr()   As Variant
    Dim x       As Long
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(1, 1).Resize(x).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        Select Case Lcase$(arr(x, 1))
            Case Is = "tokyo"
                arr(x, 1) = "East"
            Case Is = "london"
                arr(x, 1) = "West"
            Case Is = "johannesburg"
                arr(x, 1) = "South"
            Case Is = "oslo"
                arr(x, 1) = "North"
            Case Else
                arr(x, 1) = arr(x, 1) & " city not defined"
        End Select
    Next x
    
    Cells(1, 3).Resize(x).Value = arr
    Erase arr
    
End Sub

Using x to define last row in column A that contains data
Reading range A1:Ax into an array
Loop over array, use SELECT CASE to define direction based on city, over-write city with direction or suitable error message if city not found
Using LCASE to avoid distinction in city names if cases are not consistent.
Paste updated array into column C

 

[Fluff]

How about

Sub DrumLad()
   Dim ary As Variant
   Dim i As Long
   ary = Array("Tokyo", "East", "London", "West")
   For i = 0 To UBound(ary) Step 2
      With Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Replace ary(i), "=XXX", xlWhole, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).Offset(, 2).Value = ary(i + 1)
         .Replace "=XXX", ary(i), xlWhole, , False, , False, False
      End With
   Next i
End Sub

Simply add to the array with your other values, in a similar style

 

[DrumLad]

Hi,

I am new to VBA and would like someone to help me on this little query?

I need an IF statement using VBA that looks at the following cells in col "A" and creates the result in col "C":

If cell A1 = "Tokyo", then "East" , A2 = "London", then "West" A3 = "Johannesburg" then "South" A4 = "Oslo" then "North"

Of course this would be part of a large sheet with maybe over 2000 lines so the macro would need to loop down until it reaches the last line....

I really would appreciate if someone can do this for me and explain what they did so I can understand the logic!

Many guys for taking the trouble to help me - I now have a solution!

 

[DrumLad]

Hi, one other issue (since you guys rock!)

I need to concatenate 3 columns of data (no spaces in between them) into one column each period.
I would like to use a macro (button) that loops to the bottom of each column and presents the concatenated data in another column (again this is a large sheet so a macro would greatly improve the process for me!

Example:

Col A Col B Col C Col D

John Smith 54 JohnSmith54
Peter Jones 50 PeterJones50

and so on...........

 

[Scott T]

Try

Sub joincol()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

For X = 2 To lr 'assumes headers in row 1 and data starts in row 2
    Cells(X, "D") = Cells(X, "A") & Cells(X, "B") & Cells(X, "C") 
Next X

End Sub

 

[DrumLad]

That works a treat - thank you Scott T!!