Jun 08 2022 06:52 AM - edited Jun 08 2022 07:06 AM
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
Jun 08 2022 06:25 PM
@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
Jun 09 2022 06:03 AM
Jun 09 2022 06:23 AM
Jun 09 2022 06:33 AM
SolutionSee 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
Jun 09 2022 06:49 AM
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")
Jun 09 2022 07:10 AM
If you open the workbooks in the code, the formula should work without specifying the path.
Jun 09 2022 08:54 AM
Jun 09 2022 11:21 AM
It shouldn't really matter, and trying to refine the formula only complicates things.
Jun 09 2022 06:00 PM
Jun 09 2022 06:33 AM
SolutionSee 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