Help with Autofill Formula w/ Dynamic Range

[nwd9s]

Hello-

I am trying to write the below macro (I have changed the sheet names and truncated the formula for privacy). I need help on the last line of the autofill, how can I write it since my starting point is an offset? The formula should be there for all the rows, but now I need it to autofill to the right for the number of columns in the data.

Sheets("Sheet1").Activate
    Dim NBVColumn As Long
        NBVColumn = Range("A" & Rows.Count).End(xlUp).Row
    Dim NBVRow As Long
        NBVRow = Range("A" & Columns.Count).End(xlToRight).Column
    Range("A3:A" & NBVColumn).End(xlToRight).Offset(2, 0).Formula = "=FORMULA"
    ActiveCells.AutoFill Destination:=Range("ActiveRange:ActiveRange" & NBVRow), Type:=xlFillDefault

Many thanks in advance for you help.

Regards,
nwd9s

 

[jasonb75]

Welcome to the board!

I'm not sure I fully understand exactly what you're trying to do, maybe something like

Dim NBVColumn As Long
Dim NBVRow As Long
Sheets("Sheet1").Activate
    NBVColumn = Cells(2, Columns.Count).End(xlToLeft).Column
    NBVRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A3", Cells(NBVRow, NBVColumn)).Offset(2, 0).Formula = "=FORMULA"

Please back-up data before trying this code or test on a dummy file!

If written correctly you don't need to autofill, the formula will automatically write to the whole range.

 

[nwd9s]

Thank you Jason-

The formula works, but does not offset it exactly how I want it. To demonstrate with an example, say I have data in columns A through D, I would want my formula to start in Column F, so it would be columns F through I and go down as many rows as the data in columns A through D.

But, the original data columsn might change, so my first example of the original data being columns A through D might change to be A through F, then I would like the formula to be column G through L, and to fill down as many rows as in A through F.

Hopefully this makes a bit more sense?

Thanks,
nwd9s

 

[jasonb75]

That makes more sense now, could still need a little trial and error though.

You had rows and columns mixed up in your original code which was causing some confusion.

Try

Dim NBVColumn As Long
Dim NBVRow As Long
Sheets("Sheet1").Activate
    NBVColumn = Cells(2, Columns.Count).End(xlToLeft).Column
    NBVRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(3, NBVColumn + 2), Cells(NVBRow, (NBVColumn * 2) + 2)).Formula = "=FORMULA"

and see how that looks, note the 2 red characters in the code above, this is assuming to check column A and row 2 to test the size of the exisiting data range, please amend as needed.

The rest, I think, should be ok.

 

[nwd9s]

Hi Jason-

Thanks for the reply. I entered your updated formula but am getting an error message relating to the last line. Any ideas as to why?

Thanks,
nwd9s

 

[jasonb75]

Typo in that line change NVBRow to NBVRow and all should work.

 

[nwd9s]

Yep, that simple. Thank you Jason for your help!!!