VBA - When Union is used IF sentence does not work..?

[Phil811]

Hi,

Im trying to look at multiple ranges and check each cells with an if sentence in order to replace letters with words e.g. If the cells reads "AR" replace it with "Night"..This works perfectly if I say Range("D10:D40") but not if i say Union(Range("D10:D40"), Range("L10:L40"), Range("T10:T40")...Then it says 'Type mismatch' error 2042...

For Each cell In Range("D10:D40")
If cell.Value = "NR" Then
cell.Value = "Nat"
End If
Next cell

For Each cell In Union(Range("D10:D40"), Range("L10:L40"), Range("T10:T40"), Range("D49:D79"), Range("L49:L79"), Range("T49:T79"), Range("D88:D117"), Range("L88:L117"), Range("T88:T117"), Range("D127:D153"), Range("L127:L157"), Range("T127:T157"))
If cell.Value = "AR" Then
cell.Value = "Aften"
End If
Next cell

 

[pgc01]

Hi Phil

Did you check that there are no error values in any of the cells in those ranges?

 

[Phil811]

Yup everything looks okay in the excel sheet.

 

[pgc01]

Sorry, I just tried the code you posted and had no problem.

Sub test()
Dim cell As Range

For Each cell In Union(Range("D10:D40"), Range("L10:L40"), Range("T10:T40"), Range("D49:D79"), Range("L49:L79"), Range("T49:T79"), _
                              Range("D88:D117"), Range("L88:L117"), Range("T88:T117"), Range("D127:D153"), Range("L127:L157"), Range("T127:T157"))
    If cell.Value = "AR" Then
        cell.Value = "Aften"
    End If
Next cell
End Sub

I wrote AR in some cells and the code performed the replacement as expected.

When the code stops, press Debug and inspect the cell that is being tested.

 

[Phil811]

You are correct - it does work and it was a reference error in one single cell that messed it all up..it works like a charm now

 

[pgc01]

I'm glad it's working OK

Remark: Your range is an intersection of rows and columns.

Maybe a simpler way and easier to read is:

Sub test()
Dim rC As Range

For Each rC In Range("(d:d,l:l,t:t) (10:40,49:79,88:117,127:153)")
    If rC.Value = "AR" Then rC.Value = "Aften"
Next cell
End Sub