I have to combine / add up 2 regions of data.
To do so I have made a bit of code that determines the "old value" in a cell, then copies the 2nd value in the
activecell, and as last, adds up the previous value.
This works if I use a fixed number for the column_index.
However, this column_number changes for each column.
Therefore I want to use the value of "j" as column_index.
Simply adding this to the code doesn't seem to work.
This is the code as I thought would do the trick:
Dim i As Long
Dim j As Integer
For j = 1 To NumberOfColumns ‘NumberOfColumns is determined in 1st part
For i = 1 To NumberOfRows ‘NumberOfRows is determined in 1st part
Dim OldValue as long
OldValue = ActiveCell.Value
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1, C10:C17, j + 1, FALSE)"
Dim NewValue As Long
NewValue = ActiveCell.Value
ActiveCell = OldValue + NewValue 'This replaces the formula with a value
I've tried every trick I know to get the column_index working: adding a ", declaring a new variable, (, [, etc etc.
None of these work.
What can I do to make it work ??
Thanks in advance