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

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

CodePudding user response：

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

CodePudding user response：

Thank you for the helpfull coments. I took the advice of giving a specific cell the multiplier factor that was asked in the application.inputbox and then referenced that cell in my formula and everything worked :) thank you very much :)