Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: VBA - Insert Formula, then copy down column

1. I agreed to these rules
Join Date
4th January 2012
Posts
15

## VBA - Insert Formula, then copy down column

Hi There,

Searched far and wide and cannot figure out how to:

Have this formula:

=IF('Utensils-Portions'!A2="","",'Utensils-Portions'!A2)

Auto entered via a VBA Macro into cell L5 of the active sheet. Then once entered, copy and paste that formula down the column to cell L106 (all via one VBA Macro).

So cell L106's value would be:

=IF('Utensils-Portions'!A103="","",'Utensils-Portions'!A103)

THANK YOU!
Last edited by AAE; February 4th, 2012 at 07:11. Reason: remove code tags from formula

Excel Video Tutorials / Excel Dashboards Reports

2. Established Member
Join Date
8th July 2004
Posts
679

## Re: VBA - Insert Formula, then copy down column

This might work

VB:
```Sub DoesThisWork()
i = 2
For Each c In Range("L5:L106")
c.Value = "=IF('Utensils-Portions'!A" & i & "="""","""",'Utensils-Portions'!A" & i & ")"
i = i + 1
Next
End Sub

```
HTH
John

Excel Video Tutorials / Excel Dashboards Reports

3. Established Member
Join Date
8th July 2004
Posts
679

## Re: VBA - Insert Formula, then copy down column

Sorry, came through twice.

This might work

VB:
```Sub DoesThisWork()
i = 2
For Each c In Range("L5:L106")
c.Value = "=IF('Utensils-Portions'!A" & i & "="""","""",'Utensils-Portions'!A" & i & ")"
i = i + 1
Next
End Sub

```
HTH
John
Last edited by jolivanes; February 4th, 2012 at 06:46. Reason: Double posted

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: VBA - Insert Formula, then copy down column

MikeyG,

Please do not use code tags with formulas, they are reserved for use when posting VBA code.

No need for a loop.

VB:
```Range("L5").Formula = "=IF('Utensils-Portions'!A2="""","""",'Utensils-Portions'!A2)"
Range("L5").AutoFill Destination:=Range("L5:L106")

```
Note the use of the double sets of quotes in the formula - required by VBA.

5. Established Member
Join Date
8th July 2004
Posts
679

## Re: VBA - Insert Formula, then copy down column

@AAE.
I tried to adapt the following but with my limited knowledge, I could not do it and that's why I
ended up with the looping code. Without a loop is better of course.
The RC notation has me stumped.

VB:
```Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 15) _
.FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
End Sub

```
Thanks and Regards
John

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: VBA - Insert Formula, then copy down column

@jolivanes,

Not trying to be persnickety, but this is MickeyG's thread.

Maybe: Copy formula with !R1C1 notation to other cells

Thanks,

7. Established Member
Join Date
8th July 2004
Posts
679

## Re: VBA - Insert Formula, then copy down column

@AAE
Sorry about that. Was trying to explain what I tried.
Won't happen again.

Thanks and Regards
John

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
4th January 2012
Posts
15

## Re: VBA - Insert Formula, then copy down column

AAE,

your code worked perfectly! Problem solved, thanks much!

Sorry about the code tags for formulas. Won't happen again.

Have a great one!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno