VBA WorksheetFunction VLookup causing 'Run-Time Error 13' Type Mismatch

[LaneDaley]

Hey there,

I am really new with VBA and teaching myself from online tutorials, so please excuse me if this is a silly question!
I am building a contracting program which includes features of a hotel room type. To provide the user the ability to check details along the way, I am using WorksheetFunction Vlookup - however two of the values that come back need to appear as currency ("$#,##0.00") ...
So far I have the below code, which returns the correct information; however I can't figure out where to insert the "Format" expression to make the textbox display correctly. It's the tbUpgrade and tbEPaxFee that needs to be $$



Private Sub cmdSelect_Click()


With Me
.tbRmTypeName = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 2, 0)
.tbUpgrade = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Format(Sheets("RmTypes").Range("tblRmTypes").Value, "$#,##0.00"), 3, 0)
.tbInventory = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 4, 0)
.tbInclPax = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 5, 0)
.tbExtraPax = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 6, 0)
.tbEPaxFee = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Format(Sheets("RmTypes").Range("tblRmTypes").Value, "$#,##0.00"), 7, 0)
.tbRmSize = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 8, 0)
.tbRollaway = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 9, 0)
.tbBabyCot = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 10, 0)
.tbBedding = Application.WorksheetFunction.VLookup((Me.cbPMSCode), Sheets("RmTypes").Range("tblRmTypes"), 11, 0)
End With

End Sub

 

[Teeroy]

My first thought would be to wrap the value returned in the Format Function.

e.g.

.tbUpgrade = Format(Application.WorksheetFunction.VLookup((Me.cbPMSCode), Format(Sheets("RmTypes").Range("tblRmTypes").Value, "$#,##0.00"), 3, 0),"$#,##0.00")

 

[LaneDaley]

Thanks for that Teeroy
I gave it a shot, and it has stopped the 'Type Mismatch' error, however now it is display an error that reads "Run-Time error 424, Object Required"

 

[Teeroy]

Which line gives you the error? I would look for either there not being a match on the VLookup (i.e. error returned and error handling required) or a mistype on the property name that the text is being assigned to.