Macro Help - IF statement with OR and AND

[MrMatt]

Hi

I'm struggling to create a macro that will check each cell in Columns B and E and do the following:

if cell in column B = London or Washington or Paris or Brussels (there are dozens of cities but I won't list them all)!
and if cell in column E is blank then insert "text" into column E (effectively overwriting the blank 'value')
else leave the value in column E as it is

Any help greatly appreciated.

Thanks

Matt

 

[Comfy]

Rather than listing each City, would it also work if you said:

If each Cell in B is not blank and the cell in E is Blank insert a value into E?

And what would like like to be entered into E?

 

[Teeroy]

Try the following:

Sub Teeroy()
sCheckcities = UCase("London Washington Paris Brussels") 'add as required
For Each rng In Intersect(Columns("B"), ActiveSheet.UsedRange)
    If InStr(sCheckcities, UCase(rng.Value)) <> 0 And rng.Offset(0, 3) = "" Then
        rng.Offset(0, 3) = "Text"
    End If
Next
End Sub

 

[MrMatt]

Try the following:

Sub Teeroy()
sCheckcities = UCase("London Washington Paris Brussels") 'add as required
For Each rng In Intersect(Columns("B"), ActiveSheet.UsedRange)
    If InStr(sCheckcities, UCase(rng.Value)) <> 0 And rng.Offset(0, 3) = "" Then
        rng.Offset(0, 3) = "Text"
    End If
Next
End Sub

Thanks Teeroy, this looks like it should work. One question before I run the macro, is how would I handle a city like New York - should I put each city into it's own quote marks?

 

[Teeroy]

The Instr just matches a substring so you don't need to collect the two parts as a city. You may get a situation where if you had both "New York" and "York", York would match as a city. If this were a problem you could change the string to include "|" at each end as a separator e.g

Sub Teeroy()
sCheckcities = UCase("|London|Washington|Paris|Brussels|New York|") 'add as required
For Each rng In Intersect(Columns("B"), ActiveSheet.UsedRange)
    If InStr(sCheckcities, "|" & UCase(rng.Value) & "|") <> 0 And rng.Offset(0, 3) = "" Then
        rng.Offset(0, 3) = "Text"
    End If
Next
End Sub

 

[MrMatt]

The Instr just matches a substring so you don't need to collect the two parts as a city. You may get a situation where if you had both "New York" and "York", York would match as a city. If this were a problem you could change the string to include "|" at each end as a separator e.g

Sub Teeroy()
sCheckcities = UCase("|London|Washington|Paris|Brussels|New York|") 'add as required
For Each rng In Intersect(Columns("B"), ActiveSheet.UsedRange)
    If InStr(sCheckcities, "|" & UCase(rng.Value) & "|") <> 0 And rng.Offset(0, 3) = "" Then
        rng.Offset(0, 3) = "Text"
    End If
Next
End Sub

Thanks for the explanation. The macro works perfectly.

Appreciate your help on this.

 

[Teeroy]

You're welcome. Glad to help.