Formula to code

[fairwinds]

Hi,
Could anyone help me with the code that does the same as this formula =SUMIF($A3:INDIRECT(ADDRESS(ROW()+$CO2;1;4));$A2+1;CX3:INDIRECT(ADDRESS(ROW()+$CO2;COLUMN();4)))*$D2+J2*$D2

I have a macro that puts this in several cells and then copy and paste special values.

Cells(a, 102).FormulaR1C1 = _
"=SUMIF(R[1]C1:INDIRECT(ADDRESS(ROW()+RC93,1,4)),RC1+1,R[1]C:INDIRECT(ADDRESS(ROW()+RC93,COLUMN(),4)))*RC4+RC[-92]*RC4"

However I cannot figure out the code that just does the job.

Thanks in advance

 

[fairwinds]

I try to clarify and see if I get some response.
This is a part of a multilevel price calculation.
Level 1 is highest level.
Product level 1 consists of several level 2’s.
Each level two of several level 3’s and so on.
Up to ten levels and unlimited number of products.
The formula is on every row and reaches down until it finds the same level or higher. (That is given by the $CO2 reference). And then summarises only the levels that is one level lower within that area. I’ve tried to solve this with for next loops that checks every row but it takes forever as it is several thousand rows. I would like to apply the mentioned formula to each cell going from bottom up without actually pasting it into the cell.
Any help is highly appreciated!

 

[Andrew Poulsom]

Well you could just do this:

Cells(a, 102).FormulaR1C1 = _ 
"=SUMIF(R[1]C1:INDIRECT(ADDRESS(ROW()+RC93,1,4)),RC1+1,R[1]C:INDIRECT(ADDRESS(ROW()+RC93,COLUMN(),4)))*RC4+RC[-92]*RC4"
Cells(a, 102).Value = Cells(a, 102).Value

 

[fairwinds]

Thanks! it seems to be speeding it up a little. Which one do yu think is fastest. Either turn autmatic calc of, paste code in several cells, calc on and then do our trick or to do it cell by cell? (this formula and others go like 30 times per row)

 

[Andrew Poulsom]

You can use the FormulaRIC1 property with a Range to populate all the cells at once. Here is a simple example:

Sub Test()
    Dim a As Long
    a = 1
    With Range(Cells(a, 2), Cells(a + 4, 2))
        .FormulaR1C1 = "=RC[-1]"
        .Value = .Value
    End With
End Sub

 

[fairwinds]

Yes! That was it. Your solution uses like 10% of the time as mine. (Strange though as it still calculates every cell one by one)

Many thanks!

 

[Aladin Akyurek]

A formula with INDIRECT/ADDRESS (both are volatile), copied to hundreths of cells is bound to take ages to compute.

 

[fairwinds]

Yes but as it replaces several levels of loops it is actually not that big difference. What Andrew did for me was to ensure that they did not recalk more than once. That did it for me.