Variable cell input in VLOOKUP

[Sankekur]

When using the VLOOKUP function in Excel is it possible to look up a value that is in a cell on the current sheet but of which the indexes (or at least one of them) is variable.

 

[schielrn]

Welcome to the Board!

You can set all the variables in the vlookup formula to be based off a cell value if you really want to?

Not really clear on what specifically you want. Can you better explain or myabe type out what the formula is you think you want?

Hope that helps.

 

[Sankekur]

A part of the code follows. What I am doing in the macro is filling a row with with values that are looked up from another sheet in the same workbook. The problem is that the row that has to be filled is is below a pivot table (also generated by the same macro) and the pivot table is variabe in size thus the index of the row to be filled is viariable. Further the value that has to be looked up is contained in the pivot tabel thus the row and colomn referencing in the VLOOKUP command is also has to be variable. In the code the viariable j is of the type interger and returns the size (height) + 1 of the pivot table generated by the macro, ie. the first open cell below the pivot table. The value of j is then changed to the negative and stored in viariabe k (also of type interger). What I am trying to do is to use this value of k to in relative referencing of a specific cell in the VLOOKUP command. But so far I have been unsuccessful. The error given when the macro is run is: Application-defined or object-defined error. Given in the line where the VLOOKUP command is used. When i run the code without the variable and just substitute the values manually (substitute k with its value) the macro works fine.


Cells(j, 1).Select
ActiveCell.FormulaR1C1 = "Process"
With Selection
.VerticalAlignment = xlCenter
End With
k = -1 * j
For Counter = 2 To 74
Set curCell = Cells(j, Counter)
curCell.FormulaR1C1 = "=VLOOKUP(R[k]C,EOP!R5C17:R236C25,2,0)"
curCell.Orientation = 90
Next Counter

 

[schielrn]

I am not seeing where j is being given a value, so I am just assuming that you are correctly giving j the value? When it errors, do you get an option to debug? If not maybe try putting a breakpoint in just before the formula line of code and see what the values of i and j are at that point?