Reputation: 10409
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
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
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