VBA Vlookup returning Type Mismatch

[scoha]

I am having trouble getting my VBA Vlookup to work and hope the exterts out there might be able to assist.

I use a global variable to transfer a variable value from another userform within the same workbook.

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Contingency")

txtTCID = GlobalContractID

End Sub

The bidea is then to place this number into a text box (called txtTContractID) and use the change to then go and lookup the values for the other text boxes, along the lines of:

Private Sub txtTCID_Change()

txtTProjectID.Value = Application.VLookup(Me.txtTCID, Range("tblContracts"), 2, False)

End Sub

but I get a "Could not set the Value property - Type Mismatch error' The lookup up is not returning the right value.

 

[BrianB]

Don't know if it is due to different Excel version but the syntax is usually
Application.WorksheetFunction.VLookup()

VLOOKUP() is often a problem. The find value and lookup range must be formatted the same to get a match. In this case, using a textbox, the find value will be a string(text).

Try formatting the lookup range as text. That will probably not be enough. We also need to force the conversion by selecting the column and using Data/Text to columns...Finish.

*Sometimes* in cases like this Excel does not like doing too much work in a single line. Could try
.......... Vlookup(GlobalContractID,............
Because it is already a variable, not looking up a textbox value. Need to Dim the variable type of GlobalContractID As String or numeric type.

 

[Norie]

If you are looking up a number why are you using a textbox?

The least you could do is wrap it with the Val function.

Private Sub txtTCID_Change()

txtTProjectID.Value = Application.VLookup(Val(Me.txtTCID), Range("tblContracts"), 2, False)

End Sub

PS What global variable are you talking about?

I don't see any variables, never mind global ones, declared in the code you've posted.