Simple Vlookup not working within VBA code

[heathball]

Im trying to find out why this is not working, and google is not having a good day.

i have a simple vba code, without any complications, and i"m just wondering what I'm missing.

Im in cell "O2" and i just cannot get this up and running......if anyone knows why i would really appreciate it.



Range("O2") = Application.WorksheetFunction.VLookup(N2,'month-year look list'!A:B,2,false)

 

[Eric W]

Try:

Range("O2") = Application.WorksheetFunction.VLookup(Range("N2"), Sheets("month-year look list").Range("A:B"), 2, False)

or

Range("O2") = Evaluate("VLookup(N2,'month-year look list'!A:B,2,false)")


If you use the WorksheetFunction version, you need to give it the parameters in the VBA format. If you use Evaluate, you can just give it the same formula you'd use on the sheet. Both ways have their place.

 

[heathball]

Im doing this

Range("O2").Select
Range("O2") = Application.WorksheetFunction.VLookup(Range("N2"), Sheets("month-year look list").Range("A:B"), 2, False)
ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))


its producing the correct result in cell O2, but it seems to be pasting the value, not leaving the formula in the cell, so when the autofill operates, its pasting the value in O2-- in every cell down the page?

do you know why that would be occurring?

 

[Eric W]

Well, yes, I thought you just wanted the value inserted. If you want to actually insert a formula, you need to use the .Formula method. Try this:

Range(Range("O2"), Range("N2").End(xlDown).Offset(, 1)).Formula = "=VLOOKUP(N2,'month-year look list'!A:B,2,FALSE)"

This handles all 3 lines of your last post.

 

[heathball]

this is producing the actual formula in text - each cell down the page is looking like this =VLOOKUP(N2,'month-year look list'!A:B,2,FALSE)

how do i get the cell to act like a normal Vlookup does, with the result showing in the cell, but the cell containing the formula?

 

[Eric W]

Sounds like you have column O formatted as Text. Change it to General, delete everything in it, then run the macro again.

 

[heathball]

ahh thanks very much

interesting what you did by adding the End(xlDown).Offset(, 1)) within the formula. i will check this out

thanks for your time!