R1C1 lookup and variable incrementation

[mmetzinger]

Ok, I am trying to figure out how to increment a R1C1 reference. I am running this code through a loop so I have to move columns for each loop.

Here is an example of my attempted code

Dim ProductCol As Integer
ProductCol = 1

ActiveCell.FormulaR1C1 = "=Original!R[ProductCol]C & Original!RC[ProductCol]"

Excel doesn't like this code but for the life of me I can't figure out how to input the variable into the lookup formula.

Any ideas on how to use the variable in the lookup?

Thanks,
Matt

 

[ChrisM]

You have to break up your "" "" to get the variables recognized, otherwise VBA interprets your entire command as one long string (ie it doesn't know that ProductCol is a variable)

ActiveCell.FormulaR1C1 = "=Original!R[" & ProductCol & "]C & Original!RC[" & ProductCol & "]"

 

[mmetzinger]

Thanks! How do you make it an absolute reference now? The column keeps changing on me as I move to the next cell to the right. I tried to remove the brackets but this only locked the cell not the column.

You have to break up your "" "" to get the variables recognized, otherwise VBA interprets your entire command as one long string (ie it doesn't know that ProductCol is a variable)

ActiveCell.FormulaR1C1 = "=Original!R[" & ProductCol & "]C & Original!RC[" & ProductCol & "]"

 

[ChrisM]

If you want to hardcode the column then just put the column number right in to the formula instead of using ProductCol variable.

But I may not understand what you are trying to do. Can you post a bit more code?

 

[Jonmo1]

Not sure if this is the problem.... But..

I notice that you are using a variable called ProductCol in the ROW portion of the R1C1 reference.
The variable's name indicates to me that it is intended for use in the COLUMN section of the R1C1 reference...

Maybe I missed something though...

 

[mmetzinger]

Yep, that was the problem! I noticed it after staring for a while! My final code snippet for this piece is below:

ActiveCell.FormulaR1C1 = "=Original!R" & ProductRow & "C" & ProductCol & " & Original!RC" & ProductAttb & ""

Thanks for the help!

Matt

Not sure if this is the problem.... But..

I notice that you are using a variable called ProductCol in the ROW portion of the R1C1 reference.
The variable's name indicates to me that it is intended for use in the COLUMN section of the R1C1 reference...

Maybe I missed something though...