CODEWITHSUNDEEP
excelvbainput
Andrew Hill
Andrew Hill

Reputation: 21

Can I use an input box to define a variable for multiplication of a cell 1 cell to the right?

I'm trying to multiply the cell to the right's value * an integer defined by an input box. I want to select a cell, and run the macro, and it ask for your first discount code, and second discount code. From there, it should move 1 cell to the left of the selection and perform (Cell to the right * First discount code entered.)

After that it should move one cell down, and perform (Cell above by 1 * Second Discount code entered)

The code below doesn't seem to work, it just tried to multiply by "@Discount1"

Sub Discount()
' Sets discounts for the total cost

Dim Discount1 As Integer
Discount1 = Application.InputBox("Please Enter Discount Here:", "Discount Percentage 1", "0.%%")

Dim Discount2 As Integer
Discount2 = Application.InputBox("Please Enter Discount 2 Here:", "Discount Percentage 2", "0.%%")


    Selection.Select
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "=RC[1] * Discount1"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C * Discount2"
    ActiveCell.Offset(1, 0).Range("A1").Select
    
End Sub

Upvotes: 1

Views: 99

Answers (1)

Andrew Hill
Andrew Hill

Reputation: 21

Switching it to As Variant instead of As Integer, and then doing what braX said to do resolved the issue :) Working code below!

Sub SPFTool()
'
' SPFTool Macro
'

'
    Dim Discount1 As Variant
    Discount1 = Application.InputBox("Please Enter Discount Here:", "Discount Percentage 1", "0.%%")

    Dim Discount2 As Variant
    Discount2 = Application.InputBox("Please Enter Discount 2 Here:", "Discount Percentage 2", "0.%%")

    Selection.Select
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "=RC[1] * " & Discount1
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C * " & Discount2
    ActiveCell.Offset(1, 0).Range("A1").Select
    
End Sub

Upvotes: 1

Related Questions