Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run-time error '9'

0

How to identify the error on the following VBA?

error -

Subscript Out of Range

Bug
Set Rng = dataVoucherNo.Find(what:=searchValue, _

LookIn:=x1Values, _

LookAt:=x1Whole, _

SearchOrder:=x1ByRows, _

MatchCase:=False)





Sub Select_Data()

'OriginalClubAccount

'Search the data repository worksheet and return the found record into sourceSheet

Dim sourceSheet As Worksheet

Dim dataSheet As Worksheet

Dim searchValue As Variant

Dim dataVoucherNo As Range

Dim recordRow As Integer

'Make some sheet variables so we can use those instead of hard-coding

Set sourceSheet = Sheets("Form")

Set dataSheet = Sheets("TB")

'Column that contains the value for which we will search.

Set dataVoucherNo = dataSheet.Range("E7:E65000")

'Value to search for.

searchValue = InputBox("Input an Voucher No ", "Record Search")

'Check if the user input a value and hit the OK button.

If searchValue <> vbNullString Then

'Value input, so search for it.

'Clear Data

sourceSheet.Range("F5").Value = ""

sourceSheet.Range("F7").Value = ""

sourceSheet.Range("F9").Value = ""

sourceSheet.Range("F11").Value = ""

sourceSheet.Range("F13").Value = ""

sourceSheet.Range("F15").Value = ""

sourceSheet.Range("F17").Value = ""

sourceSheet.Range("F19").Value = ""

'Search

'More specific info:https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

Set Rng = dataVoucherNo.Find(what:=searchValue, _

LookIn:=x1Values, _

LookAt:=x1Whole, _

SearchOrder:=x1ByRows, _

MatchCase:=False)

'Check if a record was found.

If Not Rng Is Nothing Then

'Record Found!

'Get the row of the record.

recordRow = Rng.Row

recordAddress = Rng.Address

'Put the records information back into the form.

sourceSheet.Range("F5").Value = dataSheet.Cells(recordRow, 1).Value

sourceSheet.Range("F7").Value = dataSheet.Cells(recordRow, 2).Value

sourceSheet.Range("F9").Value = dataSheet.Cells(recordRow, 3).Value

sourceSheet.Range("F11").Value = dataSheet.Cells(recordRow, 4).Value

sourceSheet.Range("F13").Value = dataSheet.Cells(recordRow, 5).Value

sourceSheet.Range("F15").Value = dataSheet.Cells(recordRow, 6).Value

sourceSheet.Range("F17").Value = dataSheet.Cells(recordRow, 7).Value

sourceSheet.Range("F19").Value = dataSheet.Cells(recordRow, 8).Value

Else

'Nothing Found, tell the user.

MsgBox "Record Not Found"

End If

End If

End Sub

Answer
Discuss

Answers

0
Selected Answer

When I opened your workbook an error message appeared saying that it contains a circular reference. I suspect tab "Form", column M but couldn't access that sheet. It seems to be protected.

It would have been helpful if you would have pasted the code in your question within code brackets (press the "Code" button in the posting form and follow instructions). That would give proper format to your code as demonstrated here:-

Option Explicit

Sub Select_Data()

    'OriginalClubAccount
    'Search the data repository worksheet and return the found record into sourceSheet

    Dim sourceSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim searchValue As Variant
    Dim dataVoucherNo As Range
    Dim recordRow As Integer
    Dim Fnd As Range
    Dim C As Long, i As Long

    'Make some sheet variables so we can use those instead of hard-coding
    Set sourceSheet = Sheets("Form")
    Set dataSheet = Sheets("TB")

    'Column that contains the value for which we will search.
    Set dataVoucherNo = dataSheet.Range("E7:E65000")

    'Value to search for.
    searchValue = InputBox("Input an Voucher No ", "Record Search")

    'Check if the user input a value and hit the OK button.
    If searchValue <> vbNullString Then
        'Value input, so search for it.
        'Clear Data
        For C = 5 To 19 Step 2
            sourceSheet.Range(Format(C, "F" & 0)).Value = ""
        Next C

        'Search
        'More specific info:https://docs.microsoft.com/en-us/office/vba/api/excel.range.find
        Set Fnd = dataVoucherNo.Find(what:=searchValue, _
                                     LookIn:=xlValues, _
                                     LookAt:=xlWhole, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False)

        'Check if a record was found.
        If Not Fnd Is Nothing Then
            'Record Found!
            'Get the row of the record.
            recordRow = Fnd.Row
'            recordAddress = Fnd.Address

            'Put the records information back into the form.
            For C = 5 To 19 Step 2
                i = i + 1
                sourceSheet.Range(Format(C, "F" & 0)).Value = dataSheet.Cells(recordRow, i).Value
            Next C
        Else
            'Nothing Found, tell the user.
            MsgBox "Record Not Found"
        End If
    End If
End Sub

You use the variable "Rng" which isn't declared. Therefore I assume that you don't use Option Explicit at the top of your code module. I recommend that you do. It will save you time to allow VBA to point out missing declarations.

Other than that there appears nothing wrong with your code. I re-wrote it, taking out all the extra blank lines in your post and changing the way you clear and over-write the Form. I couldn't test my code on your workbook because its VBA Project is password protected and you supplied no password.

However, I did test my code on a blank workbook and found that you used the character "1" (numeric) instead character "l" (lower case L) in some of the enumeration values in the declaration of "Rng" (now "Fnd"), like "x1Values" in place of "xlValues". After this correction the above code ran on my blank workbook. I don't know if it actually worked.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login