How to column length into a formula

K

Kanga 85

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!
 
B

Bob Phillips

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)
 
K

Kanga 85

Thanks Bob

Bob Phillips said:
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 said:
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!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 3
Macro that deletes and pastes filterd rows and then some 2
Vlookup in vba - how to use absolute rows not relative 1
AutoFill using a macro 5
Help with my VBA and formula 8
Advanced Filter works in recorder but not while running macro 5
Creating Pivot Table with Macro using Multiple Sheets 0
relatetive reference-to perform a series of cal and return result 2

Top