formulaR1C1 to reference specific range?

[mfisher316]

I have a counter loop macro written that cuts/pastes columns, and inserts rows if specific conditions apply. Here's the dilemma, say column C is my active cell. I need a vlookup formula that looks at column A, next row, against data in the range A4:B55 on a separate worksheet. I have tried the following but none have worked:

activecell.formulaR1C1= "=vlookup(+R[1]C[-2],Costcodes!$A$4:$B$55,2)" - can you not have specific range reference w/ an r1c1 formula??

I have set variables x and y to be eqal to "4 - counter" and "55-counter" respectively (so that it always pulls from the correct range) with the following formula:
activecell.formulaR1C1="=vlookup(+R[1]C[-2],Costcodes!R[x]C[-2]:R[y]C[-2],2)"
but that hasn't worked either. Any suggestions?

Thanks in advance!

 

[mfisher316]

Sorry, typo in the last formula: should finish with ...R[y]C[-1],2)"

 

[Oaktree]

If x and y are variables, they shouldn't be inside the quotes. Try:

activecell.formulaR1C1="=vlookup(+R[1]C[-2],Costcodes!R[" & x & "]C[-2]:R[" & y & "]C[-1],2)"

 

[Richard Schollar]

Hi

As you are accessing the FormulaR1C1 property of the cell, you shouldn't attempt to use A1-style references in the formula string.

Within R1C1 style referencing, the [] brackets denote a relative reference - since I assume the range on Costcodes sheet is fixed, you need to drop these references. Also, you need to concatenate the variable values with the formula string for Excel to recognise the variables as variables, and not simply part of the string. Hence:

activecell.formulaR1C1="=vlookup(R[1]C[-2],Costcodes!R" & x & "C1:R" & y & "C2,2)"

 

[mfisher316]

That worked!!! Thank you so much!