Range variable question

[davehunter]

I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?

Thanks
Dave

[VBA Noob]

Just a reminder to post to the right group next time.

Maybe

Range("C1:C" & x)

to find last then see links

http://www.rondebruin.nl/last.htm

http://www.ozgrid.com/VBA/ExcelRanges.htm

VBA Noob

[davehunter]

Thanks VBA noob. I have the findlast sorted already.

I am now trying to add a similar piece of code into a more complex section:

Range("A1").FormulaR1C1 = "=IF(ISERROR(MATCH(old!$C$1:$C$x,new!$C$1:$C$x,0)),old!$C$1:$C$x,"")"

Can you tell me how to make x overallLastRow in this instance?

Thanks Again
Dave

[VBA Noob]

Instead of FormulaR1C1 try Formula

You may need to post the actual formula but try something like

Please Login or Register  to view this content.

VBA Noob

[davehunter]

Instead of FormulaR1C1 try Formula

You may need to post the actual formula but try something like

Please Login or Register  to view this content.

VBA Noob

I get Run-Time Error '1004'

Application defined or object defined error

Please Login or Register  to view this content.

I have substituted x for overallLastRow, but have this error.

[VBA Noob]

Your still using the R1C1 format

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

You may need to post the actual formula but try something like

Then post the formula as it would be displayed in a cell.

VBA Noob

[davehunter]

I now have:

Please Login or Register  to view this content.

Still have the same error.

[VBA Noob]

Which line does it stop at.

See if this helps

Please Login or Register  to view this content.

VBA Noob

[davehunter]

Ah, thanks. I've spotted it.

See amendment in bold:

Range("A1").Formula = "=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" & overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"")"

Range("A1").Formula = "=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" & overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"""")"

[VBA Noob]

Glad you got it sorted

Double quotes get you every time.

I usually write the formula then turn on the recorder and enter a ' in front of the formula to get

Please Login or Register  to view this content.

Then remove the R1C1 and the '

HTH

VBA Noob