Having trouble with IFERROR and MIN

[Moonbeam111]

How can I convert this to VBA Code?

=IFERROR( MIN(1,IF('Sheet1'!F16="Missed Budget",0,IF(AND('Sheet2'!C23="",'Sheet2'!C22="Unknown"),"Percentage of Budget",IF(AND('Sheet1'!F16="Hit Budget",'Sheet2'!C20=1,E26="Success"),RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01/2,IF(AND('Sheet1'!F16="Hit Budget",'Sheet2'!C20=1,H26="Success"),RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01/2,IF('Sheet1'!F22="Budget Surpassed",RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*2*0.01,IF('Sheet1'!F16="Budget Exceeded",RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01))))))),"Not Applicable")

Here is what I have:

If Sheets("Sheet1").Range("F16") = "Missed Budget" then
Sheets("My worksheet").Range("A33") = 0
end if

If Sheets("Sheet2").Range("C23") = "" and Sheets("Sheet2").Range("C22") = "Unknown" then
Sheets("My worksheet").Range("A33") = "Percentage of Budget"
end if

If Sheets("Sheet1").Range("F16") = "Hit Budget" and Sheets("Sheet2").Range("C20") = "1" and Sheets("My worksheet").Range("E26") = "Success" then
Sheets("My worksheet").Range("A33") = WorksheetFunction.RandBetween(Sheets("Sheet2").Range("D40"), Sheets("Sheet2").Range("D41")) * 0.01 / 2
end if

Etc..

Is there a more streamlined code for this? I'm mostly having trouble with applying the IFERROR part across the VBA Code and the MIN function.

 

[DanteAmor]

Hi @Moonbeam111

One option is to put the formula and then put the value in the cell:

Sub formula_vba()
  Randomize
  With Sheets("My worksheet").Range("A33")
    .Formula = "=IFERROR(MIN(1,IF('Sheet1'!F16=""Missed Budget"",0," & _
      "IF(AND('Sheet2'!C23="""",'Sheet2'!C22=""Unknown""),""Percentage of Budget""," & _
      "IF(AND('Sheet1'!F16=""Hit Budget"",'Sheet2'!C20=1,E26=""Success""),RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01/2," & _
      "IF(AND('Sheet1'!F16=""Hit Budget"",'Sheet2'!C20=1,H26=""Success""),RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01/2," & _
      "IF('Sheet1'!F22=""Budget Surpassed"",RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*2*0.01," & _
      "IF('Sheet1'!F16=""Budget Exceeded"",RANDBETWEEN('Sheet2'!D40,'Sheet2'!D41)*0.01))))))),""Not Applicable"")"
    .Value = .Value   'If you want the value. If you want the formula, delete this line.
  End With
End Sub

----------------------------​

The other option, with more lines, but perhaps more clarity in the conditions is as follows:

Sub formula_vba_2()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim randB As Double, vle As Variant, x As Variant
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("My worksheet")
  
  Randomize
  randB = WorksheetFunction.RandBetween(sh2.Range("D40").Value, sh2.Range("D41").Value)
  
  If sh1.Range("F16").Value = "Missed Budget" Then
    vle = 0
  Else
    If sh2.Range("C23").Value = "" And sh2.Range("C22").Value = "Unknown" Then
      vle = "Percentage of Budget"
    Else
      If sh1.Range("F16").Value = "Hit Budget" And sh2.Range("C20").Value = 1 And _
        (sh3.Range("E26").Value = "Success" Or sh3.Range("H26").Value = "Success") Then
       vle = randB * 0.01 / 2
      Else
        If sh1.Range("F22").Value = "Budget Surpassed" Then
          vle = randB * 2 * 0.01
        Else
          If sh1.Range("F16").Value = "Budget Exceeded" Then
            vle = randB * 0.01
          Else
            'If no condition is met?
          End If
        End If
      End If
    End If
  End If
  x = Application.Min(1, vle)
  With sh3.Range("A33")
    If IsError(x) Then .Value = "Not Applicable" Else .Value = x
  End With
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[DanteAmor]

This would be another way of putting the code in the Case statement.
The order of the validations must correspond to the priority.
Next I show you the order as you have it in your formula.
There remains a doubt, since if no condition is met your formula returns a FALSE. I am putting the same in the following macro. Check the comments in the macro:

Sub formula_vba_3()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim randB As Double, vle As Variant, x As Variant
 
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("My worksheet")
 
  Randomize
  randB = Int((sh2.Range("D41").Value - sh2.Range("D40").Value + 1) * Rnd + sh2.Range("D40").Value)
 
  Select Case True
    Case sh1.Range("F16").Value = "Missed Budget"
      vle = 0
    Case sh2.Range("C23").Value = "" And sh2.Range("C22").Value = "Unknown"
      'Returning a text as a result and putting it in the Min function causes the result to be an error.
      vle = "Percentage of Budget"
    Case sh1.Range("F16").Value = "Hit Budget" And sh2.Range("C20").Value = 1 And _
        (sh3.Range("E26").Value = "Success" Or sh3.Range("H26").Value = "Success")
      vle = randB * 0.01 / 2
    Case sh1.Range("F22").Value = "Budget Surpassed"
      vle = randB * 2 * 0.01
    Case sh1.Range("F16").Value = "Budget Exceeded"
      vle = randB * 0.01
    Case Else
      'If no condition is met?
      vle = False
  End Select
  x = Application.Min(1, vle)
  With sh3.Range("A33")
    If IsError(x) Then .Value = "Not Applicable" Else .Value = x
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Moonbeam111]

I like the second formula you put up the most, thank you. However, sometimes the values in Sheet2.Range("D40") and ("D41") are "#N/A" and when I run your code it says there is a type mismatch in the following line because they are not numbers.

randB = WorksheetFunction.RandBetween(sh2.Range("D40").Value, sh2.Range("D41").Value)

Is there a possible way around this? If there is any error at all I would like the result to be "Not Applicable".

Thanks again.

 

[DanteAmor]

Is there a possible way around this? If there is any error at all I would like the result to be "Not Applicable".

Try this:

Sub formula_vba_3()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim randB As Double, vle As Variant, x As Variant
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("My worksheet")
  
  Randomize
  If IsError(sh2.Range("D40").Value) Or IsError(sh2.Range("D41").Value) Then
    vle = "err"
  Else
    randB = WorksheetFunction.RandBetween(sh2.Range("D40").Value, sh2.Range("D41").Value)
  End If
  
  Select Case True
    Case vle = "err"
      
    Case sh1.Range("F16").Value = "Missed Budget"
      vle = 0
    Case sh2.Range("C23").Value = "" And sh2.Range("C22").Value = "Unknown"
      'Returning a text as a result and putting it in the Min function causes the result to be an error.
      vle = "Percentage of Budget"
    Case sh1.Range("F16").Value = "Hit Budget" And sh2.Range("C20").Value = 1 And _
        (sh3.Range("E26").Value = "Success" Or sh3.Range("H26").Value = "Success")
      vle = randB * 0.01 / 2
    Case sh1.Range("F22").Value = "Budget Surpassed"
      vle = randB * 2 * 0.01
    Case sh1.Range("F16").Value = "Budget Exceeded"
      vle = randB * 0.01
    Case Else
      'If no condition is met?
      vle = False
  End Select
  x = Application.Min(1, vle)
  With sh3.Range("A33")
    If IsError(x) Then .Value = "Not Applicable" Else .Value = x
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Moonbeam111]

Great, that works as intended