S
shternm
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
'_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