VBA Userform Error with vlookup

[beetle_cca]

Hi All,

Very much a novice when it comes to VBA. Thank you in advance.

I have a userform where the user inserts a contract number the clicks on a command button to extract data from a master list. This is all working great, hoever I have tried to handle errors when an unmatch value is entered. The msgbox appears and the user is taken back to the form however when they enter a correct value the "cusname" is not populated.

Any help would be amazing.



Private Sub Userform_Extract()

Dim ws As Worksheet: Set ws = Sheets("Extract")
Dim rnglook As Range: Set rnglook = ws.Range("Extract")
Dim cusname As Variant

cusname = Application.VLookup(contract.Value, rnglook, 40, False)

If IsError(cusname) Then
MsgBox "Check Contract Number as No Matches found"
contract.SetFocus
Exit Sub
End If

NI = Format(Application.VLookup(contract.Value, rnglook, 35, False), "$ #,##0.00")
interest = Format(Application.VLookup(contract.Value, rnglook, 10, False) / 100, "0.00%#")
branch = Application.VLookup(contract.Value, rnglook, 2, False)
equip = Application.VLookup(contract.Value, rnglook, 46, False)

 

[Tinbendr]

You may have to force a recalculation.

Application.Calculate

"cusname" is not populated.

Also, you're sample code doesn't show it being assigned to any control or range. Just a variable.

 

[beetle_cca]

As that I am so new at this I am not sure where to put the "application.calculate" in my code.

I am also not sure what you mean by "Also, you're sample code doesn't show it being assigned to any control or range. Just a variable. "

If you could please elaborate it would be fantastic.

 

[Tinbendr]

Dim cusname As Variant

Application.Calculate
cusname = Application.VLookup(contract.Value, rnglook, 40, False)

Once you look this value up, what is the code doing with it? It is displayed on the userform?

 

[beetle_cca]

Yes, You enter in the contract number and it locates the customer name from a database and displays in it the userform. It is then transferred to another worksheet once the userform is complete.