Ask for number with application box and use it in a function through a range

Question

Range("H1").Select
ActiveCell.FormulaR1C1 = "Range Total"

Range("I1").Select
ActiveCell.FormulaR1C1 = "Range Real"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*10)"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*10"

Range("H3").Select

Do Until ActiveCell.Offset(0, -1).Value = ""            
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*10"            
    ActiveCell.Offset(0, 1).Activate            
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*10)"            
    ActiveCell.Offset(1, -1).Activate    
Loop

This code fills columns "H" and "I" with values calculated with a if formula from values of the columns before and multiplied with a fixed value of "10".

What I wanted was to ask for a number with an application.inputbox and used it as a multiplier in the if formula.

Dim factor As Integer
factor = Application.InputBox("What is the multiplier value :", Type:=1)

Range("H1").Select
ActiveCell.FormulaR1C1 = "Range Total"

Range("I1").Select
ActiveCell.FormulaR1C1 = "Range Real"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*factor)"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*factor"

Range("H3").Select  
Do Until ActiveCell.Offset(0, -1).Value = ""            
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*factor"            
    ActiveCell.Offset(0, 1).Activate            
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*factor)"            
    ActiveCell.Offset(1, -1).Activate    
Loop

This was my atempt and it doesn't work

You should write the factor into a cell - and reference this cell within your formula. And you definitly should read [How to avoid select](https://stackoverflow.com/q/10714251/16578424) — Ike, Oct 19 '22 at 10:15
Please do not write in all capital letters! This is considered screaming. — Pᴇʜ, Oct 19 '22 at 10:32
`factor` is a variabe that is declared in VBA and does not exist in a formula. Therefore you can only write it's value into the formula by changing `"=(RC[-3]-RC[-2])*factor"` into `"=(RC[-3]-RC[-2])*" & factor`. Alternative is to write the factor into a cell and reference this cell in the formula. — Pᴇʜ, Oct 19 '22 at 10:35
i used "factor" as a general word, i didn't want to use any specific formula associated with the word "factor". And how do I reference the cell? — Tvle26, Oct 19 '22 at 10:54
Is there a reason for wanting to have a formula in the cell, if not you could use vba to do the calculation then put that value directly into the cell. — 5202456, Oct 19 '22 at 11:12

score 1 · Answer 1 · answered Oct 19 '22 at 12:27

You need to write the value of the factor into the formula and not the variable name!

For example if factor = 5 and you write ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*factor" this will write the following formula to the cell =(RC[-3]-RC[-2])*factor and it does not know what factor is because it only exists in VBA.
But if you write ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*" & factor it will write =(RC[-3]-RC[-2])*5 as formula into the cell. Because now it uses the value of the variable factor and appends it to the string "=(RC[-3]-RC[-2])*".

Dim factor As Long
factor = Application.InputBox("What is the multiplier value:", Type:=1)

Range("H1").Select
ActiveCell.FormulaR1C1 = "Range Total"

Range("I1").Select
ActiveCell.FormulaR1C1 = "Range Real"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*" & factor & ")"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*" & factor

Range("H3").Select  
Do Until ActiveCell.Offset(0, -1).Value = ""            
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*" & factor            
    ActiveCell.Offset(0, 1).Activate            
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*" & factor & ")"            
    ActiveCell.Offset(1, -1).Activate    
Loop

Ask for number with application box and use it in a function through a range

1 Answers1