Autofilling formulas with variables

[Jameo]

Guys, sorry for the double post, but this is a follow up to my question here:

http://www.mrexcel.com/forum/showthread.php?t=557005

That seems to have got lost in the stream.

As a recap:

I am using the following line of code to populate a series of formulas accross a row:

Sheets("CriticalOTPressures").Cells(3, TpAP).Formula = "=AllOTCalc!" & GetRange(FtV, 2) & ""

and the function:

Public Function GetRange(intColumn As Integer, intRow As Integer) As String
    GetRange = Replace(Replace(Cells(intRow, intColumn).Address, "$", ""), ":", "")
End Function

My code works fine, but now I need to autofill the formulas down to the last row, again using the variables for the column index

LRow is as follows

LRow = Range("A" & Rows.Count).End(xlUp).Row

and normally I would use the code:

Range("B3").AutoFill Destination:=Range("B3:B" & LRow), Type:=xlFillDefault

to fill the formula from B3 down to the last row. However, I can not figure out how to insert the column variables into the above code.

My getrange function return the whole cell, for instance B3, which works fine for replacing everything but "B3:B"

Any ideas how i can get around this? Last question for the day I promise

Cheers all

 

[Andrew Poulsom]

If you want to use numbers for columns use the Cells property inside the Range property. For example:

Range(Cells(3,2), Cells(10,4))

returns B3:D10.

 

[Jameo]

Thanks for the reply Andrew,

I have tried that, but think it is failing at:
Range("B3:B" & LRow), I think

My attempt is:

Range(Cells(3, TpAP)).AutoFill Destination:=Range(Cells(3, TpAP) & LRow), Type:=xlFillDefault

 

[Andrew Poulsom]

Look at my example. I use the Cells property twice - first cell then last cell.

Range(Cells(3, TpAP)).AutoFill Destination:=Range(Cells(3, TpAP), Cells(LRow, TpAP)), Type:=xlFillDefault

 

[Jameo]

Hi Andrew,

Sorry but I am still getting an error using that formula. Putting in a break I have checked the variables to see that they are returning correctly, which they are. LRow=15 and TpAP=2, which is as they should be.

Not really sure what is going on, I thought the above would work.

I am getting "method range of globel object failed"

 

[Andrew Poulsom]

Tested in Excel 2003:

Sub Test()
    Const TpAP As Long = 2
    Const LRow As Long = 15
    Cells(3, TpAP).Value = 1
    Cells(3, TpAP).AutoFill Destination:=Range(Cells(3, TpAP), Cells(LRow, TpAP)), Type:=xlFillDefault
End Sub

 

[Jameo]

Thanks Andrew, realised it was trying to run on the incorrect sheet. Cheers mate