ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(OFFSET(gfs_br_rev_tc,MATCH(RC[" _
& column_shift1 & "],gfs_br_rev,0)," & _
column_shift2 & "))"

--
Regards,
Tom Ogilvy

"Petr" wrote in message
om...
Hallo,
I would need to use the variable (column offset) given via inputbox in
the formula of following type:
ActiveCell.FormulaR1C1 ="=OFFSET(br_exp_tc,MATCH(RC[-4],br_exp,0),5)",

I.e. to replace fixed input parameters (-4 and 5 in this case) with
user defined values (column_shift1 and column_shift2 in sample code
bellow).

I wrote the code below, but it does not work (end with runtime error).
Any ideas how to modify the code?
Thank you very much in advance for any suggestion.
Petr Duzbaba


Sub inputbox_variable_formula()

Application.ScreenUpdating = True
IntgInput1 = InputBox("Negative column shift (No of columns I.)")
If IntgInput1 = "" Then Exit Sub
If Not IntgInput1 = -1 And Not IntgInput1 = -2 And Not IntgInput1 = -3
And Not IntgInput1 = -4 And Not IntgInput1 = -5 And Not IntgInput1 =
-6 Then
MsgBox "Invalid input!", vbCritical

Exit Sub

End If
Application.ScreenUpdating = False

column_shift1 = IntgInput1

Application.ScreenUpdating = True
IntgInput2 = InputBox("Positive column shift (No of columns II.)")
If IntgInput2 = "" Then Exit Sub
If Not IntgInput2 = 1 And Not IntgInput2 = 2 And Not IntgInput2 = 3
And Not IntgInput2 = 4 And Not IntgInput2 = 5 And Not IntgInput2 = 6
Then
MsgBox "Invalid input!", vbCritical

Exit Sub

End If
Application.ScreenUpdating = False

column_shift2 = IntgInput2

ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(OFFSET(gfs_br_rev_tc,MATCH(RC[column_shift1],gfs_br_rev,0),colu
mn_shift2))"

End Sub