Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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.

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

formula.

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").

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:

Range("B1").Select

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

Range("B2").Select

(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

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

"=SUMIF(C:C,RC[-1],D:D)"

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 =

"=SUMIF(C3:C3,RC[-1],C4:C4)"

The formula that ends up in G5 is

=SUMIF($C:$C,F5,$D:$D)

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

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:

[C1].Select

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

Next

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?

IF(ISNUMERIC... (like the worksheet formula)"

Curious, because IsNumeric is the vba version while ISNUMBER is the

worksheet function.

So

If Selection.Value = "2" Then

might become:

If IsNumeric(Selection.Value) Then

(untested)

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

Related Topics:

- VBA Code for pasting a set of formulas down a data range
- Getting a formula to copy from inside VBA
- copy down formulas in excel
- VBA variable range in a formula
- VBA Formula prob
- Formula using range name in VBA code
- VBA code to add values in two cells which have formulas
- VBA or array formula?
- formula help but not vba please
- cells containing formula that refer to user-defined VBA function
- Stuck on copying formula from 1 sheet to another
- Copy Value produced by formula in cell C3 to cell D3
- Stuck on copying formula from 1 sheet
- VBA Excel Drop down lists
- Copy and Paste using VBA
- Excel VBA - need to copy paste conditional statement
- Newbie VBA Macro question- Conditional Copy & Paste based on time
- New to VBA and need help with copy and paste
- VBA conditional formatting - copy & paste
- VBA conditional formatting - copy & paste
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- writing vba code to generate vba code
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA