VBA code to divide a range of cells by a variable with an option to select each time

[Nravota]

Hi everyone,

First, thank you for any information and ideas shared. I need a vba code which is able to divide range of numbers with two decimals (prices) with decimal coefficient. For instance, I have values in A1:A25 and I need to divide them with a different value each time (0.9 or 0.92 or 0.9205). Then I would like to have the results with two decimals pasted in another column. So, I need to have an option in the macro to enter the variable I need, before it gave the results.

Can someone help with this?

Thank you in advance for the support!

 

[Joe4]

Welcome to the Board!

Maybe something like this?

Sub MyDivide()


    Dim myRange As Range
    Dim cell As Range
    Dim myDivisor As Double
    Dim myOffset As Integer
    
    Application.ScreenUpdating = False
    
'   Determine range to apply to
    Set myRange = Range("A1:A25")
    
'   Indicate how many columns to offset the results by (i.e. "B" would be 1 columns for "A")
    myOffset = 1
    
'   Prompt to enter divisor
    myDivisor = InputBox("Enter the value you wish to divide by")
    
'   Divide each cell and round to 2 decimals
    For Each cell In myRange
        cell.Offset(0, 1) = Round(cell / myDivisor, 2)
    Next cell
        
    Application.ScreenUpdating = True
    
End Sub

 

[Nravota]

Great! Many thanks, this function works perfectly. I was also wondering if there is a way to select the range with the cursor each time instead of fixing it from A1 to A25? Thanks once again for sharing this.

 

[Nravota]

Great! Many thanks, this function works perfectly. I was also wondering if there is a way to select the range with the cursor each time instead of fixing it from A1 to A25? Thanks once again for sharing this.

Hi, I found the solution, it can't be more simple, but if someone new to vba like me ever wonders about it:

Set myRange = Selection