I am new to vba and i have encountered into some problems.

I want to look up something from another worksheet and copy the match value to the other cell

VBA said it was the last code having problem.

Private Sub ReferenceOk_Click()

Dim nextRefRec As Integer
Dim i As Integer
Dim ListNo As Integer

ListNo = ListBoxBook.ListIndex
If ListNo < 0 Then
    MsgBox "Please select any book"
    Exit Sub
End If


Sheets("Rental History").Activate
nextRefRec = Cells(Rows.Count, 2).End(xlUp).Row + 1
For i = 0 To 1
    Cells(nextRefRec, i + 3).Value = ListBoxBook.List(ListNo, i)
Next i
Cells(nextRefRec, 3).NumberFormat = "0000"
Cells(nextRefRec, 2).NumberFormat = "00000"
Cells(nextRefRec, 2).Value = TxtMemberNo.Value
Cells(nextRefRec, 5).Value = Date
Cells(nextRefRec, 6).Value = Date + TxtRentalDays.Value
Cells(nextRefRec, 7).Value = Application.WorksheetFunction.VLookup(Worksheets("Rental History").Cells(nextRefRec, 4), Worksheets("Book List").Cells("B4:C24"), 6, False)

End Sub

1 Answers

0
chillin On

Your VLOOKUP formula:

VLookup(Worksheets("Rental History").Cells(nextRefRec, 4), Worksheets("Book List").Cells("B4:C24"), 6, False)

seems invalid.

The range Worksheets("Book List").Cells("B4:C24") contains 2 columns, but your third argument is 6. In other words, you are attempting to get the 6th column of a 2-column range (which obviously doesn't exist). So at the moment it's probably returning a #REF error.

Further info on VLOOKUP if needed: https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

Fix your VLOOKUP formula so that you're passing a column that at least exists within the range you pass as the second argument.


Also, I recommend declaring these variables as type Long (to prevent type overflow errors):

Dim nextRefRec As Integer
Dim i As Integer
Dim ListNo As Integer

and changing this line:

Cells(nextRefRec, 7).Value = Application.WorksheetFunction.VLookup(Worksheets("Rental History").Cells(nextRefRec, 4), Worksheets("Book List").Cells("B4:C24"), 6, False)

to this (except also fix your VLOOKUP arguments as mentioned above):

Cells(nextRefRec, 7).Value = Application.VLookup(Worksheets("Rental History").Cells(nextRefRec, 4), Worksheets("Book List").Cells("B4:C24"), 6, False)

so that if VLOOKUP returns #N/A or some other error, the error value can be written to the cell instead of interrupting your macro.

Your code implicitly refers to whatever sheet happens to be active whilst the code is running. Try to refer to the parent workbook and worksheet (in case the sheet that's active isn't the one that you think it is).