# 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

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:

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