Converting excel formula into vba funtcion

I would like to convert this formula:

=IF(AND(ISTEXT(A11)),"",
      IF(AND(A11>=100,A11<=199),A11+15,
        IF(AND(A11>=200,A11<=599),A11+30,
          IF(AND(A11>=600,A11<=899),A11+40,
            IF(AND(A11>=900,A11<=1099),A11+50,
              IF(AND(A11>=1100),ROUNDDOWN(A11*105%,-1)))))))

into a vba function where A11 is a variable that I can choose while calling the function. (So I can use it on any cell that I choose). I'm not interested in learning vba THAT much but want to help my dad a little to automate this process. Any kind soul to help me on this? Thanks for any tips

1 answer

  • answered 2021-05-03 18:23 hbere

    Slight addition to the comments above - great job to the authors for those formulas - combining a formula with a What-If Analysis Data Table will help ensure better data quality and easier updating over time because the formula will only have to be updated in one place, B11 in the following screenshot:

    Excel Data Table Screenshot

    =IF(ISTEXT(A11),"",CHOOSE(MATCH(A11,{100,200,600,900,1100}),15,30,40,50,ROUNDDOWN(A11*0.05,-1))+A11)
    

    As an alternative, I see you mentioned VBA in your original question. Here is a VBA subroutine, in case that is what you are looking for:

    Sub CalcThis()
    On Error GoTo ErrorHandler:
        'Declare Variables
        Dim InputCell As Range     'Range for input cell
        Dim OutputCell As Range    'Range for output cell
        Dim val As Double
    
        'Get Input Cell
        Set InputCell = Application.InputBox(Title:="Input Cell", prompt:="Select input cell.", Type:=8)
        
        'Calculate Value
        val = InputCell
        If (val >= 100 And val <= 199) Then
            val = val + 15
        ElseIf (val >= 200 And val <= 599) Then
            val = val + 30
        ElseIf (val >= 600 And val <= 899) Then
            val = val + 40
        ElseIf (val >= 900 And val <= 1099) Then
            val = val + 50
        ElseIf (val >= 1100) Then
            val = val * 1.05
        Else
            Err.Raise (1)
        End If
        
        'Save Value to Output Cell
        Set OutputCell = Application.InputBox(Title:="Output Cell", prompt:="Select output cell.", Type:=8)
        OutputCell = val
    
    Exit Sub
    
    ErrorHandler:
        MsgBox "Error: " & Err.Description & ".  Please try again.", vbOKOnly
    
    End Sub