 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Macro for 2 Tables
Hello All I was wondering if it is possible to write a macro for the following situation and if so how? Basically we have 2 tables with various formulas in its cells, each table yields different results based on the formulas. Outside of the tables we have 6 cells which are the end results from the tables. Now the thing is we only want to use one table each time dependent on the results. We want both tables to run but we only want to choose one for the end, so can a macro be written that shows the results of table 1 in the "Results Cells" when clicked and then can be clicked again to show the results of table 2 and then back and forth? I hope I explained this well, if there is any confusion please let me know. Thanks. Peter Your question is wide open. Yes, what you want can be done with a macro but I have no idea how since I don't know what you have. Do you have some sort of a header cell labeled "Results Cells" that you will be clicking on? You say "Outside of the tables we have 6 cells which are the end results from the tables." What is in those 6 cells? Formulas? Those 6 cells cannot contain formulas unless your tables contain some variables that you want the macro to change when "Results Cells" is clicked on. Or do those 6 cells contain formulas and you want those formulas changed by the macro? You say "We want both tables to run.....". What do you mean by "run"? What is happening when a table "runs"? Otto > Hello All > I was wondering if it is possible to write a macro for the following [quoted text clipped - 15 lines] > know. > Thanks. Hey Otto, sorry for the confusion. Don't worry about each table "running", bad wording on my part, I just meant they both need to be on the page. Picture it like this...each table has various formulas in various cells that produce results in 6 "result cells". Each table has these result cells. The results will be different between the two tables as their formulas are different. Now outside of the tables we have 6 result cells that we want to show. The tables and their respective result cells are behind the scenes. We want to be able to toggle what the results are in the outside cells from Table 1 to Table 2. So one click shows the results from table 1 and the next click will show the results from table 2 and back and forth. > Peter > Your question is wide open. Yes, what you want can be done with a macro [quoted text clipped - 27 lines] > > know. > > Thanks. Peter Here is a little macro that will do what you want. As written, the following is required: The range of 6 cells that show the result of Table 1 is named "Table1Results". The range of 6 cells that show the result of Table 2 is named "Table2Results". The single cell that you click on is named "TableCell". That cell will display "Table 1" or "Table 2". The 6 cells that will display the results of either of the tables are located in the row immediately below the row that has the "TableCell" and the first of those 6 cells is immediately below the "TableCell". You can rearrange things as you want and rename things as you want, but you must change the macro accordingly. Note that this macro is a sheet macro. As such it must be placed in the sheet module of your sheet. To access that module, right-click on the sheet tab, and select View Code. Paste this macro into that module. "X" out of the module to return to your sheet. This macro will fire whenever cell "TableCell" is selected. Come back if you need more or if you need to change the macro. HTH Otto Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("TableCell")) Is Nothing Then If Target.Value = "Table 1" Or _ IsEmpty(Target.Value) Then Range("TableCell").Value = "Table 2" Range("Table2Results").Copy Range("TableCell").Offset(1).PasteSpecial xlPasteValues Else Range("TableCell").Value = "Table 1" Range("Table1Results").Copy Range("TableCell").Offset(1).PasteSpecial xlPasteValues End If Application.EnableEvents = False Range("TableCell").Offset(1).Select Application.EnableEvents = True Application.CutCopyMode = False End If End Sub > Hey Otto, sorry for the confusion. > [quoted text clipped - 59 lines] >> > know. >> > Thanks. Thanks Otto, this macro is exactly what I need. The last thing I need help with is that the 7 cells (used to be 6, I had to add one) with the results need to be moved and I don't know macros well enough to rewrite it. Let me give you the exact cells where the results of table 1 and table 2 are where I want the end results to be, if that helps. Table 1 result cells: C104, C107, C111, C112, C119, C122, C125 Table 2 result cells: G104, G107, G111, G112, G119, G122, G125 Final result cells: N8, O8, P8, Q8, N10, O10, P10 The results need to be in this order as each cell represents something different. For example, the C104 or G104 cell (depending on which table is used) corresponds to the N8 result cell, C107 or G107 corresponds to the O8 result cell, and so forth. Thanks again. > Peter > Here is a little macro that will do what you want. As written, the [quoted text clipped - 98 lines] > >> > know. > >> > Thanks. Peter It doesn't matter how many cells there are in the results of each table. The macro works with the named range, be it one cell or 1000 cells. You simply select the cells and name the range. Now, your actual Table X result cells are not contiguous, so you have to know how to select a non-contiguous set of cells. Do you? Here's how. Click on the first cell, say C104. Make sure that you don't hold the Ctrl key down until AFTER you have selected that first cell. Now hold the Ctrl key down and click on each of the remaining cells of that group. Release the Ctrl key. Ignore the fact that the first cell you selected is not the same color as the rest of them. Now click on Insert (in the menu at the top of the screen) - Name - Define. Type in "Table1Results" without the quotes. Now do the same thing with the Table 2 result cells. Don't forget to NOT hold down the Ctrl key until after you have selected the first cell. Name that group "Table2Results". Because the destination cells (Final result cells) are also not contiguous, I'll have to change the code to place the results where you want them and will send you the finished macro. One question I have is what cell do you want to use as the trigger to fire this thing? In the macro I sent you before that was the cell above the first of the final result cells. That may not be viable now, so tell me what cell you want to use. Otto > Thanks Otto, this macro is exactly what I need. The last thing I need help > with is that the 7 cells (used to be 6, I had to add one) with the results [quoted text clipped - 132 lines] >> >> > know. >> >> > Thanks. Peter It doesn't matter how many cells there are in the results of each table. The macro works with the named range, be it one cell or 1000 cells. You simply select the cells and name the range. Now, your actual Table X result cells are not contiguous, so you have to know how to select a non-contiguous set of cells. Do you? Here's how. Click on the first cell, say C104. Make sure that you don't hold the Ctrl key down until AFTER you have selected that first cell. Now hold the Ctrl key down and click on each of the remaining cells of that group. Release the Ctrl key. Ignore the fact that the first cell you selected is not the same color as the rest of them. Now click on Insert (in the menu at the top of the screen) - Name - Define. Type in "Table1Results" without the quotes. Now do the same thing with the Table 2 result cells. Don't forget to NOT hold down the Ctrl key until after you have selected the first cell. Name that group "Table2Results". Because the destination cells (Final result cells) are also not contiguous, I'll have to change the code to place the results where you want them and will send you the finished macro. One question I have is what cell do you want to use as the trigger to fire this thing? In the macro I sent you before that was the cell above the first of the final result cells. That may not be viable now, so tell me what cell you want to use. Otto > Thanks Otto, this macro is exactly what I need. The last thing I need help > with is that the 7 cells (used to be 6, I had to add one) with the results [quoted text clipped - 132 lines] >> >> > know. >> >> > Thanks. Hey Otto, the trigger cell I want to use is Q21 Thanks. > Peter > It doesn't matter how many cells there are in the results of each table. [quoted text clipped - 153 lines] > >> >> > know. > >> >> > Thanks. Peter Here are the 2 macros you need. Place them both in the sheet module of your sheet as before. Delete the previous macros. You need to name 2 ranges. The first is the range of result cells for Table 1. Name this range Table1Results as before. Be sure that you include ONLY the 7 cells that hold the Table 1 results. Do the same for the Table 2 results and name that range Table2Results. Delete any other range names that I previously told you to use. Here is how it works: If Q21 is empty and you click in that cell, you will get the Table 2 results in the final result cells, and you will get "Table 2" in Q21. You will notice that Q21 will contain only "Table 1" or "Table 2" or be blank. The blank is the initial condition, After you first click in Q21, it will contain only one of the 2 table references. If you click in Q21 when Q21 is occupied, you will get the other table reference in Q21 and the corresponding result cell values in the final result cells. Post back if you need more. HTH Otto Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("Q21")) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False If Target.Value = "Table 1" Or _ IsEmpty(Target.Value) Then Range("Q21").Value = "Table 2" Call CopyPaste(Range("Table2Results")) Else Range("Q21").Value = "Table 1" Call CopyPaste(Range("Table1Results")) End If Range("Q22").Select Application.EnableEvents = True Application.CutCopyMode = False Application.ScreenUpdating = True End If End Sub Sub CopyPaste(TheTableRng As Range) Dim i As Range Dim Dest As Range For Each i In TheTableRng Select Case i.Address(0, 0) Case "C104", "G104": Set Dest = Range("N8") Case "C107", "G107": Set Dest = Range("O8") Case "C111", "G111": Set Dest = Range("P8") Case "C112", "G112": Set Dest = Range("Q8") Case "C119", "G119": Set Dest = Range("N10") Case "C122", "G122": Set Dest = Range("O10") Case "C125", "G125": Set Dest = Range("P10") End Select i.Copy Dest Next i End Sub > Hey Otto, the trigger cell I want to use is Q21 > [quoted text clipped - 198 lines] >> >> >> > know. >> >> >> > Thanks. That did it! Thanks for all of your help Otto. > Peter > Here are the 2 macros you need. Place them both in the sheet module of [quoted text clipped - 253 lines] > >> >> >> > know. > >> >> >> > Thanks. You're welcome. Thanks for the feedback. Otto > That did it! Thanks for all of your help Otto. > [quoted text clipped - 286 lines] >> >> >> >> > know. >> >> >> >> > Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.