ExcelBanter - Formula Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Formula Macro (https://www.excelbanter.com/excel-discussion-misc-queries/127978-formula-macro.html)

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?

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

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

Secret Squirrel wrote:

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

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" wrote:

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

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

Secret Squirrel wrote:

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

In this case, yep.

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

Secret Squirrel wrote:

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" wrote:

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

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

Secret Squirrel wrote:

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

--

Dave Peterson

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" wrote:

In this case, yep.

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

Secret Squirrel wrote:

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" wrote:

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

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

Secret Squirrel wrote:

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

--

Dave Peterson

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)

"Secret Squirrel" wrote in
message ...
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" wrote:

In this case, yep.

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

Secret Squirrel wrote:

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" wrote:

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

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

Secret Squirrel wrote:

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

--

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.

Secret Squirrel wrote:

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" wrote:

In this case, yep.

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

Secret Squirrel wrote:

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" wrote:

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

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

Secret Squirrel wrote:

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

--

Dave Peterson

--

Dave Peterson

Changing that data range property to off worked.

Thanks Dave!

"Dave Peterson" wrote:

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.

Secret Squirrel wrote:

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" wrote:

In this case, yep.

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

Secret Squirrel wrote:

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" wrote:

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

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

Secret Squirrel wrote:

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

--

Dave Peterson

--

Dave Peterson