CODEWITHSUNDEEP
vbaexcel
Niva
Niva

Reputation: 298

Error in defining variable

Hi there I have a table of cells , in which one of the column is the seat number. However the seat number contains some string values such as 146A and most numericals such as 146. However in vba, when I use the seat number to vlookup, I am unable to find the right variable as if I dim seatno as integer then values such as 146A would give errors and if i dim seat no as string the numericals dont work .I formatted the whole seat no column to text in excel but the error persists. Hencei would need help on how I can define the variable which would show all values ranging from 1 to 146A. This is the code I have done 

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Dim answer As Integer
   answer = TextBox1.Value
   TextBox2.Value = WorksheetFunction.VLookup(answer, Sheets("L12 - Data Sheet").Range("B:E"), 2, False)
   TextBox3.Value = WorksheetFunction.VLookup(answer, Sheets("L12 - Data Sheet").Range("B:E"), 3, False)
   TextBox4.Value = WorksheetFunction.VLookup(answer, Sheets("L12 - Data Sheet").Range("B:E"), 4, False)

End Sub

Upvotes: 1

Views: 55

Answers (1)

user4039065
user4039065

Reputation:

If column B in Sheets("L12 - Data Sheet") is definitely text then declare answer as a string.

Dim answer As String
answer = TextBox1.Value
TextBox2.Value = WorksheetFunction.VLookup(answer, _
                   Sheets("L12 - Data Sheet").Range("B:E"), 2, False)

If there are a combination of strings (e.g. 146A) and true numbers (e.g. 146) in 'L12 - Data Sheet'!B:B then retrieve the value from TextBox1 as a string and attempt to convert it to a true number.

Dim answer As String
answer = TextBox1.Value
TextBox2.Value = WorksheetFunction.VLookup(Iif(IsNumeric(answer), Int(answer), answer), _
                   Sheets("L12 - Data Sheet").Range("B:E"), 2, False)

If there is no consistent value type to the values in 'L12 - Data Sheet'!B:B then hit it with everything you've got available.

Dim answer As Variant, mtch As Long
answer = TextBox1.Value

Select Case True
    Case Not IsError(Application.Match(CStr(answer), Sheets("L12 - Data Sheet").Columns(2), 0))
        mtch = Application.Match(CStr(answer), Sheets("L12 - Data Sheet").Columns(2), 0)
    Case Not IsError(Application.Match(Int(answer), Sheets("L12 - Data Sheet").Columns(2), 0))
        mtch = Application.Match(Int(answer), Sheets("L12 - Data Sheet").Columns(2), 0)
End Select

'error control needed here in case mtch is still not a row number
debug.print mtch
TextBox2.Value = Sheets("L12 - Data Sheet").Cells(mtch, "C").Value
TextBox3.Value = Sheets("L12 - Data Sheet").Cells(mtch, "D").Value
TextBox4.Value = Sheets("L12 - Data Sheet").Cells(mtch, "E").Value

Upvotes: 1

Related Questions