SOLVED

Contributor

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

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

9 Replies

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

@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``````

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

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

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

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..
best response confirmed by hrh_dash (Contributor)
Solution

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

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

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

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

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

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

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

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?

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

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

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

Alright noted, thanks for the help again. Much appreciated