Macro for 2 Tables

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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
 
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
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
 
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
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
 
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
 
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
 
That did it! Thanks for all of your help Otto.

Otto Moehrbach said:
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
Click to expand...
 
Back
Top