SUMIFS #REF error once macro executed

[davidhall]

I have a sumifs formula in sheet 2 where the sum range and criteria ranges are in sheet1. The criteria are on sheet 2. When I initially input the formula on sheet 2, it works fine, however, when I run a certain macro, the formula messes up.

The purpose of the macro is to format things correctly on sheet1 when I import new data. So, I import new data on sheet1, execute the macro, the data is formatted correctly so the referenced material in the sumifs formula can refer to the correct data.

Not sure why its giving me an error.

It looks like this originally = sumifs(Sheet1!G:G,Sheet1!,Indirect("Sheet2!A1"),Sheet1!A:A,Indirect("Sheet2!A2"))

The columns highlighted in red are showing the #REF once the macro is run. The reason why I put the entire column as the reference area is because the data I import varies on how far down it is.

Any suggestions?

 

[davidhall]

I've already tried the index formula and the same result occurs. Sheet1 is a price list that I update weekly. The original pricelist is a 10.5mb file. After the macro runs, it deletes the unnecessary information thus reducing the file to around 100kb. The reduced information is the only thing I need in this file. On sheet2, the cells that have the sumifs formula in them reference the adjusted pricelist.

 

[wigi]

That's more clear

To reiterate, set the SUMIFS formulas again using code, after the clean up macro has run.

 

[davidhall]

how do I insert the formula into 6 continuous rows, then skip 10 rows, insert the formula in the next 6 rows, skip 10..etc.

 

[wigi]

Run this code on an empty TEST sheet:

Sub settingformulas()

    Dim i As Long
    
    For i = 1 To 1000
    
        Range("A" & i).Resize(6).Formula = "=RC[1]+RC[2]"
        i = i + 15
    
    Next

End Sub

It will explain the basis and provide enough food for thought.

 

[davidhall]

You are awesome!