PDA

View Full Version : Solved: vba vlookup fix



danovkos
07-28-2010, 01:38 AM
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
07-28-2010, 01:47 AM
Is column C numbers, if so, cast vysledok to a number also.

danovkos
07-28-2010, 01:52 AM
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
07-28-2010, 02:28 AM
And is your lookup on a number?

danovkos
07-28-2010, 02:34 AM
yes, on number
but when i define vysledok as integer it return "error Overflow"
:(

Bob Phillips
07-28-2010, 03:12 AM
Try this



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:D")
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

danovkos
07-28-2010, 04:29 AM
yes it works perfectly
thank you very much :friends: :clap: