Loops stop working and presents "Run time error '91':
 

If I use a loop the runtime error presents itself showing "Object
variable or with block variable not set". This presents itself in the
"rng.Select" part of the code. Any suggestions why this occurs? I have
come around the problem using "On Error Resume Next". But I rather not
want to use that command... Any help is greately appreciated!

Sub Classification_Top_3()

TargetColumn = 1
TargetRow = 2
StartCell = "A1"
Sht = "SPI"
Sht2 = ActiveSheet.Name

SearchVariable2 = Sheets(Sht2).Range(StartCell).Value

Do While Not SearchVariable2 = ""
TargetRow = 2
Call Classification_Engine2

Sheets(Sht2).Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Activate

SearchVariable2 = ActiveCell.Value
TargetColumn = TargetColumn + 1
Loop


End Sub

Sub Classification_Engine2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

Sheets(Sht).Select

' Finds Industry Column
SearchVariable = "INDUSTRY"
Call Find

IndustryColumn = OffsetColumn
NumberofComps = Sheets(Sht).UsedRange.Rows.Count

' Finds Subindustry Column
SearchVariable = "SUBINDUSTRY"
Call Find

SubIndustryColumn = OffsetColumn

' Finds Region Column
SearchVariable = "Region"
Call Find

RegionColumn = OffsetColumn

' Finds Region Column
SearchVariable = "Dscode"
Call Find

DscodeColumn = OffsetColumn

' Defines which columns to be used for concatenated ind/subind and
region
Worksheets(Sht).Range("C9").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

RegionandIndustryColumn = ActiveCell.Column
RegionandSubIndustryColumn = ActiveCell.Offset(0, 1).Column

