Dynamic Vlookup by VBA

[sshanley]

Hi all

Need to accumualte values from a repeating Vlookup using VBA but cannot get the Vlookup to take dynamic ranges

Sub test()
Dim myrange As Range
Set mytable = Worksheets("sheet2").Range("A1:I10")
Dim accum, mycol, myrow, currentval, Collimit As Integer
'Inial position to look
mycol = 4
myrow = 1
accum = 0
On Error GoTo Tag:
colllimit = 0
Set myrange = Cells(myrow, mycol)

'Inner loop - set the value in the first cell, look across the columns
Do
currentval = Application.WorksheetFunction.VLookup(myrange, mytable, mycol, 0)
accum = accum + currentval
mycol = mycol + 1
Loop Until mycol = colllimit
ActiveCell.Value = accum
Tag:
MsgBox (Err.Number & " " & Err.Description)
End Sub


Gives error 1004, unable to get the vlookup propery of the worksheet funciton class

any ideas??
can I use this format?
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C[-9],Sheet2!R[-3]C[-9]:R[6]C[-1],4,0)"

 

[venkat1926]

Perhaps I am little confused.
in vlookup the reference cell (that is your myrange)should be the FIRST column of the table (your mytable).otherwise you have to use index----match function.

your myrange is within mytable. I do not know what is the idea. are they in different sheets. will it be possible for you to give a small extract of your sheet.

 

[sshanley]

The issue for me is how to pass the vlookup the first and third
arguements made up from variables
normally a vlookup is set based on non changing cell references
but here I want to to change the the arguements a number of times
and accumulate the total
I keep getting error indicated
can someone post an example of vlookup showing the first arguements
made up of something like:
cells(mrow,mcol) where mrow and mcol are variables

I can changes the row and column within the loop as shown

This
S