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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]This[/TD]
[TD]That[/TD]
[/TR]
[TR]
[TD]When[/TD]
[TD]Why[/TD]
[/TR]
[TR]
[TD]This[/TD]
[TD]That[/TD]
[/TR]
[TR]
[TD]Where[/TD]
[TD]Who[/TD]
[/TR]
[TR]
[TD]When[/TD]
[TD]Why[/TD]
[/TR]
</tbody>[/TABLE]

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