Getting macro to return input made into input box

Hi i am very new to excel macros and i am very lost. I am trying to get a macro to calculate the sum of a number of cells and from that calculate an escalation based on the input made into an input box.. this is working fine but i need to get the macro to return the input made into the input box in the cell below with a percentage symbol. This i can't figure out.. This is what i've tried..

  Dim intEscRate As Integer
    intEscRate = InputBox("Enter an escalation percentage: ")
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C+R[-3]C+R[-2]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C*1." & intEscRate & ""
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = Val("intExcRate")
    ActiveCell.Offset(1, 0).Range("A1").Select

It's the last two lines that aren't right. For example if i put 25 into the inpur box i need the macro to put 25% into the cell below the other two values.

Can anyone help me please??

Hello and welcome to the forum. What is val in your last formula? Is this a user-defined function because Excel doesn't seem to recognize it.

abousetta

OK. I'm not exactly sure what you are attempting to accomplish but this should give you the results you are after.

Sub test()

Dim intEscRate As Integer
    intEscRate = InputBox("Enter an escalation percentage: ")
    With ActiveCell
        .Formula = "=SUM(R[-4]C+R[-3]C+R[-2]C)"
        .Offset(1, 0).Formula = "=R[-1]C*1." & intEscRate
        .Offset(2, 0).Formula = intEscRate / 100
        .Offset(2, 0).Style = "Percent"
    End With

End Sub

If you could post a workbook then we may be able to tweak this better for you.

Hope this helps.

abousetta

Hi,

No problem. So let's break it down:

    With ActiveCell
    End With

This saves time in writing the code and running it. Instead of Activecell. something and Activecell. something you can simply use With... End With and make sure the dot stays with the next lines.

        .Formula = "=SUM(R[-4]C+R[-3]C+R[-2]C)"
        .Offset(1, 0).Formula = "=R[-1]C*1." & intEscRate
        .Offset(2, 0).Formula = intEscRate / 100
        .Offset(2, 0).Style = "Percent"

Offset method you already know so I won't go into it, but all I did was use the Activecell as the reference and offseted 1 and 2 cells and added the formulas. For the second formula I divided by 100 (to get a percentage) and formatted the cell as percentage rather than general.

Hope this helps.

abousetta

P.S. Thanks for marking the thread as Solved.