• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to pass Dynamic Range Variable in Formula as argument?

C

Chirag R Raval

Member
Dear All Experts,

I struggle to achieve result on just simple sum product formula as below.

Code: 
Option Explicit
Option Compare Text
Sub Rng_in_var_Pass_In_formu()
'Formulas Start From Here
'FIRST DEFINE DYNEMIC RANGE FOR COLUMNS TO REFER IN FIRMULAS

Dim lookmycol As Range
Dim qtycol As Range
Dim valcol As Range

Set lookmycol = ActiveSheet.Range("B1", ActiveSheet.Range("B1").End(xlDown))
Set qtycol = ActiveSheet.Range("E1", ActiveSheet.Range("E1").End(xlDown))
Set valcol = ActiveSheet.Range("F1", ActiveSheet.Range("F1").End(xlDown))

'JUST EXAMPLE-my2ndrng.Cells(1, 2).Formula = "=SUM(SUMIFS(sum_range,criteria_range,{" * red * "," * blue * "}))"

'BELOW IS WORKING FIRMULA ON SHEET LEVEL BUT HOW TO ACHIVE RESULT BY RANGE VARIABLE PASS IN FORMULA

activesheet.range("E154").formula =SUMPRODUCT(SUMIFS(E2:E152,B2:B152,{"*SECOND*WHS*TOTAL*","*CUTS*TOTAL*"}))

'BELOW IS NOT WORKING FORMULA, HOW TO PASS DAYNEMIC RANGE, STORE IN VARIABLE AS ARGUMENT OF FORMULA

activesheet.range("E154").formula = "=SUMPRODUCT(SUMIFS("+& qtycol +","+ & lookmycol & + ",{"*SECOND*WHS*TOTAL*","*CUTS*TOTAL*"}))"


End Sub

how to pass a variable, that construct as dynamic range variable , in to the formula as argument.

I also attached my excel file also contain same not working code in it.

Hope there are solution found & also Help will be appreciated.

Regards,
Chirag Raval
 

Attachments

  • SUBTOTAL -RANGE SELECTION TEST.xlsm
    28.2 KB · Views: 6
Try this:
Code: 
Sub Rng_in_var_Pass_In_formu()
'Formulas Start From Here

Dim lookmycol As range
Dim qtycol As range
Dim valcol As range

Set qtycol = ActiveSheet.range("E1", ActiveSheet.range("E1").End(xlDown))
Set lookmycol = qtycol.Offset(0, -3)
Set valcol = qtycol.Offset(0, 1)

'JUST EXAMPLE-my2ndrng.Cells(1, 2).Formula = "=SUM(SUMIFS(sum_range,criteria_range,{" * red * "," * blue * "}))"

ActiveSheet.range("E154").formula = "=SUMPRODUCT(SUMIFS(" & qtycol.Address & "," & lookmycol.Address & ",{" & Chr(34) & "* Second * WHS * Total * CUTS * Total *" & Chr(34) & "}))"
ActiveSheet.range("E155").formula = "=SUMPRODUCT(SUMIFS(" & qtycol.Address + "," & lookmycol.Address & ",{" & Chr(34) & "* Second * WHS * Total *" & Chr(34) & "," & Chr(34) & "* CUTS * Total *" & Chr(34) & "}))"

End Sub
 
Check it.

Code: 
Option Explicit
Option Compare Text
Sub Rng_in_var_Pass_In_formu()
Dim oRresultCell As Range
Const iResult1Cell = 154

With ActiveSheet.Range("B1:F" & iResult1Cell - 3)
    Set oRresultCell = .Parent.Range("E" & iResult1Cell)

        oRresultCell.Formula = _
        "=SUMPRODUCT(SUMIFS(" & .Columns(4).Address & "," & .Columns(1).Address & ",{" & """*FRESH*WHS*Total*""" & "}))"
       
        oRresultCell.Offset(1).Formula = _
        "=SUMPRODUCT(SUMIFS(" & .Columns(4).Address & "," & .Columns(1).Address & ",{" & """*SECOND*WHS*Total*""" & "," & """*CUTS*Total*""" & "}))"
       
        oRresultCell.Offset(2).Formula = _
        "=SUMPRODUCT(SUMIFS(" & .Columns(4).Address & "," & .Columns(1).Address & ",{" & """*TRS*TOTAL*""" & "}))"
       
        oRresultCell.Offset(3).Formula = _
        "=SUMPRODUCT(SUMIFS(" & .Columns(4).Address & "," & .Columns(1).Address & ",{" & """*MBO*TOTAL*""" & "}))"

