0





59
1

Been stuck for a while. I hope someone can help me with my current issue, and point me to why I'm having error

Object Required 424 error

Here is the sheet I'm working on, and here is my current code:

note: (me.cmbveh.value is a combo box on my vba form)

Dim v_name As String
Dim add_date As Date

v_name = Me.cmbveh.Value
add_date = Application.WorksheetFunction.VLookup(v_name, Sheets("Vehicle Database").Range("F14:R33"), 13, False)

Application.WorksheetFunction.VLookup(v_name, Sheets("Vehicle Database").Range("F14:R33"), 13, False).Select
ActiveCell.Value = DateSerial(Year(add_date) + 1, Month(add_date), Day(add_date))

The error is pointing in this line of code:

Application.WorksheetFunction.VLookup(v_name, Sheets("Vehicle Database").Range("F14:R33"), 13, False).Select

What I'm trying to do here is, if the value on my combo box has a match on my table, I would like to be able to add 1 year on the "Registration Expiry Date" column. Any help would be appreciated!

Question author Kelvin-jhon-camua | Source

0


1

Since you know you are looking to compare your cmbveh Combo-Box value, with the values in Column F, you can use the Application.Match to find the row number that matches the value in the Combo-Box. Afterwards, you can get the row refference (add 13 to it, since your Range starts from row 14 and not row 1), and read the value from Column "R" (where you store your "REGISTRATION EXPIRY DATE").

At last, use the DateAdd function to add 1 year to exisitng date (by choosing "yyyy" as the interval parameter).

Code

Option Explicit

Private Sub CommandButton1_Click()

Dim v_name As String
Dim add_date As Date

Dim MatchRow    As Variant

v_name = Me.cmbveh.Value

With Sheets("Vehicle Database")
    ' first test to see if Match criteria was met (found in Column F)
    If Not IsError(Application.Match(v_name, .Range("F14:F33"), 0)) Then
        MatchRow = Application.Match(v_name, .Range("F14:F33"), 0) '<-- get row number
        .Range("R" & MatchRow + 13).Value = DateAdd("yyyy", 1, .Range("R" & MatchRow + 13).Value)
    End If
End With

End Sub
Answer author Shai-rado

Ask about this question here!