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

[Wesslan]

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

 

[Mike H]

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]

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






























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