Excel. Adding a formula to a cell with a SUMIF and a variable row-CodePudding

`Evening all. Just a quick one i'm sure to save me going back and forth forever with "s and &s

So i have some VBA code that is adding a Formula into a cell. The formula is using a sumif function to compare a time to a column of times and adding up the totals that are in a column a couple of doors down (but only the cells that are in the same column as the original time. I am currently toying with the following formula (and have been for quite a while) but i think my coding is just a little sub par and I'm struggling to get it to work in my current project, but I think I'm close :)

WS.[C14].Offset(i).Formula = "=SUMIF(times," & ActiveCell.Row & "*,proc)"

times and proc are named ranges where the times to check and the cells which will have the total to sum are, and the criteria (the actual time in the middle of the SUMIF) is being taken from the active row of the selected cell... which is being selected in some coding for a ForEach block as you can see below

For Each cel In Rng.Cells
WS.[A14].Offset(i).Select
WS.[B14].Offset(i) = WC.[H1].Offset(j)
WS.[C14].Offset(i).Formula = "=SUMIF(times," & ActiveCell.Row & "*,proc)"
i = i   1
j = j   1

Next cel On Error Resume Next

The whole part of the VBA works fine, it's just that one formula in the C14 loop that just refuses to play right. Does anyone have any ideas on a suitable easy fix? Added below an image of the table below... with the result that I am after.

Just a point to note, i can't used set ranges as both tables are pretty dynamic in how many values they can both hold!

Any ideas would be greatly appreciated, or if more info is needed, then I'l be happy to help :) many thanks

Faffing for ages :/ hehe

CodePudding user response：

A guess as to what you want:

WS.Range("C14").Resize(Rng.Rows.Count).Formula = "=SUMIF(times,B14,proc)"

There is no need to loop. When you write a formula with relative (or mixed) references to a range, Excel will automatically update the references down rows and/or across columns.