End With

Set oRresultCell = Nothing

End Sub
 
Dear Sirs @Hui & @Deepak ,

Amazing, Both's Code Work like a charm...Thank you very much.

Oh, I realize now that , Formula just want string as range address & I pass it range object.

Mr Deepak's code looking require some more precise construct, as per it should have require last cell's address (Const iResult1Cell = 154) , but actually this is dynamic range. (Each month's report, have rows count can varied)

If , last cell address , should be auto find (Must be top to bottom approach, due to after main big range , just after 2 lines below another range where this formula require) then there are no need to go to this code each time
to modify for provide last cell address.

hope you can have some solution for Mr Deepak's Code.

Again thanks for your valuable help.

Regards,

Chirag Raval
 
Check it.

Code: 
Option Explicit
Option Compare Text
Sub Rng_in_var_Pass_In_formu()
Dim oRresultCell As Range, r1 As String, r2 As String

With ActiveSheet.Range("A1").CurrentRegion
r1 = .Columns(5).Address
r2 = .Columns(2).Address

Set oRresultCell = .Parent.Range("E" & .Rows.Count + 3)
        oRresultCell.Formula = _
        "=SUMPRODUCT(SUMIFS(" & r1 & "," & r2 & ",{" & """*FRESH*WHS*Total*""" & "}))"
     
        oRresultCell.Offset(1).Formula = _
        "=SUMPRODUCT(SUMIFS(" & r1 & "," & r2 & ",{" & """*SECOND*WHS*Total*""" & "," & """*CUTS*Total*""" & "}))"
     
        oRresultCell.Offset(2).Formula = _
        "=SUMPRODUCT(SUMIFS(" & r1 & "," & r2 & ",{" & """*TRS*TOTAL*""" & "}))"
     
        oRresultCell.Offset(3).Formula = _
        "=SUMPRODUCT(SUMIFS(" & r1 & "," & r2 & ",{" & """*MBO*TOTAL*""" & "}))"
End With
Set oRresultCell = Nothing
End Sub
 
My code does that in

Set qtycol = ActiveSheet.range("E1", ActiveSheet.range("E1").End(xlDown))

with the other two ranges based on that as offsets
Set lookmycol = qtycol.Offset(0, -3)
Set valcol = qtycol.Offset(0, 1)
 
Or i will go with it to avoid object.

Code: 
Option Explicit
Option Compare Text
Sub Rng_in_var_Pass_In_formu()
Dim strF As String

With ActiveSheet.Range("A1").CurrentRegion
    strF = "=SUMPRODUCT(SUMIFS(" & .Columns(5).Address & "," & .Columns(2).Address & ",{"
       
    With .Parent.Range("E" & .Rows.Count + 3)
   
        .Offset(0).Formula = strF & """*FRESH*WHS*Total*""" & "}))"
       
        .Offset(1).Formula = strF & """*SECOND*WHS*Total*""" & "," & """*CUTS*Total*""" & "}))"
       
        .Offset(2).Formula = strF & """*TRS*TOTAL*""" & "}))"
       
        .Offset(3).Formula = strF & """*MBO*TOTAL*""" & "}))"
       
    End With
   
End With

End Sub
 
Dear Sir @Deepak,

Just Great,

Ok, so Take base on Current Region, So "& .Columns(2).Address" naturally assign last cell as "B151" to maintain consistency as same size of ranges,
& that's.

Also great learn from you about how to fix repeated necessary construct for each command line in some small variable like below & just reference it as & when require, so we can make code more neat & maintainable.

"strF = "=SUMPRODUCT(SUMIFS(" & .Columns(5).address & "," & .Columns(2).address & ",{""

Sir @Deepak, for your Post No 5,
can you give some more information in below command about roll of .Parent ?

"Set oRresultCell = .Parent.Range("E" & .Rows.Count + 3)"


Also Respected Sir @Hui's .Offset Method also very helpful for over come
from construct 3 difference same size ranges as this ranges make just dependable on 1 fully qualified range..

Thanks Again.

Regards,
Chirag Raval
 
You must log in or register to reply here.
Back
Top