Forum Discussion

hrh_dash's avatar
hrh_dash
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's avatar
    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
HansVogelaar's avatar
HansVogelaar
MVP
Jun 09, 2022

hrh_dash 

 

Why don't you just place the formula in the cell?

hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 09, 2022
The file will be used across the whole team and do not wish for the formula to be meddle with 😞 it will also be sent to external service provider vendors to help enhance the whole credit assessment process..
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    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's avatar
      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's avatar
        HansVogelaar
        MVP
        Jun 09, 2022

        hrh_dash 

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

Resources