' Copies the Dscode of the desired ranking firm to the output sheet
SearchRow = 9
For i = 1 To 3
Set rng = Nothing
Set rng = Cells.Find(What:=SearchVariable2, _
After:=Cells(SearchRow,
RegionandIndustryColumn), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

rng.Select
If Not rng Is Nothing Then
SelectRow = ActiveCell.Row
SelectColumn = DscodeColumn

Worksheets(Sht2).Cells(TargetRow, TargetColumn).Value = _
Worksheets(Sht).Cells(SelectRow, SelectColumn).Value

End If

SearchRow = SelectRow + 1
TargetRow = TargetRow + 1
Next i

End Sub


Sub Find()
Sheets(Sht).Select
SearchRow = 9
OffsetColumn = WorksheetFunction.Match(SearchVariable,
Rows(SearchRow), 0)
End Sub

Loops stop working and presents "Run time error '91':
 

Hi,

I suspect that after setting rng to nothing your set rng fails to find
anything and when you try to select an empty range (rng.select) you get your
error. To test this put these couple of lines of code in before you select
the range

If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Range rng is empty"
End If


Mike

"Wesslan" wrote:

If I use a loop the runtime error presents itself showing "Object
variable or with block variable not set". This presents itself in the
"rng.Select" part of the code. Any suggestions why this occurs? I have
come around the problem using "On Error Resume Next". But I rather not
want to use that command... Any help is greately appreciated!

Sub Classification_Top_3()

TargetColumn = 1
TargetRow = 2
StartCell = "A1"
Sht = "SPI"
Sht2 = ActiveSheet.Name

SearchVariable2 = Sheets(Sht2).Range(StartCell).Value

Do While Not SearchVariable2 = ""
TargetRow = 2
Call Classification_Engine2

Sheets(Sht2).Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Activate

SearchVariable2 = ActiveCell.Value
TargetColumn = TargetColumn + 1
Loop


End Sub

Sub Classification_Engine2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

Sheets(Sht).Select

' Finds Industry Column
SearchVariable = "INDUSTRY"
Call Find

IndustryColumn = OffsetColumn
NumberofComps = Sheets(Sht).UsedRange.Rows.Count

' Finds Subindustry Column
SearchVariable = "SUBINDUSTRY"
Call Find

SubIndustryColumn = OffsetColumn

' Finds Region Column
SearchVariable = "Region"
Call Find

RegionColumn = OffsetColumn

' Finds Region Column
SearchVariable = "Dscode"
Call Find

DscodeColumn = OffsetColumn

' Defines which columns to be used for concatenated ind/subind and
region
Worksheets(Sht).Range("C9").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

RegionandIndustryColumn = ActiveCell.Column
RegionandSubIndustryColumn = ActiveCell.Offset(0, 1).Column

' Copies the Dscode of the desired ranking firm to the output sheet
SearchRow = 9
For i = 1 To 3
Set rng = Nothing
Set rng = Cells.Find(What:=SearchVariable2, _
After:=Cells(SearchRow,
RegionandIndustryColumn), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

rng.Select
If Not rng Is Nothing Then
SelectRow = ActiveCell.Row
SelectColumn = DscodeColumn

Worksheets(Sht2).Cells(TargetRow, TargetColumn).Value = _
Worksheets(Sht).Cells(SelectRow, SelectColumn).Value

End If

SearchRow = SelectRow + 1
TargetRow = TargetRow + 1
Next i

End Sub


Sub Find()
Sheets(Sht).Select
SearchRow = 9
OffsetColumn = WorksheetFunction.Match(SearchVariable,
Rows(SearchRow), 0)
End Sub

Loops stop working and presents "Run time error '91':
 

On Feb 12, 11:15*am, Mike H wrote:
Hi,

I suspect that after setting rng to nothing your set rng fails to find
anything and when you try to select an empty range (rng.select) you get your
error. To test this put these couple of lines of code in before you select
the range

If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Range rng is empty"
End If

Mike



"Wesslan" wrote:
If I use a loop the runtime error presents itself showing "Object
variable or with block variable not set". This presents itself in the
"rng.Select" part of the code. Any suggestions why this occurs? I have
come around the problem using "On Error Resume Next". But I rather not
want to use that command... Any help is greately appreciated!

Sub Classification_Top_3()

TargetColumn = 1
TargetRow = 2
StartCell = "A1"
Sht = "SPI"
Sht2 = ActiveSheet.Name

SearchVariable2 = Sheets(Sht2).Range(StartCell).Value

Do While Not SearchVariable2 = ""
TargetRow = 2
* * Call Classification_Engine2

* * Sheets(Sht2).Select
* * Selection.End(xlUp).Select
* * ActiveCell.Offset(0, 1).Activate

* * SearchVariable2 = ActiveCell.Value
* * TargetColumn = TargetColumn + 1
Loop

End Sub

Sub Classification_Engine2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

Sheets(Sht).Select

' * Finds Industry Column
* * SearchVariable = "INDUSTRY"
* * Call Find

* * IndustryColumn = OffsetColumn
* * NumberofComps = Sheets(Sht).UsedRange.Rows.Count

' * Finds Subindustry Column
* * SearchVariable = "SUBINDUSTRY"
* * Call Find

* * SubIndustryColumn = OffsetColumn

' * Finds Region Column
* * SearchVariable = "Region"
* * Call Find

* * RegionColumn = OffsetColumn

' * Finds Region Column
* * SearchVariable = "Dscode"
* * Call Find

* * DscodeColumn = OffsetColumn

' * Defines which columns to be used for concatenated ind/subind and
region
* * Worksheets(Sht).Range("C9").Select
* * Selection.End(xlToRight).Select
* * ActiveCell.Offset(0, 1).Select

* * RegionandIndustryColumn = ActiveCell.Column
* * RegionandSubIndustryColumn = ActiveCell.Offset(0, 1).Column

' * Copies the Dscode of the desired ranking firm to the output sheet
* * SearchRow = 9
* * For i = 1 To 3
* * * * Set rng = Nothing
* * * * Set rng = Cells.Find(What:=SearchVariable2, _
* * * * * * * * * * * * * * * After:=Cells(SearchRow,
RegionandIndustryColumn), _
* * * * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * * * SearchOrder:=xlByRows, _
* * * * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * * * SearchFormat:=False)

* * * * rng.Select
* * * * If Not rng Is Nothing Then
* * * * SelectRow = ActiveCell.Row
* * * * SelectColumn = DscodeColumn

* * * * Worksheets(Sht2).Cells(TargetRow, TargetColumn).Value = _
* * * * Worksheets(Sht).Cells(SelectRow, SelectColumn).Value

* * * * End If

* * * * SearchRow = SelectRow + 1
* * * * TargetRow = TargetRow + 1
* * Next i

End Sub

Sub Find()
Sheets(Sht).Select
SearchRow = 9
OffsetColumn = WorksheetFunction.Match(SearchVariable,
Rows(SearchRow), 0)
End Sub- Hide quoted text -

- Show quoted text -

Mike you are very right! I will have to write a work around it.
Perhaps I can just include the rng.select into the:
If Not rng Is Nothing Then

And the problem should dissapear.

Your help is very much appreciated!