VBA If statements

[Excel_Blonde]

Hi I'm a very new self teacher of VBA so this is probably a very easy problem to solve.

I'm trying to write a code that will fill column B depending on the text in column A.

For example:

This	That
When	Why
This	That
Where	Who
When	Why

<tbody>
</tbody>

So if This is Found in Column A, Column B will fill with That, if Where is found in Column A, column B will fill with Who etc.

I can get it work for 1, but not for multiples.

Any help would be greatly appreciated.

 

[gallen]

Something like this would work although it's straight forward enough to do with a formula too

Sub DoIf()
    Dim c As Range, s As String
    
    'loop through all used cells in column A from cell A2 downwards
    For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Select Case c.Value
            Case "This"
                s = "That"
            Case "When"
                s = "Why"
            Case "Where"
                s = "Who"
            Case Else
                s = "Unknown"
        End Select
        c.Offset(0, 1) = s
    Next c
End Sub

 

[Excel_Blonde]

Thank you, i'll give it a go and report back

 

[Excel_Blonde]

Something like this would work although it's straight forward enough to do with a formula too

Sub DoIf()
    Dim c As Range, s As String
    
    'loop through all used cells in column A from cell A2 downwards
    For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Select Case c.Value
            Case "This"
                s = "That"
            Case "When"
                s = "Why"
            Case "Where"
                s = "Who"
            Case Else
                s = "Unknown"
        End Select
        c.Offset(0, 1) = s
    Next c
End Sub

That works perfectly, thank you very much.

 

[gallen]

Very welcome