Formula in given via input box

[Petr]

Hallo,
I have following problem. I would need to use the user values
submitted via inputbox into the formula of the following type:

ActiveCell.FormulaR1C1 = "='HIO - Exp'!R[-21]C-'HIO - Rev'!R[-26]C"

I.e. in this case the parameter "HIO" should be submitted via
inputbox.

I have the following code which ends with error.

Sub formula_input_test

IntgInput = InputBox("Submit the segment name (abbrev.)")

segment = IntgInput

Range("A1").Select
ActiveCell.FormulaR1C1 = "='& segment & - Exp & !R[-21]C - '& segment
& - Rev & !R[-26]C"

End Sub


Thanks a lot in advance for any suggestions.
Best regards, Petr Duzbaba

 

[Tom Ogilvy]

s = InputBox("Submit the segment name (abbrev.)")

ActiveCell.FormulaR1C1 = "='" & s & " - Exp'!R[-21]C-'" & s & " -
Rev'!R[-26]C"


Demo['d from the immediate window:

s = "HIO"
? "='" & s & " - Exp'!R[-21]C-'" & s & " - Rev'!R[-26]C"
='HIO - Exp'!R[-21]C-'HIO - Rev'!R[-26]C

 

[Tom Ogilvy]

Or, fixing yours:

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp & !R[-21]C - '" _
& segment & " - Rev & !R[-26]C"

 

[Guest]

you're confused by your quotes !


Sub test()

Dim segment As String
Dim formula As String
segment = "HIO"

formula = "=" & Chr(39) & segment & " - Exp" & Chr(39) & "!R[-21]C - " _
& Chr(39) & segment & " - Rev" & Chr(39) & "!R[-26]C"



selection.FormulaR1C1 = formula


End Sub

 

[Guest]

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp '!R[-21]C - '" _
& segment & " - Rev '!R[-26]C"

Or, fixing yours:

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp & !R[-21]C - '" _
& segment & " - Rev & !R[-26]C"

--
Regards,
Tom Ogilvy

Hallo,
I have following problem. I would need to use the user values
submitted via inputbox into the formula of the following type:

ActiveCell.FormulaR1C1 = "='HIO - Exp'!R[-21]C-'HIO - Rev'!R[-26]C"

I.e. in this case the parameter "HIO" should be submitted via
inputbox.

I have the following code which ends with error.

Sub formula_input_test

IntgInput = InputBox("Submit the segment name (abbrev.)")

segment = IntgInput

Range("A1").Select
ActiveCell.FormulaR1C1 = "='& segment & - Exp & !R[-21]C - '& segment
& - Rev & !R[-26]C"

End Sub


Thanks a lot in advance for any suggestions.
Best regards, Petr Duzbaba

 

[Tom Ogilvy]

Yep -Missed some of the Ampersands you put in - sorry

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp & !R[-21]C - '" _
& segment & " - Rev & !R[-26]C"

should be

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp'!R[-21]C - '" _
& segment & " - Rev'!R[-26]C"

so I should always bench test it in the immediate window:

segment = "HIO"
? "='" & segment & " - Exp'!R[-21]C - '" _
& segment & " - Rev'!R[-26]C"
='HIO - Exp'!R[-21]C - 'HIO - Rev'!R[-26]C

--
Regards,
Tom Ogilvy

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp '!R[-21]C - '" _
& segment & " - Rev '!R[-26]C"

Or, fixing yours:

ActiveCell.FormulaR1C1 = "='" & segment & " - Exp & !R[-21]C - '" _
& segment & " - Rev & !R[-26]C"

--
Regards,
Tom Ogilvy

Hallo,
I have following problem. I would need to use the user values
submitted via inputbox into the formula of the following type:

ActiveCell.FormulaR1C1 = "='HIO - Exp'!R[-21]C-'HIO - Rev'!R[-26]C"

I.e. in this case the parameter "HIO" should be submitted via
inputbox.

I have the following code which ends with error.

Sub formula_input_test

IntgInput = InputBox("Submit the segment name (abbrev.)")

segment = IntgInput

Range("A1").Select
ActiveCell.FormulaR1C1 = "='& segment & - Exp & !R[-21]C - '& segment
& - Rev & !R[-26]C"

End Sub


Thanks a lot in advance for any suggestions.
Best regards, Petr Duzbaba