CODEWITHSUNDEEP
variablesexcelinputexcel-formulavba
Mehper C. Palavuzlar
Mehper C. Palavuzlar

Reputation: 10409

Use an input variable inside a formula in macro

I want to use a user input inside a formula as follows:

Sub example()

Dim StockDays As Integer
StockDays = InputBox(Prompt:="How many days?")

Range("AG2").FormulaR1C1 = "=ROUNDUP(RC[-6]*" & StockDays & "/90, 0)"
Range("AG2").Select
Selection.AutoFill Destination:=Range(Cells(2, 33), Cells(1500, 33))

End Sub

When run, the above code throws an error at the ROUNDUP line.

Run-time error 1004.
Application-defined or object-defined error.

I think the problem is related to the variable StockDays.

How can I arrange the code so that I can make it work?

Upvotes: 0

Views: 10171

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

I have commented the code so you shouldn't have any problem understanding it :)

Option Explicit

Sub example()
    Dim StockDays As Integer

    '~~> Type:=1  will ensure that the user enters only numbers
    StockDays = Application.InputBox(Prompt:="How many days?", Type:=1)

    '~~> No Need to autofill. You can fill all the range in one go
    Thisworkbook.Sheets("Sheet1").Range("AG2:AG1500").FormulaR1C1 = _
    "=ROUNDUP(RC[-6] * " & StockDays & "/ 90, 0)"

    'OR this as mentioned in your comment

    Thisworkbook.Sheets("Sheet1").Range("AG2:AG1500").FormulaR1C1 = _
    "=ROUNDUP((RC[-6]* " & StockDays & "/90),0)"
End Sub

Upvotes: 3

Naveen Babu
Naveen Babu

Reputation: 1584

I think this should work

Range("AG2").FormulaR1C1 = "=ROUNDUP(RC[-6] * " & StockDays & " / 90, 0)"

the mistake u did was, you where applying a formula but forgot to replace the variable u used with the value in the varaiable

Upvotes: 0

Related Questions