Error referencing lastRow

[deduwa]

Hi

I am trying to input a formula into column V as below. I get a Compile error: Expected end of statement message pop up.

Dim lastRow As Long
Set fnd = Range("A:A").Find("Currency", , , xlWhole, , , False, , False)
If fnd Is Nothing Then Exit Sub
lastRow = Cells(Rows.Count, "D").End(xlUp).Row

Range("V2:V" & fnd.Row - 1).FormulaR1C1 = "=IF(AND(RC[-18]=""POUND STERLING"",Manual!R1C6=1),(RC[-8]+RC[-7])/"R"&lastRow+53)*"Q"&lastRow+53"


It doesn't like the " around the R and around the Q.

What I am trying to achieve is in column V input a formula referencing a cell that is located 53 cells below lastRow of column R and 53 cells below lastRow of column Q

How can I achieve this?

Thanks

 

[WaterGypsy]

I think you need another pair of brackets

Range("V2:V" & fnd.Row - 1).FormulaR1C1 = "=IF(AND(RC[-18]=""POUND STERLING"",Manual!R1C6=1),((RC[-8]+RC[-7])/"R"&lastRow+53)*"Q"&lastRow+53)"

 

[steve the fish]

You cant use A1 (eg column R) references in the middle of a R1C1 formula.

 

[deduwa]

You cant use A1 (eg column R) references in the middle of a R1C1 formula.

Does that mean what I am trying to achieve isn't possible?


(Also WaterGypsy - the extra brackets gave the same error message)

 

[steve the fish]

Its not possible to mix the reference styles so you need to convert your column references into R1C1 style. Give us an example of the formula you want to place into V2 complete with any absolute referencing (dollar signs).

 

[deduwa]

So, cell V2 should show the following formula;

=IF(AND(D2="POUND STERLING",Manual!$F$1=1),(N2+O2)/$R$319)*$Q$319

Note that for this example - the table with dynamic number of rows finishes at row 266. (Call this the primary table)

Cell R319 & Q319 contains numbers (This is a smaller secondary table that always sits below the primary main table above)

There are values in cell R319 and Q319 because a different macro always inserts them below the primary table, i.e. at lastRow + 53


Hope this helps

Thanks

 

[Joe4]

Does that mean what I am trying to achieve isn't possible?

It's an "either or" proposition.
Either you use R1C1 referencing in your formula, or you use the "direct" range referencing (i.e. "A1", "V2", etc).
And never the two shall you mix!

Note that if you use square brackets in R1C1 referencing, that indicates relative referencing (i.e. offset from the cell the formula is going in).
But you can "hard-code" it to an exact range by leaving out the square brackets.

See here for an explanation: https://tduhameau.wordpress.com/2012/09/27/the-beauty-of-the-r1c1-reference-style/

 

[steve the fish]

So you want the 319 to be lastRow plus 53 but absoluted? Yes?

Range("V2:V" & fnd.Row - 1).FormulaR1C1 = "=IF(AND(RC[-18]=""POUND STERLING"",Manual!R1C6=1),(RC[-8]+RC[-7])/R" & lastRow + 53 & "C18)*R" & lastRow + 53 & "C17"

 

[deduwa]

So you want the 319 to be lastRow plus 53 but absoluted? Yes?

Range("V2:V" & fnd.Row - 1).FormulaR1C1 = "=IF(AND(RC[-18]=""POUND STERLING"",Manual!R1C6=1),(RC[-8]+RC[-7])/R" & lastRow + 53 & "C18)*R" & lastRow + 53 & "C17"

Hi Steve - your suggestion gives the following formula in cell V2;

=IF(AND(D2="POUND STERLING",Manual!$F$1=1),(N2+O2)/$R$53)*$Q$53

I need the formula to look like this;

=IF(AND(D2="POUND STERLING",Manual!$F$1=1),(N2+O2)/$R$319)*$Q$319


(Just to be clear row 319 is 53 rows after lastRow)

 

[steve the fish]

That means lastRow has not been assigned its value. You need to run it in conjunction with your other code.