CHR(36) VBA in VLOOKUP Formula R1C1 ABSOLUTE CELL REFERENCE

[fish462]

I have two questions. Referencing the following code:

ActiveCell.Offset(0, 1).Activate
For k = 0 To 10
ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP([B]RC[-42][/B],DSOH" & aRegions(i, 1) & ",4,FALSE)-RC[11]"
Next K

1. I am trying trying to get make the RC[-42] an absolute by putting a dollar sign in front of it. I am pretty sure I need to use the ascii CHR(36) but I can't seem to get the syntax correct. I have tried just about everything imaginable but it won't compile. The one I thought would work is " & chr(36) "RC[-42] but to no avail.

2. Inside the VLOOKUP, I need the column returned to increment by 1 as it loops through. What is the best way to accomplish this?

I have been combing the internet for about 2 hours and can't seem to find an example.

 

[Norie]

With R1C1 notation you don't use $ to make a reference absolute.

What you do is remove any [] and specify the column and row, for example R10C2 is $B$10.

Which cell is RC[-42] supposed to reference?

 

[fish462]

With R1C1 notation you don't use $ to make a reference absolute.

What you do is remove any [] and specify the column and row, for example R10C2 is $B$10.

Which cell is RC[-42] supposed to reference?

A3
I need it go down the column A3, A4, A5 etc.

 

[Norie]

Not sure why you need the reference absolute but try this.

ActiveCell.Offset(0, 1).Activate
For k = 0 To 10
ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(R" & k+3 & "C1,DSOH" & aRegions(i, 1) & ",4,FALSE)-RC[11]"
Next K

 

[fish462]

Not sure why you need the reference absolute but try this.

ActiveCell.Offset(0, 1).Activate
For k = 0 To 10
ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(R" & k+3 & "C1,DSOH" & aRegions(i, 1) & ",4,FALSE)-RC[11]"
Next K

That worked. As did

ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(RC1,DSOH" & aRegions(i, 1) & ",4,FALSE)-RC[11]"

Any thoughts on question 2?

 

[Norie]

RC1 will work if you were putting the formula in rows 3,4,5... etc.

To change the column returned try this.

ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(RC1,DSOH" & aRegions(i, 1) & ",ROW()+1,FALSE)-RC[11]"

 

[fish462]

RC1 will work if you were putting the formula in rows 3,4,5... etc.

To change the column returned try this.

ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(RC1,DSOH" & aRegions(i, 1) & ",ROW()+1,FALSE)-RC[11]"

That incremented the column as it went down the rows. I need it to increment as it goes from column to column.

 

[fish462]

I found a solution here: http://www.mrexcel.com/forum/excel-...col_index_num-when-copying-other-columns.html

Thanks for the help!!!!

 

[Norie]

If the formula is going across columns how can RC1 work for the first part?

It will always refer to the first column of the row the formula is going in, not A3, A4, A5... etc as you mentioned in post #3.