How to column length into a formula
 

I have the following code which works well:

Sheets("Outcomes").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R100C2,2)"
With ActiveSheet
lastrowo = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").AutoFill Destination:=.Range("J2:J" & lastrowo),
_Type:=xlFillDefault
End With

However I arbitary chose the value 100 in the third row, and I would like to
change this to 'lastrowp' which I have determined elsewhere.

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&"lastrowp"C2,2)"

doesn't work, and neither does:

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&(lastrowp)C2,2)". Help!

How to column length into a formula
 

Sheets("Outcomes").Select
Range("J2").FormulaR1C1 = "=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R" & lastrowp
& "C2,2)"
With ActiveSheet
lastrowo = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").AutoFill Destination:=.Range("J2:J" & lastrowo), _
Type:=xlFillDefault
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kanga 85" wrote in message
...
I have the following code which works well:

Sheets("Outcomes").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R100C2,2)"
With ActiveSheet
lastrowo = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").AutoFill Destination:=.Range("J2:J" & lastrowo),
_Type:=xlFillDefault
End With

However I arbitary chose the value 100 in the third row, and I would like
to
change this to 'lastrowp' which I have determined elsewhere.

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&"lastrowp"C2,2)"

doesn't work, and neither does:

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&(lastrowp)C2,2)". Help!

How to column length into a formula
 

Thanks Bob

"Bob Phillips" wrote:

Sheets("Outcomes").Select
Range("J2").FormulaR1C1 = "=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R" & lastrowp
& "C2,2)"
With ActiveSheet
lastrowo = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").AutoFill Destination:=.Range("J2:J" & lastrowo), _
Type:=xlFillDefault
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kanga 85" wrote in message
...
I have the following code which works well:

Sheets("Outcomes").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R100C2,2)"
With ActiveSheet
lastrowo = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J2").AutoFill Destination:=.Range("J2:J" & lastrowo),
_Type:=xlFillDefault
End With

However I arbitary chose the value 100 in the third row, and I would like
to
change this to 'lastrowp' which I have determined elsewhere.

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&"lastrowp"C2,2)"

doesn't work, and neither does:

ActiveCell.FormulaR1C1 =
_"=VLOOKUP(RC[-8],'Paste-AB'!R1C1:R&(lastrowp)C2,2)". Help!