VBA Variable type

[bensonsearch]

Hi All,

may be easy but i need a variable type to hold 13 digits in it. I first did long but it aint long enough.

It is told hold a barcode.

 

[Jon von der Heyden]

Try using a double

 

[bensonsearch]

Try using a double

it seems to accept the length but i try using a vlookup with it and it fails for some reason. msgbox says all there but only searches down 2 records

 

[bensonsearch]

the code im testing in is

Dim barcode As Variant
Dim name As Variant
Dim stock As Variant
Dim barcode2 As Double 'error with this one
 
barcode = InputBox("Barcode Of Item?")
If barcode = "" Then
Exit Sub
Else
ThisWorkbook.Sheets("Money In").Range("c1").Value = barcode
If IsNumeric(barcode) Then
        barcode2 = ThisWorkbook.Sheets("Money In").Range("c1").Value
        ThisWorkbook.Sheets("Money In").Range("c1").Value = ""
       
        name = Application.VLookup(barcode2, Worksheets("Item").Range("A2:H15000"), 4)
         'MsgBox (barcode2)
            If IsError(name) Then
            MsgBox ("Sorry barcode not found, Please try again")
            Else
            TextBox1.Text = name
     
            stock = Application.VLookup(barcode2, Worksheets("Item").Range("A2:H15000"), 6)
                If IsError(stock) Then
                Exit Sub
                Else
                TextBox2.Text = stock
                TextBox1.Visible = True
                TextBox2.Visible = True
                Label9.Visible = True
                Label10.Visible = True
                Label12.Visible = True
                Label13.Visible = True
                End If
               
            End If
    Else
    MsgBox ("Please enter numbers only")
    Exit Sub
    End If
End If

 

[Jon von der Heyden]

You have invoked binary search Vlookup. You need to invoke linear search.

E.g:

name = Application.VLookup(barcode2, Worksheets("Item").Range("A2:H15000"), 4[B][COLOR="Red"], False[/COLOR][/B])

 

[bensonsearch]

You have invoked binary search Vlookup. You need to invoke linear search.

E.g:

name = Application.VLookup(barcode2, Worksheets("Item").Range("A2:H15000"), 4[B][COLOR=red], False[/COLOR][/B])

that would help, now it says barcode not found..... when i search my item sheet its there

 

[Jon von der Heyden]

Click on the barcode in the sheet (the one that you says does exist).

Open the VBE and in the immediate window* type:

?TypeName(ActiveCell.Value)

What result is displayed back in the immediate window?

*If the immediate window isn't already displayed hit Ctl+G.

 

[bensonsearch]

Click on the barcode in the sheet (the one that you says does exist).

Open the VBE and in the immediate window* type:

?TypeName(ActiveCell.Value)

What result is displayed back in the immediate window?

*If the immediate window isn't already displayed hit Ctl+G.

for some reason it calls it a string. Can i change that to be number keeping 0's? or can vlookup do string as well?

 

[Jon von der Heyden]

Well if they are always strings then you could use that data type for your barcode. If there are a mix of strings and doubles in the sheet then you'd be best to convert them all to doubles. The best way would be:

Select column A.
Go to Format Cells and choose custom format and enter 0000000000000
Next go to Text to Column > Delimited > Next > Finish

I don't like using a mix of data types but where your VlookUp fails you could also do a secondary lookup and use the CStr coercion function to convert from double to string:

name = Application.Vlookup(CStr(barcode),.......)

 

[bensonsearch]

Well if they are always strings then you could use that data type for your barcode. If there are a mix of strings and doubles in the sheet then you'd be best to convert them all to doubles. The best way would be:

Select column A.
Go to Format Cells and choose custom format and enter 0000000000000
Next go to Text to Column > Delimited > Next > Finish

I don't like using a mix of data types but where your VlookUp fails you could also do a secondary lookup and use the CStr coercion function to convert from double to string:

name = Application.Vlookup(CStr(barcode),.......)

I have converted to double via method above. thank you thank you thank you

help is very much appriciated