VBA to copy formula from one cell to variable range

[rasinc]

I am trying to copy a cell formula from one cell to a column that starts at cell G6 but is variable length.

I created a macro and the copy worked but with a variable number of rows, the code created may not work.

So I have no idea how to come up with the formula for the destination in the following code

    Sheets("Setup").Select
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PriceLists").Select
    Range("G6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("G6:G90")  'here I believe may be the issue
    Range("G6:G90").Select

So I want to be able to copy but down as many rows as may be there. However, I do not know how to determine the range to copy based on what is in column F.

Any help is appreciated.

TIA, rasinc

 

[SydneyGeek]

Assuming that you want to fill down from G6, you could use:

  Dim lngLastRow as Long 'declare a variable for the last row

  lngLastRow = Range("F" & Rows.Count).End(xlUp).Row
  Range("G6:G" & lngLastRow).FillDown

Denis

 

[Triangle]

I've been working on this same problem myself. I have filled data in columns B, F, and I that can be filled down to a variable point (but all three columns are filled down to the SAME variable point). I need the other columns to fill down to where the other 3 columns stop.

This is the code I found that many sites suggest:

Selection.AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)

I understand WHAT this is doing (it counts the filled rows in column B and autofils column A down to the same point). However, I get a runtime error saying "Autofil method of Range Class failed"

This seems like it should work, and I've getting very frustated trying to figure out why it does not. Perhaps it will work for you, but I would love some suggestions.

 

[Triangle]

Just found this, and it seems to be working for me:


Dim lastrow As Long
lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & lastrow), Type:=xlFillDefault
End Sub

 

[rasinc]

Thanks Denis. It worked very nicely.

 

[SydneyGeek]

@rasinc,

You're welcome.

@Triangle,

I have used Autofill before (that's basically what you get when you use the recorder), but I prefer FillDown. It's a bit cleaner to use...

By the way, if you want to write a formula to multiple cells you can record that formula, then use VBA to write it to all the target cells simultaneously.

1. Start the recorder, select the cell, press the F2 key, press Tab, stop the recorder.

Assuming you did that for G6 you will see something like...

Range("G6").Select
Selection.FormulaR1C1="whatever the formula was in the cell..."
Range("H6").Select

This can be changed to...

  Dim lngLastRow as Long 'declare a variable for the last row

  lngLastRow = Range("F" & Rows.Count).End(xlUp).Row
  Range("G6:G" & lngLastRow).FormulaR1C1="whatever the formula was in the cell..."

Denis

 

[AM2014]

Hello, I am trying to use the same code to copy down a formula a certain number of rows from a pre selected range of cells.

    'Pastes format into last row
    Range("B6:M6").Select
    Selection.Copy
    Range("M6").Select
    Selection.End(xlDown).Offset(0, -11).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Copies down formulas on Orders tab
    Windows("CAL.CSV").Activate
    Range("A1").Select
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlDown).Row
    Windows("PFP.xlsm").Activate
    Range(ActiveCell).AutoFill Destination:=Range(ActiveCell.Offset(LR))

This is counting the number of rows in the CAL.CSV file then going back to the PFP.xlsm file and filling the formulas (which are already selected) down the specific number of counted rows in the CAL.CSV file. Is where any way of copying the formulas down in a preselected range? I should mention that the initial row that needs to be copied down will change each time, that is why it is preselected.

 

[SydneyGeek]

Try:

Range(ActiveCell,Cells(LR,ActiveCell.Column)).FillDown

Denis

 

[AM2014]

Thanks for the reply!

I seem to be still having issues with this Macro. First, when I used this code originally with the "Cells(LR,ActiveCell.Column)" portion it would only fill the first leftmost cell down three rows, leaving the remaining 10 cells in that row untouched. I changed the code to "Range(ActiveCell,Cells(LR,ActiveCell.Row)).FillDown" (changed the .column to .row) and this filled all of the 11 cells in that row down three rows. So, the formulas are being copied down three rows, but there lies the second problem, only three rows. The CAL.CSV has 16 rows, thus the formula should be copied down all 16 rows instead of 3. Next week the CAL.CSV rows will change, so the range copied down needs to reflect that.

Any idea why this is occuring? Thanks in advance!

 

[rasinc]

I was just working on some programming an hour ago I used

Range("A1").Copy
Range("A2:A" & lngLastRow).PasteSpecial Paste:=xlPasteAll

to copy the formula that will always be in A1 to the rest of the column. lngLastRow is derived from a Function that looks for the last row in Column B in this case. The root of it is

FindLastRow = Range(strStartCol & Rows.Count).End(xlUp).Row

It's a different approach but it works.