| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| stop error message "too many different cell formats" | Excel Worksheet Functions | |||
| Stop the "Personal" sheet from popping up every time I open a work | Excel Discussion (Misc queries) | |||
| How do I stop "&T" in AT&T from displaying time in Excel footer? | Setting up and Configuration of Excel | |||
| Formulas stop working on all excell spreads at the same time | Excel Worksheet Functions | |||
| Excel should STOP the "zero or negative ..in log plot" error msg | Charts and Charting in Excel | |||
Hybrid Mode
