User input formula variable

[Petr]

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),column_shift2))"

End Sub

 

[Tom Ogilvy]

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

 

[Sharad]

Hello,
change the last code as under:

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

Furthr you can reduce the If And IF And like below

If Int(IntgInput1) > -1 Or Int(IntgInput1) < -6 Then
MsgBox "Invalid input!", vbCritical
Exit Sub
End If

If Int(IntgInput2) < 1 Or Int(IntgInput1) > 6 Then
MsgBox "Invalid input!", vbCritical
Exit Sub
End If


Sharad

 

[Bob Phillips]

Try this

Sub inputbox_variable_formula()

Application.ScreenUpdating = True
intginput1 = InputBox("Negative column shift (No of columns I.)")
If intginput1 = "" Then Exit Sub
If intginput1 >= 0 Or Not intginput1 < -5 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 intginput2 <= 0 Or 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[" & intginput1 & _
"],gfs_br_rev,0)," & intginput2 & "))"

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)