PDA

View Full Version : Vlookup



MarkNumskull
08-13-2009, 01:27 PM
Hi,

Turns out vlookup in vba can be a bit of nightmare compared to normal excel vlookup if your like me and never used it before! Iv been trying to get the following one to work and have no yet succeeded so i have resorted to swearing at the computer, obv this hasn't helped me so perhaps one of you could help out? what am i doing wrong with the following?

ActiveCell.Offset(0, 3).Value = Application.WorksheetFunction.VLookup(ActiveCell.value, thisworkbook.Sheets("Sheet2").Range("$b$2:$h$65000"), 3, False)

what i want it to do is take the active cells value say 1 then go into the table on worksheet 2 and find the corresponding value and input it into the cell 3 rows away from the activecell on worksheet 3.

cheers,

Mark

Bob Phillips
08-13-2009, 01:47 PM
That code does exactly what you describe.

MarkNumskull
08-13-2009, 01:51 PM
You can see my frustration, i cant see any reason why it wouldn't work but each time i run the sub i get a 'runtime error 9' coming up. The rest of the code is perfect as it runs perfectly well until i put this in

MarkNumskull
08-13-2009, 01:54 PM
perhaps its a problem with the bit of code preceding it?

ActiveCell.Value = Date
ActiveCell.Offset(0, 1).Value = Time
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, ThisWorkbook.Sheets("sheet2").Range("$b$2:$h$65000"), 3, False)

mdmackillop
08-13-2009, 02:23 PM
Try

ActiveCell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(ActiveCell, _
ThisWorkbook.Sheets("sheet2").Range("$b$2:$h$65000"), 3, False)

MarkNumskull
08-13-2009, 02:38 PM
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValues
ActiveCell.Resize(, 1).Select
ActiveCell.Offset(0, 2).Value = Application.WorksheetFunction.VLookup(ActiveCell, Sheets("pricelist").Range("$c$2:$h$65000"), 4, False)

got it going with this, i think i was having problems as i had two cells selected at once so had to resize to make the activecell just the cell i was looking rather than 2 cells eg b2:c2. Thanks for your help though, i think that helped with my vlookup code!

I have now stopped swearing and shall go back to listening to mongolian throat singers.

Thanks,

Mark

mdmackillop
08-13-2009, 03:10 PM
You can only have one ActiveCell, even when merged. The problem was the .Value in ActiveCell.Value