We help IT Professionals succeed at work.

Error direction if no records found

Fordraiders
Fordraiders asked
on
Excel 2003
vba Updated

I have a routine I'm calling to add data to a listbox.

If the code below sees no rows in the  Sheet "List".. I need the code to move on.
Error 91 is the code Number..

I know I could use "On Error Resume Next"
but maybe something better ?


DetermineUsedRange UsedRng
           ' select the range for the listbox
            ListBox1.RowSource = "List!" & UsedRng.Address

Thanks
fordraiders
Sub DetermineUsedRange(ByRef theRng As Range)

Dim FirstRow As Integer, FirstCol As Integer, _
   LastRow As Integer, LastCol As Integer
   
   On Error GoTo DetermineUsedRange_Error

FirstRow = Cells.Find(What:="*", _
     SearchDirection:=xlNext, _
     SearchOrder:=xlByRows).Row
FirstCol = 1
LastRow = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
   Cells(LastRow, LastCol))
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"


   On Error GoTo 0
   Exit Sub
DetermineUsedRange_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"

End Sub

Open in new window

Comment
Watch Question

View Solution Only

gowflowPartner
CERTIFIED EXPERT

Commented:
don't see any reference to sheet "List"
if theRng is nothing then ....

gowflow
Fordraiders

Author

Commented:
 ' select the range for the listbox
            ListBox1.RowSource = "List!" & UsedRng.Address

Fordraiders

Author

Commented:
sorry, Im just using before the statement.

Sheets("List").Activate
andrewssd3
Top Expert 2011

Commented:
Try changing your main routine to 
Sub DetermineUsedRange(ByRef theRng As Range)

Dim FirstRow As Integer, FirstCol As Integer, _
   LastRow As Integer, LastCol As Integer
   
   On Error GoTo DetermineUsedRange_Error

FirstRow = Cells.Find(What:="*", _
     SearchDirection:=xlNext, _
     SearchOrder:=xlByRows).Row
FirstCol = 1
LastRow = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
   Cells(LastRow, LastCol))
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"


ExitBlock:
   On Error GoTo 0
   Exit Sub
DetermineUsedRange_Error:

If Err.Number = 91 Then
    Set theRng = Nothing
    Resume ExitBlock
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"
End If
End Sub

Open in new window

The you call would be 
DetermineUsedRange UsedRng
           ' select the range for the listbox
If Not (UsedRng Is Nothing) Then            
    ListBox1.RowSource = "List!" & UsedRng.Address
End If

Open in new window

Top Expert 2011
Commented:
It would be better practice, and more flexible, to change your DetermineUsedRange to a function which returned the range you want, then you would not be dependent on what sheet was active when you ran the macro:
Public Function DetermineUsedRange(ByRef theRng As Range) As Excel.Range

Dim FirstRow As Integer, FirstCol As Integer, _
   LastRow As Integer, LastCol As Integer
Dim wks As Excel.Worksheet
   
   On Error GoTo DetermineUsedRange_Error

Set wks = theRng.Worksheet

FirstRow = wks.Cells.Find(What:="*", _
     SearchDirection:=xlNext, _
     SearchOrder:=xlByRows).Row
FirstCol = 1
LastRow = wks.Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByRows).Row
LastCol = wks.Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByColumns).Column
Set DetermineUsedRange = wks.Range(Cells(FirstRow, FirstCol), _
   Cells(LastRow, LastCol))
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"


ExitBlock:
   On Error GoTo 0
   Exit Function
DetermineUsedRange_Error:

If Err.Number = 91 Then
    Set DetermineUsedRange = Nothing
    Resume ExitBlock
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DetermineUsedRange for ListData of Form frmList"
End If
End Function

Open in new window

Your call would then look like: 
Dim rng1 as Range
rng1=DetermineUsedRange(UsedRng)
           ' select the range for the listbox
If Not (rng1 Is Nothing) Then            
    ListBox1.RowSource = "List!" & rng1.Address
End If

Open in new window

Fordraiders

Author

Commented:
Thanks alot sorry for the delay

Explore More ContentExplore courses, solutions, and other research materials related to this topic.