Solved: vba vlookup fix

[danovkos]

Hi all,
pls. i have a code, but it doesnt works as i wish...
This is only part of it, but here is something wrong..

what i try to do with this code is - From active cell i want only value without last 3 character. This value, i try to search in sheet ALL in column C. Now it works, but nothing is found (but the value is in column C). By debuging it return that value found has error 2042.

pls. can you help me?

Dim cif, cif2
Dim pval, pvall2 As String
Dim i
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim vysledok As String
Dim found As Variant
Dim Rng As Range
Dim LookFor As Range
Set Rng = Workbooks("file.xls").Sheets("ALL").Columns("C:D")
vysledok = Left(ActiveCell.Value, Len(ActiveCell.Value) - 3)
found = Application.VLookup(vysledok, Rng, 1, 0)
If IsError(found) = False Then GoTo cisloU

[Bob Phillips]

Is column C numbers, if so, cast vysledok to a number also.

[danovkos]

In column C is in rows 1 to 30 names and numbers (many diferent characters, header...). And from row 30 starts only numbers.

[Bob Phillips]

And is your lookup on a number?

[danovkos]

yes, on number
but when i define vysledok as integer it return "error Overflow"

[Bob Phillips]

Try this

[vba]

Dim cif, cif2
Dim pval, pvall2 As String
Dim i
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim vysledok As Variant
Dim found As Variant
Dim Rng As Range
Dim LookFor As Range
Set Rng = Workbooks("file.xls").Sheets("ALL").Columns("C")
vysledok = Left(ActiveCell.Value, Len(ActiveCell.Value) - 3)
If IsNumeric(vysledok) Then vysledok = CLng(vysledok)
found = Application.VLookup(vysledok, Rng, 1, 0)
If IsError(found) = False Then GoTo cisloU
[/vba]

[danovkos]

yes it works perfectly
thank you very much