I'm confused about what you're doing. Are you just trying to loop through both
the BU and AFFL range and find those values for each cell?

If that's close....

Option Explicit
Sub testme()

Dim rngBU As Range
Dim rngAFFL As Range
Dim rngSrc As Range
Dim myCell As Range

Set rngBU = Nothing
Set rngAFFL = Nothing
On Error Resume Next
Set rngBU = Range("bu")
Set rngAFFL = Range("affl")
On Error GoTo 0

Set rngSrc = Nothing
If rngBU Is Nothing Then
Set rngSrc = rngAFFL
ElseIf rngAFFL Is Nothing Then
Set rngSrc = rngBU
Else
Set rngSrc = Union(rngAFFL, rngBU)
End If

If rngSrc Is Nothing Then
MsgBox "Neither exist"
Exit Sub
End If

For Each myCell In rngSrc.Cells
'do your work against mycell.value
Next myCell

End Sub





shternm wrote:

Thank you, this works - sort of.....

I no longer get the error when I use the union but it does not find it
in the range 'rngFound'.

This code works if I have one range like 'BU' or 'Affl' but not both
(at least one instance exists).

Am I missing something obvious?

Thanks for all your help.

--
shternm
------------------------------------------------------------------------
shternm's Profile: http://www.excelforum.com/member.php...nfo&userid=858
View this thread: http://www.excelforum.com/showthread...hreadid=472191

--

Dave Peterson