Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
How would the macro determine where to go based on the letter and column
combination. assume the cell in column E would correspond to the cell selected in one of columns F to P set rng = Intersect(columns(5),ActiveCell.EntireRow) sLetter = rng.Value Now what? -- Regards, Tom Ogilvy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
Tom Ogilvy wrote: How would the macro determine where to go based on the letter and column combination. assume the cell in column E would correspond to the cell selected in one of columns F to P set rng = Intersect(columns(5),ActiveCell.EntireRow) sLetter = rng.Value Now what? -- Regards, Tom Ogilvy Tom, I am sorry that my desrciption is not concise and clear. Let me summuarize briefly: 1. I would create 20 "table"worksheets, each sheet having a particular table. 2. On each row of the "main" worksheet, I would enter a code letter in column E. 3. I would then go to cell in the same row column F, making F the active column, and, if I need to refer to a table worksheet, I would invoke a macro [Crlt-Shift Z for example], and the macro would do something like...IF the value in col E = "M" and the active column = F, THEN go to sheet 9. 4. After viewing the table in sheet 9, I would then want to press ENTER or any other key and go back to the cell that was active when I invoked the macro I could enter the value I viewed in the table. As i mentioned before the column from which i invoke the macro could be from col F to col P. I hope this is more clear, and i apologize for not being able to be more clean AND concise. Thanks, Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
for that approach you would need 11 columns x n rows of if statements. If
you have on 20 possible table sheets to go to, that doesn't make much sense. If you have a rule that would be used, then you would want to code the rule. Or you could set up another table on another sheet where you lookup the letter and column number and return the sheet to go to. -- Regards, Tom Ogilvy "Billy" wrote: Tom Ogilvy wrote: How would the macro determine where to go based on the letter and column combination. assume the cell in column E would correspond to the cell selected in one of columns F to P set rng = Intersect(columns(5),ActiveCell.EntireRow) sLetter = rng.Value Now what? -- Regards, Tom Ogilvy Tom, I am sorry that my desrciption is not concise and clear. Let me summuarize briefly: 1. I would create 20 "table"worksheets, each sheet having a particular table. 2. On each row of the "main" worksheet, I would enter a code letter in column E. 3. I would then go to cell in the same row column F, making F the active column, and, if I need to refer to a table worksheet, I would invoke a macro [Crlt-Shift Z for example], and the macro would do something like...IF the value in col E = "M" and the active column = F, THEN go to sheet 9. 4. After viewing the table in sheet 9, I would then want to press ENTER or any other key and go back to the cell that was active when I invoked the macro I could enter the value I viewed in the table. As i mentioned before the column from which i invoke the macro could be from col F to col P. I hope this is more clear, and i apologize for not being able to be more clean AND concise. Thanks, Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
Sub gotoSheet()
On Error GoTo no_sheet Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate Exit Sub no_sheet: MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value & Selection.Column Err.Clear On Error GoTo 0 End Sub -- Festina Lente "Billy" wrote: I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
PapaDos wrote: Sub gotoSheet() On Error GoTo no_sheet Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate Exit Sub no_sheet: MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value & Selection.Column Err.Clear On Error GoTo 0 End Sub -- Festina Lente "Billy" wrote: PapaDos, Thats works! I can make that do what i want. Thanks so very muhcto you, and to Tom, for all your help!! Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
Billy wrote: PapaDos wrote: Sub gotoSheet() On Error GoTo no_sheet Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate Exit Sub no_sheet: MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value & Selection.Column Err.Clear On Error GoTo 0 End Sub -- Festina Lente "Billy" wrote: PapaDos, Thats works! I can make that do what i want. Thanks so very muhcto you, and to Tom, for all your help!! Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy I hate to ask this PapaDos, but I need one other thing to make this macro perfect. After the macro takes me to the derired sheet, i would like to view it only, then press "Enter" to return to the original sheet and active cell. Thak you again so very much! Billy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and that is done at the Application level... A probably better way would be to add a link or a button on your sheets to go back. Let me know which method you want to use... -- Festina Lente "Billy" wrote: Billy wrote: PapaDos wrote: Sub gotoSheet() On Error GoTo no_sheet Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate Exit Sub no_sheet: MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value & Selection.Column Err.Clear On Error GoTo 0 End Sub -- Festina Lente "Billy" wrote: PapaDos, Thats works! I can make that do what i want. Thanks so very muhcto you, and to Tom, for all your help!! Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy I hate to ask this PapaDos, but I need one other thing to make this macro perfect. After the macro takes me to the derired sheet, i would like to view it only, then press "Enter" to return to the original sheet and active cell. Thak you again so very much! Billy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need If Then?
Well, first let me tell you that there will be many "calling" sheets in
the workbook, of course only 1 active at the time. I would like to avoid using the mouse if possible, as most of the work is keying in, and getting the mouse, moving the cursor, selecting, and going back to the keyboard would not be as fast as pressing Enter [or another key if need be]. therefore I think I would prefer a keystroke rather than the mouse. Also, there will be a bunch of "goto" sheets. I would like to have them hidden, then unhide the one selected to view, and hide again when finished viewing. Can this be accomplished also. I hate to impose, but this is way beyond my ability. You have no idea how much this is helping me in this project! Billy PapaDos wrote: I can show you how to use ENTER to go back to the calling sheet, but it is not a very good way to do it. We need to assign a macro to the ENTER key and that is done at the Application level... A probably better way would be to add a link or a button on your sheets to go back. Let me know which method you want to use... -- Festina Lente "Billy" wrote: Billy wrote: PapaDos wrote: Sub gotoSheet() On Error GoTo no_sheet Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate Exit Sub no_sheet: MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value & Selection.Column Err.Clear On Error GoTo 0 End Sub -- Festina Lente "Billy" wrote: PapaDos, Thats works! I can make that do what i want. Thanks so very muhcto you, and to Tom, for all your help!! Billy I need a macro that sort of performs a lookup, based on first, the letter I would input in col. E, and then second, the column that the curser is in, (which could be any or all cols. from F - P) and then, when i invoke it, displays a corresponding sheet that will contain a table I will make that relates to that letter/column combo. I dont know if it would be an If-Then type procedure or what, but is is certainly beyond my capability to do. Please not that this letter/column scenario is repeated on row after row in my spreadsheet, and not just one row. Thank you very much, Billy I hate to ask this PapaDos, but I need one other thing to make this macro perfect. After the macro takes me to the derired sheet, i would like to view it only, then press "Enter" to return to the original sheet and active cell. Thak you again so very much! Billy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|