Run-Time Error

R

Error 1004 can occur for a number of reasons but in this case I think it's your use of VLOOKUP which causes the problem. (Note that it helps if you upload a file with your question- you can still do that by editting your question and using the Add Files... button to upload a representative Excel file -without any personal data- to show your existing macro and data).

It looks like your code relates to a UserForm (and I put my code behind a control called CommandButton1) and tries to find whatever value if in cmb_Ticket in column B of your worksheet CT_Master then format the values in column C and D. If it does not find a match, an error 1004 will occur.

You can use error handling to avoid that problem. In this case I added a line:

On Error GoTo Wrong

so the code jumps to the line starting Wrong: if an error occurs (and gives a user message).

I also tidied up your code since yours does one thing if Ticket or Type is "" and another thing if the Type is "Issued " or "Received" (so only two If decisions are needed plus an Else if something else was entered as the Type). Note the comments and changes in bold below:

Private Sub CommandButton1_Click()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("CT_Master")

' Is a field blank?
If Me.cmb_Ticket.Value = "" Or Me.cmb_Type.Value = "" Then
    Me.txt_Amount.Value = ""
    Me.txt_Unit.Value = ""
    Exit Sub
End If

' Is the Type a recognised value?
If Me.cmb_Type.Value = "Issue" Or cmb_Type.Value = "Received" Then
On Error GoTo Wrong
    Me.txt_Amount.Value = Format(Application.WorksheetFunction.VLookup(Me.cmb_Ticket, sh.Range("B:D"), 3, 0), "#,##0.00")
    Me.txt_Unit.Value = Format(Application.WorksheetFunction.VLookup(Me.cmb_Ticket, sh.Range("B:D"), 2, 0))

    Else
    MsgBox "Please enter a correct Ticket Type and retry"
End If

Exit Sub

' If an error occurs...
Wrong:
MsgBox "Ticket not found, please correct and try again"

End Sub

Hope this explains / fixes your problem.