Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input formula variable
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How get user input that can then be used in formula, as criteria? | Excel Worksheet Functions | |||
Can I request user in input variable value within formula? | Excel Worksheet Functions | |||
How do I use a worksheet name as an input variable to a formula? | Excel Discussion (Misc queries) | |||
Message box- Assign a file name to a variable, from user input | Excel Programming | |||
Help creating link & formula from user input | Excel Programming |