Can some please explain to me why I am getting 'Range' of object
'_Global' failed error?

The ranges that are referred to on that line are 3 cell columns.


Thank you, thank you, thank you ………


Sub Macro2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim Value As Variant
Dim i As Long
Dim rngSrc As Range
Dim nameToFind As String

Set wks = Sheets("Query")
Set rngToSearch = wks.Columns(1)
Set rngSrc = Range("BU" & "Affl")
----------------------------------- Error Line

Sheets("Query").Select
Range("A2").Select

' leave header row alone
For i = 2 To rngSrc.Rows.Count
' search values in Column A
nameToFind = rngSrc.Cells(i, 1).Value
If (Len(nameToFind) 0) Then
Set rngFound = rngToSearch.Find(what:=nameToFind,
lookat:=xlWhole)

Worksheets("Query").Rows("1:1").Copy
Sheets.Add
ActiveSheet.Name = nameToFind
ActiveSheet.Paste

If rngFound Is Nothing Then
Sheets("Data").Select
Exit For
Else
Do
rngFound.EntireRow.Copy
Worksheets(nameToFind).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste

rngFound.ClearContents
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
End If
Next i
End Sub


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

s,
----------------------
Sub IdentifyTheColumnRanges()
Dim rngSrc As Excel.Range
Set rngSrc = Range("BU:BU, AF:AF, FL:FL")
MsgBox rngSrc.Address
Set rngSrc = Nothing
End Sub
'-------------------------
Jim Cone
San Francisco, USA

"shternm"
wrote in message

Can some please explain to me why I am getting 'Range' of object
'_Global' failed error?
The ranges that are referred to on that line are 3 cell columns.
Thank you, thank you, thank you ………


Sub Macro2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim Value As Variant
Dim i As Long
Dim rngSrc As Range
Dim nameToFind As String

Set wks = Sheets("Query")
Set rngToSearch = wks.Columns(1)
Set rngSrc = Range("BU" & "Affl")
----------------------------------- Error Line
-snip-

Sorry for the confusion: Both "BU" and "AFFL" are named ranges
specifying columns.
I would like to concatenate the two ranges.

For example:

*BU* * Affl * *Combined result to search
for*77 AX 77AX
63 MS 63MS


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

s,

Confusion is normal here...
Do not really understand your range designations,
however this works...
Set rngSrc = Application.Union(Range("BU"), Range("Affl"))
Note that names of the ranges are case sensitive.

Jim Cone

"shternm"
wrote in message

Sorry for the confusion: Both "BU" and "AFFL" are named ranges
specifying columns.
I would like to concatenate the two ranges.
For example:
*BU* * Affl * *Combined result to search
for*77 AX 77AX
63 MS 63MS
--
shternm

This looks for a range named BUAffl

Maybe...

Set rngSrc = union(Range("BU"),range("Affl"))



shternm wrote:

Can some please explain to me why I am getting 'Range' of object
'_Global' failed error?

The ranges that are referred to on that line are 3 cell columns.

Thank you, thank you, thank you ………

Sub Macro2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim Value As Variant
Dim i As Long
Dim rngSrc As Range
Dim nameToFind As String

Set wks = Sheets("Query")
Set rngToSearch = wks.Columns(1)
Set rngSrc = Range("BU" & "Affl")
----------------------------------- Error Line

Sheets("Query").Select
Range("A2").Select

' leave header row alone
For i = 2 To rngSrc.Rows.Count
' search values in Column A
nameToFind = rngSrc.Cells(i, 1).Value
If (Len(nameToFind) 0) Then
Set rngFound = rngToSearch.Find(what:=nameToFind,
lookat:=xlWhole)

Worksheets("Query").Rows("1:1").Copy
Sheets.Add
ActiveSheet.Name = nameToFind
ActiveSheet.Paste

If rngFound Is Nothing Then
Sheets("Data").Select
Exit For
Else
Do
rngFound.EntireRow.Copy
Worksheets(nameToFind).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste

rngFound.ClearContents
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
End If
Next i
End Sub

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

Just this portion:

Note that names of the ranges are case sensitive.

I don't think that this is true. You may want to test once more.

Jim Cone wrote:

s,

Confusion is normal here...
Do not really understand your range designations,
however this works...
Set rngSrc = Application.Union(Range("BU"), Range("Affl"))
Note that names of the ranges are case sensitive.

Jim Cone

"shternm"
wrote in message

Sorry for the confusion: Both "BU" and "AFFL" are named ranges
specifying columns.
I would like to concatenate the two ranges.
For example:
*BU* * Affl * *Combined result to search
for*77 AX 77AX
63 MS 63MS
--
shternm

--

Dave Peterson

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

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