How to copy array formula from named range in same range's next columns

[Chirag R Raval]

Dear All Experts,

I can not copy sumproduct array formula from one columns to many columns.
below is screen shot for the same with requirement




my code of array formula of first column is below

Dim lookmycol As Range
Dim qtycol As Range
Dim threeblk As Range
Dim raja As Variant

Set qtycol = ActiveSheet.Range("E2", ActiveSheet.Range("E2").End(xlDown))
Set lookmycol = qtycol.Offset(0, -3)
Set threeblk = rng2.Range("B1:B3")

raja = Application.WorksheetFunction.Sum(threeblk)
'Set valcol = qtycol.Offset(0, 1)

'JUST EXAMPLE-my2ndrng.Cells(1, 2).Formula = "=SUM(SUMIFS(sum_range,criteria_range,{" * red * "," * blue * "}))"
'EXAMPLE-2  qtycol.End(xlDown).Offset(7, 0).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address + "," & lookmycol.address & ",{" & Chr(34) & "*TRS*Total*" & Chr(34) & "," & Chr(34) & "*CUTS*Total*" & Chr(34) & "}))"

'SUM & SUMIFS  & SUMPRODUCTS ON END OF COLUMN OF RANGE 2 QTY COLUMN

rng2.Cells(1, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address & "," & lookmycol.address & ",{" & Chr(34) & "*TRS*Total*" & Chr(34) & "}))"
rng2.Cells(2, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address + "," & lookmycol.address & ",{" & Chr(34) & "*MBO*Total*" & Chr(34) & "}))"
rng2.Cells(3, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address + "," & lookmycol.address & ",{" & Chr(34) & "*whs*Total*" & Chr(34) & "}))"
rng2.Cells(4, 2).Value = Application.WorksheetFunction.Sum(rng2.Range("B1:B3"))
rng2.Columns(2).Copy
rng2.Range("C:M").Paste

Column "B of above range is absolute reference for all cell's formula because all values of all columns based on column b's critera

but how can make dynamic that column b is absolute but next all columns
formula take as relative at right side when copy same in right


hope your help please..

Regards,

Chirag Raval
Hope there are some way

 

[Chirag R Raval]

Dear Sir,

I can copy that formula in row 1 but can not make column reference of "QtyCol"
from absolute to relative for before copy of main or after copy that in row 1 of rng1.

further how to fill down same formula /

hope your helps.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear All Experts,

I successfully copy rng2's 1st column's formula to next 11 columns
but without relative reference as per below

rng2.Cells(1, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address & "," & lookmycol.address & ",{" & Chr(34) & "*TRS*Total*" & Chr(34) & "}))"
rng2.Cells(2, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address + "," & lookmycol.address & ",{" & Chr(34) & "*MBO*Total*" & Chr(34) & "}))"
rng2.Cells(3, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address + "," & lookmycol.address & ",{" & Chr(34) & "*whs*Total*" & Chr(34) & "}))"
rng2.Cells(4, 2).Value = Application.WorksheetFunction.Sum(rng2.Range("B1:B3"))

rng2.Range("C1:M1").FormulaArray = rng2.Range("B1").FormulaArray
rng2.Range("C2:M2").FormulaArray = rng2.Range("B2").FormulaArray
rng2.Range("C3:M3").FormulaArray = rng2.Range("B3").FormulaArray
rng2.Range("C4:M4").FormulaArray = rng2.Range("B4").FormulaArray

REQUIREMENT IS BELOW.




Hope there are some solution found here , help will be appreciated well.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear Sir,

In short How to modify below formula as it's First Reference of formula (Only lookup column Column Reference) Relative?

rng2.Cells(1, 2).Formula = "=SUMPRODUCT(SUMIFS(" & qtycol.address & "," & lookmycol.address & ",{" & Chr(34) & "*TRS*Total*" & Chr(34) & "}))"

ORIGINAL FORMULA IN WHICH COLUMN REFERENCE ON (WHICH NEED TO SUM ) -IS ABSOLUTE

result of Above Formula In Sheet's Cell Is Looking As Below



REQUIRED AS BELOW

REQUIRED FORMULA , IN WHICH REQUIRE/NEED TO HAVE RELATIVE REFERENCE OF COLUMN REFERENCE



So I can copy it in next rows-columns via vba like below.
if, it is an array formula then my below method of copy paste is correct or not? please guide.

rng2.Range("C1:M1").FormulaArray = rng2.Range("B1").FormulaArray
rng2.Range("C2:M2").FormulaArray = rng2.Range("B2").FormulaArray
rng2.Range("C3:M3").FormulaArray = rng2.Range("B3").FormulaArray
rng2.Range("C4:M4").FormulaArray = rng2.Range("B4").FormulaArray

Hope I now try better to describe my requirement.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear Sir,

Yes I found from the we, I convert my original formula as column relative , row absolute. as per below...



but the problem now when copy it to next row columns it not updated.
it till hold column "E".
I must fill by drag the first column over next columns & then its updated..

please help that I can successfully copy first column on next columns with all modification.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear Sirs,

Now I face problem on copy of last row that should have simple sum.
as below



please guide that how to just sum of rn2.cells(1,2),cells(3,2) on cell?

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear All Users ,

Finally it solved with help of following link that deeply learn us just how to sum
formula entered in any cell of any range.

https://stackoverflow.com/questions/11707888/sum-function-in-vba

Below screen shot for modify VBA structure that help to put formula in cell



The result in sheet screen shot



May Be my question , that , i can not described well about my little , simple requirement of just to say how to put formula in cell through VBA with relative reference.that's it

And , also, my uncertain & ramble steps towards, questions style, hence not any single reply, from this site , but though, thank you very much for all users & experts of this site which one of the forever blessing of amazing learning environment, that force us to just towards, solution of our problems but first based on our own effort.

Thank You Very Much,

Regards,

Chirag Raval