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

Phil811

New Member
Joined
Mar 27, 2019
Messages
7
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi Phil

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Sorry, I just tried the code you posted and had no problem.

Code:
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

New Member
Joined
Mar 27, 2019
Messages
7
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,824
Members
410,813
Latest member
Vhinzvirgo
Top