Formula Macro

[Guest]

I have a formula in cell C2. I want to create a macro in VBA to fill down
that formula into cells C3:C100. How would I write that code?

 

[Dave Peterson]

range("C3:c100").formula = range("C2").formula

or
range("C2").autofill _
destination:=range("C2:C100")

 

[Guest]

Hi Again!

Thanks for the response. After I posted this I was tinkering around with it
and I got it to work using this code:

Worksheet("Sheet1").Range("C2:C100").FillDown

I assume it does the same thing as what you posted?

 

[Dave Peterson]

In this case, yep.

But .autofill has lots more options that you may find exciting <bg> someday.

 

[Guest]

Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
..Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub

 

[Bob Phillips]

I don't have a query, but the rest works first time for me.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
.Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub

 

[Dave Peterson]

Maybe...

Rightclick on your data|query range and select Data Range Properties.
Try changing the "enable background refresh" to off.

In code, something like:
worksheets("Sheet1").QueryTables(1).Refresh Backgroundquery:=false

If c2 is part of that querytable range, that is.

Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
.Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub

 

[Guest]

Changing that data range property to off worked.

Thanks Dave!