setting range and getting runtime error 9

CSM71593 0
Viewed 49

I am completely stumped, as far as I can tell I am only calling variables that I have already coded, when I debug it highlights Set Rng

Private Sub CommandButton1_Click()

    Dim Prompt As String
    Dim RetValue As String
    Dim Rng As Range
    Dim RowCrnt As Long

    Promt = ""
    With Sheets("Bin7in")
    Do While True

    RetValue = InputBox(Prompt & "Enter Die Number")
    If RetValue = "" Then
    Exit Do
    End If

    Set Rng = .Columns("C:C").Find(What:=RetValue, After:=.Range("C1"), _
    LookIn:=x1Formulas, LookAt:=x1Whole, SearchOrder:=x1ByRows, _
    SearchDirection:=x1Next, MatchCase:=False, SearchFormat:=False)

    If Rng Is Nothing Then
    MsgBox RetValue & "Not found"
    Else
    Bin = Rng.Columns("A:A")
    MsgBox RetValue & "In Bin" & Bin
    End If
    Prompt = Prompt
    Loop
    End With

End Sub
vba excel
1 Answers

A VBA Lookup Using the Find Method

About the Error

  • The error Run-time error '9': Subscript out of range often occurs when a worksheet is not found.
  • This code is located in a sheet module of the workbook containing this code (ThisWorkbook).
  • When you click the command button, the active workbook (ActiveWorkbook) is ThisWorkbook and your code works 'fine'.
  • When running the code from the Visual Basic Editor, another workbook could be active i.e. ActiveWorkbook is not ThisWorkbook, so if there is no Bin7in worksheet in it, the error will occur.
  • Even worse, if there is a Bin7in worksheet in it, you could cause severe damage to it. In this case, you could 'only' receive the wrong information though.
  • To avoid this, it is good practice to always explicitly specify each object (workbook, worksheet, range...), in this case, you could use ThisWorkbook.Worksheets("Bin7in") instead of the unqualified ('tied' to the ActiveWorkbook) Sheets("Bin7in") in the With statement.

A Quick Fix

' Use 'Option Explicit' at beginning of each module.
' It forces you to declare (almost) all variables
' (arrays can be declared with 'ReDim').
' If you would have used it, the code would have, in sequential order,
' highlighted the following words:
' 'Promt', 'x1Formulas', 'x1Whole', 'x1ByRows', 'x1Next', and 'Bin'
' all with the message alert 'Compile Error: Variable not defined'
Option Explicit

' Indenting the code properly will make the code more readable 
' and help identify errors more easily.

Private CommandButton1_Click()

    Dim Prompt As String ' means 'Dim Prompt As String: Prompt = ""'
    Dim RetValue As String ' means 'Dim RetValue As String: RetValue = ""'
    ' Nitpicking here, but the Find method returns a reference to a single cell.
    Dim cell As Range ' means 'Dim cell As Range: Set cell = Nothing'
    Dim RowCrnt As Long ' means 'Dim RowCrnt As Long: RowCrnt = 0'
    Dim Bin As String ' means 'Dim Bin As String: Bin = ""'
    
    ' Promt = "" ' that's a typo
    ' Prompt = "" ' is redundant; it's already ""
    
    With ThisWorkbook.Worksheets("Bin7in")
        
        Do ' not sure what 'While True' is all about
            
            RetValue = InputBox(Prompt & "Enter Die Number")
            If RetValue = "" Then Exit Do ' optionally, to save two lines
        
            ' Remember that many of the VBA constants start with 'xl'
            ' that is lowercase 'XL', none with 'x1' that is lowercase 'X1'.
            Set cell = .Columns("C").Find(What:=RetValue, _
                 After:=.Range("C1"), LookIn:=xlFormulas, LookAt:=xlWhole, _
                 SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False)
            ' Note that with this setup the Find method will search
            ' in the following order: C2, C3,... CLastRow, C1.
            ' To make it search in the order C1...CLastRow, you could
            ' use 'After:=.Columns("C").Cells(.Columns("C").Rows.Count)',
            ' referencing the last cell in the column.
            ' Note that the last four arguments could be omitted:
            ' 1.) when the range is a single column or a single row,
            ' the 'SearchOrder' is irrelevant.
            ' 2.) 'SearchDirection' is by default 'xlNext'.
            ' 3.) 'MatchCase' is by default 'False'.
            ' 4.) 'SearchFormat' is rarely used. It uses 'True' to work
            ' with 'Application.FindFormat'. When you'll need it,
            ' you'll learn how to use it.
            
            ' You can format the output more appropriately (nicely).
            
            If cell Is Nothing Then
                MsgBox RetValue & " not found.", vbExclamation
            Else
                ' To reference the cell in the same row but in column 'A',
                ' you could use one of the following:
                ' 'cell.EntireRow.Columns("A")'
                ' '.Cells(cell.row, "A")'
                Bin = CStr(cell.EntireRow.Columns("A").Value)
                MsgBox RetValue & " in Bin " & Bin & ".", vbInformation
            End If
            'Prompt = Prompt ' is redundant; it's already 'Prompt'
            
        Loop
    
    End With

End Sub
VBasic20080
Related