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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input formula variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input formula variable
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input formula variable
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) "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 |
Reply |
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 |