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 |
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 |
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! |
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 |
All times are GMT +1. The time now is 01:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com