Adding a range in VBA

**Alfarata** · 08-13-2008, 01:39 PM

I need to Add/Sum a Range(A Column) in Code I know the Top and the Bottom, but do not know how to work with R1C1 Data Here is the code I have all works except the ActiveCell.FormulaR1C1 Line.
When I try to use my reference points it fails.
The TOP and BOTTOM will change based on the data inputed.

How can I use my Top and Bottom Reference Points?

Range(Top & ":" & Bottom).Select
    Range("H" & Bottom + 4).Activate
    ActiveSheet.Cells(Bottom + 3, 8).Formula = "=Sum(bottom & : & top)"

    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("H" & Bottom + 4).Select
    Selection.AutoFill Destination:=Range("H" & Bottom + 4 & ":" & "K" & Bottom + 4), Type:=xlFillDefault
    Range("H" & Bottom + 4 & ":" & "K" & Bottom + 4).Select

**VBA Noob** · 08-13-2008, 02:02 PM

No need to select.

See if this points you in the right direction

Dim LastRow As Long
Dim StartRow As Long

StartRow = 3
LastRow = 10

With ActiveCell
    .Formula = "=SUM(A" & StartRow & ":A" & LastRow & ")"
    .AutoFill Destination:=Range(ActiveCell.Address, "A" & LastRow + 6), Type:=xlFillDefault
End With

VBA Noob

**Alfarata** · 08-13-2008, 02:24 PM

I could not get it to work
Even if I used it like this:

    Range("H6:h47").Select
    With ActiveCell
    .Formula = "=SUM(H6:H47)"
    '.AutoFill Destination:=Range(ActiveCell.Address, "H" & Bottom + 6), Type:=xlFillDefault
    End With

I did not get any results on the Sheet

**VBA Noob** · 08-13-2008, 02:28 PM

That the trouble with only giving a bit of the code. You get a bit of an answer.

I notice a

'

in the autofill line which will stop that line from running

'.AutoFill

VBA Noob

**Alfarata** · 08-13-2008, 02:34 PM

I did have a ' on that line once removed it wrote Zero's in H6 the TOP all the way down thru H53 Bottom + 6.

No Sum

Bill

**VBA Noob** · 08-13-2008, 03:05 PM

Bill,

You are selecting (which you should also avoid) H6 to H47 and then asking it to insert the formula

"=SUM(H6:H47)"

this will return a circular ref.

If you want help I suggest you post a sample with explaination of what your after

Range("H6:h47").Select
    With ActiveCell
    .Formula = "=SUM(H6:H47)"

VBA Noob

Adding a range in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding a range in VBA

Missing something

Still Somewrong

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions