copying down formula not working

jam1531

New Member
Joined
Jan 5, 2015
Messages
29
Hello,

Any help appreciated. The below code works fine EXCEPT when it goes down multiple rows it seems to just copy the first cell down multiple times. My guess is that although [FONT=&quot]Range("x11") is anchoring or in other words not moving to the new cells as it goes down, causing the problem.[/FONT]


Code:
[COLOR=#1A1A1A][FONT=&quot]Dim LastRow As Long[/FONT][/COLOR][COLOR=#1A1A1A][FONT=&quot]

With ActiveWorkbook.Sheets("Report")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

End With

ActiveSheet.Range("V11").Formula = Range("x11") / Application.SumIf(Range("i11:i" & LastRow), (Range("i11")), Range("x11:x" & LastRow))


[/FONT][/COLOR]

[COLOR=#1A1A1A][FONT=&quot]Range("V11").Copy[/FONT][/COLOR]
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you want to put the formula in column V:

Code:
Sub test1()
 With Range("V11:V" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RC[2]/SUMIF(RC[-13]:R[2]C[-13],RC[-13],RC[2]:R[2]C[2])"
 End With
End Sub

Or this

Code:
Sub test1()
 With Range("V11:V" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RC[2]/SUMIF(R11C9:R13C9,R11C9,R11C24:R13C24)"
 End With
End Sub

To complete the formula I need you to explain what you want to put
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top