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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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,870
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,870
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,112,996
Messages
5,543,188
Members
410,584
Latest member
Bluefox68
Top