Allowing user to choose variable from a list for arithmetic operations

Good morning guys.

Right now I have a macro to add 20% of a cell value (or a range of values) to that specific cell(s).

It goes like this:

Sub Multiplyby1point2()
Dim ws As Worksheet
Dim rngSel As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection
lRows = rngSel.Rows.Count
lCols = rngSel.Columns.Count

Num = 1.2

If rngSel.Count = 1 Then
   rngSel = rngSel * Num
Else
   Arr = rngSel
   For i = 1 To lRows
      For j = 1 To lCols
         Arr(i, j) = Arr(i, j) * Num
      Next j
   Next i
   rngSel.Value = Arr
End If

End Sub

I can obviously create a bunch of different macros so people can choose from in order to process something like apply a tax rates or a tariff fee addition:

suZfu96.png

But would you please help me if I can streamline it all and perhaps use a form plus a model so that users can simply select the "label" - and then the constant alongside with the arithmetic operations will be applied to the selected value(s)?

Thanks very much, I am still trying to familiarize myself with these basic concepts so it is a struggle at the beginning.

Hello
Find attached an example of a User Form that makes a calculation on a specific range of cells based on a List Box selection.

Perhaps you can amend this to your workbook.

DBY

A quick & easy fix would be to attach your "Num" value to an input box... something like:

Dim rngSel As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection
lRows = rngSel.Rows.Count
lCols = rngSel.Columns.Count

Num = 1 + 0.01 * InputBox("By what percentage would you like to increase the selected values?" & vbCrLf & "(e.g. To increase 20%, enter: 20)", "Select Percentage")

If rngSel.Count = 1 Then
   rngSel = rngSel * Num
Else
   Arr = rngSel
   For i = 1 To lRows
      For j = 1 To lCols
         Arr(i, j) = Arr(i, j) * Num
      Next j
   Next i
   rngSel.Value = Arr
End If

End Sub

Originally Posted by cantosh

A quick & easy fix would be to attach your "Num" value to an input box... something like:

Dim rngSel As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection
lRows = rngSel.Rows.Count
lCols = rngSel.Columns.Count

Num = 1 + 0.01 * InputBox("By what percentage would you like to increase the selected values?" & vbCrLf & "(e.g. To increase 20%, enter: 20)", "Select Percentage")

If rngSel.Count = 1 Then
   rngSel = rngSel * Num
Else
   Arr = rngSel
   For i = 1 To lRows
      For j = 1 To lCols
         Arr(i, j) = Arr(i, j) * Num
      Next j
   Next i
   rngSel.Value = Arr
End If

End Sub

Hi -

I like the quick Fix - but is there anyway we can leave the choice of what type of computation (+.-,* or /) to the end user?

Thanks

The easiest way to add computation type to the simple fix is by including an additional input box in which the user would type "Add", "Subtract", etc., but that might not be very user-efficient. If you want something more user friendly (radio buttons, etc.), you would need a userform. If DBY's attachment doesn't do it for you, it would be helpful if you could post a small sample workbook (use: Go Advanced --> Manage Attachments) so we can see what you're working with, rather than operating off of hypothetical data.