MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy down formula with vba

  Asked By: Bara    Date: Feb 24    Category: MS Office    Views: 31854

I'm looking to do this same thing (copy formula down to the length of
data in an adjacent column) but with vba. I know how to use 'Range
(ActiveCell, ActiveCell.End(xlDown)).Rows.Count' to get the number of
rows but can anyone tell me the code to fill the range with a formula?

For example, Column A contains a range of data that could be any
lenght from 10 rows to maybe 3000 rows long. I want to use vba to
insert a formula in column B and have it automatically extend to the
length of the data in column A. How is this done?



8 Answers Found

Answer #1    Answered By: Shannon Hughes     Answered On: Feb 24

I'm not sure why you're using ActiveCell as your starting position, but I'll
stay with it.

You don't say what the formula  is. In particular, you don't say whether it
is to be relative to where it is located. I'll assume it is to be relative.

An easy way to put a relative formula in is with FormulaR1C1. This
statement puts a formula in the cell to the right of the active cell and
every cell below it until the next blank cell.

Range(ActiveCell.Offset(0, 1), ActiveCell.End(xlDown).Offset(0,
1)).FormulaR1C1 = "=RC[-1]*10"

This formula is multiplying the cell to the left of it by 10.

Answer #2    Answered By: Clinton Edwards     Answered On: Feb 24

Thanks for that. Actually my starting cell would be G2 and I am
using a function not a formula  (sorry, got the terminology wrong!)

Function is =SUMIF(C:C,F2,D:D) and is to be copied down as far as
there is contiguous data  in column  F.

Answer #3    Answered By: Adelaide Fischer     Answered On: Feb 24

Doesn't matter. If you're going to be doing this in different places then

With Range("G2")
.Formula = "=SUMIF(C:C,F2,D:D)"
.AutoFill Destination:=Range(.Offset(0, -1), .Offset(0,
-1).End(xlDown)).Offset(0, 1)
End With

will do it and all you'll have to change is the first line and the

Answer #4    Answered By: Blake Smith     Answered On: Feb 24

Range("B1").AutoFill Destination:=Range("B1:B21")

would be the vba  way for known source and destination ranges, you need
to change the Destination range  bit automatically.

Sticking with using activecell, make sure B1 is the Active cell then

ActiveCell.AutoFill Destination:=Range(ActiveCell.Offset(0, -1),
ActiveCell.Offset(0, -1).End(xlDown)).Offset(0, 1)

(it's one line) will fill  down from the active cell row down as far as
there is a contiguous column  to the left.

The following will do the same but use the column to the right of the
active cell to determine how far down to fill:

ActiveCell.AutoFill Destination:=Range(ActiveCell.Offset(0, 1),
ActiveCell.Offset(0, 1).End(xlDown)).Offset(0, -1)

Moving away from using ActiveCell, its appearance in the above
formulae can be replaced with, say, Range("B1").

Answer #5    Answered By: Ryder Anderson     Answered On: Feb 24

missed the first bit: "to use vba  to insert a formula  in column  B"

If you record a macro while putting a formula into B1 you get
something like this:

ActiveCell.FormulaR1C1 = "=RC[-1]/2"

(I typed in a formula to halve the cell to the left)
Two things can be done to simplify, first the "=RC[-1]/2" looks a bit
foreign because it's using the R1C1 notation, so that line can be
changed to the more familiar A1 notation thus:

ActiveCell.Formula = "=A1/2"

Second, all this selecting can be removed thus:

Range("B1").Formula = "=A1/2"

so that single line is all that is needed to put a formula into cell B1

Answer #6    Answered By: Angelica Ramos     Answered On: Feb 24

If your starting cell is G2, then you can replace the first
ActiveCell.Offset with "G2" - it was only moving you one to the right of
your search-down cell anyway,

The second ActiveCell would be changed to a Range("F2") because it needs to
move down before offsetting to the right for the "G" column.

Re terminology ... you had it right in the first place. My question was
probably a bit cryptic. The formula  is the text that you put in the cell.
Usually, the term is only used when the text starts with an "=" sign
(although technically anything entered in a cell is its formula). So it IS
a formula you want to copy.

My main interest in your formula was to find out if it had relative cell
references in it, that would change as you copied the cell - i.e. your F2.

And you are using a function inside the formula (i.e. SumIf).

As luck would have it (or is it Murphy's law?) you have hit on a column  that
doesn't pass nicely through R1C1 notation without change - i.e. "C". So if
you just make the FormulaR1C1 into


then the D:D will be passed through fine. The RC[-1] will offset correctly
one column to the left. But the C:C will be treated as a reference to the
column the formula is in (i.e. G).

Because of this, you need to state your C column reference in R1C1 notation.
For completeness, it would be nice to do the D column the same way. So the
end result is a single VBA statement again:

Range("G2", Range("F2").End(xlDown).Offset(0, 1)).FormulaR1C1 =

The formula that ends up in G5 is


and similarly for the other G cells that have corresponding F cells.

Answer #7    Answered By: Lonnie Rogers     Answered On: Feb 24

One other quick question...

I am sorting out data  imported from a mainframe and have done most
of it successfully (of a fashion!). However, I want to move cells
that contain numbers and have done the following:

For varCounter = 1 To varRowCount
If Selection.Value = "2" Then
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
End If
Selection.Offset(1, 0).Select

It works perfectly, but I need it to move ALL numbers, not just 2:)
How do I tell Excel, IF(ISNUMERIC... (like the worksheet formula),
so that if the cell contains a number  it will be shifted to the
right as above?

Answer #8    Answered By: Hubba Akhtar     Answered On: Feb 24

IF(ISNUMERIC... (like the worksheet formula)"
Curious, because IsNumeric is the vba  version while ISNUMBER is the
worksheet function.


If Selection.Value = "2" Then
might become:
If IsNumeric(Selection.Value) Then

Didn't find what you were looking for? Find more on Copy down formula with vba Or get search suggestion and latest updates.