Forum Discussion

Iron Contributor

Jun 08, 2022

Solved

Got stuck at creating a code for Ifna, sumif and sumproduct

Hi All, would like to create a code for Ifna, sumif and sumproduct. Tried to create a code replicate the excel formula below: IFNA(IF(LEN(B1)>1, SUMIF(Sheet1!E:I,B2,Sheet1!I:I), SUMPROD...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Jun 09, 2022

hrh_dash

See if this does what you want:

    Dim ws As Worksheet
    Set ws = Sheet1
    With ws.Range("C44")
        .Formula = "=IFNA(IF(LEN(C10)>1,SUMIF(Sheet1!E:I,C15,Sheet1!I:I)," & _
            "SUMPRODUCT(MAX(('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B=B2)*" & _
            "('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S=" & _
            "SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B2)*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S)))*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$M2:$M)))," & _
            """Credit limit not provided to this customer"")"
        .Value = .Value
    End With

hrh_dash

Iron Contributor

Jun 09, 2022

HansVogelaar ,

is possible to substitute Sheet1 with the exportWs? Because it is a file path

Set exportWb = Workbooks.Open(Environ("Userprofile") & "\Desktop\export.xlsx")
Set exportWs = exportWb.Sheets("Sheet1")

and

'[Credit Limit Requests (no existing SAP IDs).xlsx] as clRequestWs? Because it is a file path.

Set clRequestWb = Workbooks.Open("V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\1. Credit Limit Requests\Credit Limit Requests (no existing SAP IDs).xlsx")
Set clRequestWb = ActiveWorkbook
Set clRequestWs = clRequestWb.Sheets("No SAP ID")

HansVogelaar

MVP

Jun 09, 2022

hrh_dash

If you open the workbooks in the code, the formula should work without specifying the path.

hrh_dash
Iron Contributor
Jun 09, 2022
the code works!

downside is that the

1.desktop file path cannot be replace by (Environ("Userprofile"), thus needs to be hard coded by the respective file path

2. the ranges for sumproduct for workbook Credit Limit Requests (no existing SAP IDs) cannot be replace by clRequestWs.Cells(clRequestWs.Rows.Count, "B") , clRequestWs.Cells(clRequestWs.Rows.Count, "S") and clRequestWs.Cells(clRequestWs.Rows.Count, "M"), hence it needs to be hard coded.

is there any other work around way?
- HansVogelaar
  MVP
  Jun 09, 2022
  hrh_dash
  
  It shouldn't really matter, and trying to refine the formula only complicates things.
  - hrh_dash
    Iron Contributor
    Jun 10, 2022
    Alright noted, thanks for the help again. Much appreciated