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

[hrh_dash]

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),

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")

Got stuck whilst trying to create the code:

1st hurdle: To insert the IF into IFNA

ws.Range("C44") = WorksheetFunction.IfNa( _
If Len(ws.Range("C10")) > 1 Then
WorksheetFunction.SumIf(exA, ws.Range("C15"), exB)
Else

2nd hurdle: No clue how to insert a MsgBox if value equals #N/A

MsgBox "Credit limit not provided to this customer"

Dim ws As Worksheet 'current sheet
Dim exportWb As Workbook
Dim clRequestWb As Workbook
Dim exportWs As Worksheet
Dim clRequestWs As Worksheet
Dim exportWslastRow As Long

Dim exA As Range
Dim exB As Range
Dim cLB As Range
Dim cLS As Range
Dim cLM As Range


Set ws = Sheet1
Set exportWb = Workbooks.Open(Environ("Userprofile") & "\Desktop\export.xlsx")
Set exportWs = exportWb.Sheets("Sheet1")
exportWslastRow = exportWs.Cells(exportWs.Rows.Count, "B").End(xlUp).Row

Set exA = exportWs.Range("E2:I" & exportWslastRow)
Set exB = exportWs.Range("I2:I" & exportWslastRow)

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")
ThisWorkbook.Activate

Set cLB = clRequestWs.Cells(clRequestWs.Rows.Count, "B").End(xlUp).Row
Set cLS = clRequestWs.Cells(clRequestWs.Rows.Count, "S").End(xlUp).Row
Set cLM = clRequestWs.Cells(clRequestWs.Rows.Count, "M").End(xlUp).Row

ws.Range("C44") = WorksheetFunction.IfNa( _

If Len(ws.Range("C10")) > 1 Then

WorksheetFunction.SumIf(exA, ws.Range("C15"), exB)

Else

Application.WorksheetFunction.SumProduct (WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) *
--(cLS = Application.WorksheetFunction.SumProduct(WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) * _
cLS))) * cLM))

End If

Thanks in advance and appreciate the help

[#2]

@Hans Vogelaar , i tried the following but there is some issues with structuring of the code. Would you be able to assist on this?

Dim i as Variant
i = ws.Range("C44")

i = WorksheetFunction.IfNa(
If Len(ws.Range("C10")) > 1 Then
WorksheetFunction.SumIf(exA, ws.Range("C15"), exB):=          
Else
Application.WorksheetFunction.SumProduct (WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) *
--(cLS = Application.WorksheetFunction.SumProduct(WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) * _
cLS))) * cLM))

End If

[#3]

@hrh_dash 

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

[#4]

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..

[#5 · Accepted solution]

@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

[#6]

@Hans Vogelaar ,

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")

[#7]

@hrh_dash 

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

[#8]

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?

[#9]

@hrh_dash 

It shouldn't really matter, and trying to refine the formula only complicates things.

[#10]

Alright noted, thanks for the help again. Much appreciated