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.
ActiveCell.FormulaR1C1 = "Process"
.VerticalAlignment = xlCenter
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