Macro help please!

[DA]

I had a spreadsheet with a 2-input variable EXCEL "Data Table", so I
could do sensitivity analysis. However, later, I have had to add some
complexity to the spreadsheet so that now, when I change either of
these two independent variables (aka input cells), I need to run a
macro (call it "macro1") to get my answer for the dependent variable
cell (aka output cell). This obviously means that I can't use the
data table functionality to do my sensitivity analysis.

So, my plan is to write a macro - call it "macro 2" that will manually
accomplish what a two-input EXCEL data table would do autoamtically,
i.e., if it didn't have to call macro1 each time either of the two
input values is changed. In fact, since I already have the data table
location set up, I would like to delete the table(C1,C2) references
but copy and paste the results into the current cell locations that
were already set up when it was a "live" data table. So, the user
will run a macro that will create the equivalent of a two-input
variable data table and the reader will not realize that it was not
generated by an EXCEL Data Table.

Suppose that one of the inputs is in cell C1 and the other is in cell
C2, Suppose that the dependent cell is cell C6 and it is at the top
left corner of a 2-variable EXCEL Data Table In other words, the 7
possible values for cell C1 are across row 6 in cells D6 thru J6 and
the 7 possible values for cell C2 are down column C, in cells C7 thru
C13. So, the results for the 7x7=49 different input combos would show
up in the block of cells between cells D7 thru J13. In other words, I
change an input value and then copy the result from cell C6 and edit-
paste-special-values it into a cell in this range.between cells D7
thru J13

The steps of the macro2 are:

Fix input1 at its minimum value, say 9. Set input2 at its miniumum
vakue, say 85%. Run macro 1. Take the resulf of cell C6 and edit-
paste-special-values it into cell D7.

Now increment input2 by 5% to 90%, Run macro 1 and take the new
result of cell C6 and edit-paste-special-values ("EPSV") into cell
E7.

Continue incremnting by 5% until we reach a vlaue of 115% and, with
each incrememnt, move the cursor one cell to the right and EPSV the
new result there until we have filled up cell J7 with a value.

Now set input1 to its next vlaue, by adding 2 to the prior vlaue, so
now it would be 9 + 2 = 11. Set input2 to its minimum vlaue of 85%.
Run macro1, then copy the cell C6 result and EPSV to cell D8, i.e.,
starting in the leftmost cell of the 2nd row.

Once again, increment input2 by 5% until we reach 115% and keep
pasting the cell C6 result in the next cell to the right.

Repeat this process until the last vlaue of input1 which is
9+2+2+2+2+2+2 = 21 . The final result would be pasted in cell J13

That is it.

Can someone wirte a macro for me that would do this, please? I
actually have many of these data tables but, if someone can help me
with this one, i think I can figure out how to do the others.

Thanks much. Remember that all I am trying to do is to manually
create something that looks like an EXCEL (two input) data table. The
reason i need to do this is that, each time, an input value is
changed, some other macro needs to be run in order for the output cell
to have the correct value.

Kindly ask for clairifcation if I have not been clear.

Thanks so much
Dean

 

[scattered]

I had a spreadsheet with a 2-input variable EXCEL "Data Table", so I
could do sensitivity analysis.  However, later, I have had to add some
complexity to the spreadsheet so that now, when I change either of
these two independent variables (aka input cells), I need to run a
macro (call it "macro1") to get my answer for the dependent variable
cell (aka output cell).  This obviously means that I can't use the
data table functionality to do my sensitivity analysis.

So, my plan is to write a macro - call it "macro 2" that will manually
accomplish what a two-input EXCEL data table would do autoamtically,
i.e., if it didn't have to call macro1 each time either of the two
input values is changed.  In fact, since I already have the data table
location set up, I would like to delete the table(C1,C2) references
but copy and paste the results into the current cell locations that
were already set up when it was a "live" data table.  So, the user
will run a macro that will create the equivalent of a two-input
variable data table and the reader will not realize that it was not
generated by an EXCEL Data Table.

Suppose that one of the inputs is in cell C1 and the other is in cell
C2,  Suppose that the dependent cell is cell C6 and it is at the top
left corner of a 2-variable EXCEL Data Table   In other words, the 7
possible values for cell C1 are across row 6 in cells D6 thru J6 and
the 7 possible values for cell C2 are down column C, in cells C7 thru
C13.  So, the results for the 7x7=49 different input combos would show
up in the block of cells between cells D7 thru J13.  In other words, I
change an input value and then copy the result from cell C6 and edit-
paste-special-values it into a cell in this range.between cells D7
thru J13

The steps of the macro2 are:

Fix input1 at its minimum value, say 9.  Set input2 at its miniumum
vakue, say 85%.  Run macro 1.  Take the resulf of cell C6 and edit-
paste-special-values it into cell D7.

Now increment input2 by 5% to 90%,  Run macro 1 and take the new
result of cell C6 and edit-paste-special-values ("EPSV") into cell
E7.

Continue incremnting by 5% until we reach a vlaue of 115% and, with
each incrememnt, move the cursor one cell to the right and EPSV the
new result there until we have filled up cell J7 with a value.

Now set input1 to its next vlaue, by adding 2 to the prior vlaue, so
now it would be 9 + 2 = 11.  Set input2 to its minimum vlaue of 85%.
Run macro1, then copy the cell C6 result and EPSV to cell D8, i.e.,
starting in the leftmost cell of the 2nd row.

Once again, increment input2 by 5% until we reach 115% and keep
pasting the cell C6 result in the next cell to the right.

Repeat this process until the last vlaue of input1 which is
9+2+2+2+2+2+2 = 21 . The final result would be pasted in cell J13

That is it.

Can someone wirte a macro for me that would do this, please?  I
actually have many of these data tables but, if someone can help me
with this one, i think I can figure out how to do the others.

Thanks much.  Remember that all I am trying to do is to manually
create something that looks like an EXCEL (two input) data table.  The
reason i need to do this is that, each time, an input value is
changed, some other macro needs to be run in order for the output cell
to have the correct value.

Kindly ask for clairifcation if I have not been clear.

Thanks so much
Dean

It sounds like you are using macros to implement functional
relationships between cells. If that is all that these macros do,
perhaps you can write them as functions rather than subs. VBA
functions can be used just like built-in EXCEL functions. For example,
if in a general code module you type:

Function CUBE(x As Double) As Double
CUBE = x ^ 3
End Function

If in A1 on a spreadsheet you enter the value 2 and then enter =
CUBE(A1) in B2, B2 will now display 8. If A1 is changed to 3, B1 will
automatically update to 27. The only catch is that this only works
with side-effect free functions - functions which return a value but
don't e.g. try to write to change the values in other cells. Something
like

Function CUBE(x As Double) As Double
Range("C1").Value = x ^ 3
CUBE = x ^ 3
End Function

won't work.

On the other hand - you can write and use functions which depend on
the values of cells which don't explicitly appear in the input to the
function. For example, suppose that you have a named cell called
"rate" which is intended to hold a sales tax rate. Then the following
*almost* works as expected:

Function WithTax(x As Double) As Double
WithTax = x * (1 + Range("Rate").Value)
End Function

If you, e.g., have the rate stored in A1, the pre-tax price in B1 and
the formula =WithTax(B1) in C1, the value in C1 will automatically
update when the value in B1 is changed. But - it won't automatically
update if the rate in A1 changes. If you need that functionality, you
could try:

Function WithTax(x As Double) As Double
Application.Volatile
WithTax = x * (1 + Range("Rate").Value)
End Function

Now changing A1 does change the contents of C1 automatically.

If you can't reduce your functionality to strict input-output
functions but still want things to update automatically, you can still
do so, but that would require code which handles something like the
worksheet change event. In this case, you would need to describe
exactly what you are trying to do a little more clearly. I sort of
followed you, but not completely. Seeing a copy of what you are
calling macro1 would help.

 

[DA]

Actually, someone else is writing macro1. I don't want to question
what it is doing (probably being used to avoid needing an iterative
worksheet), but simply want to call it to find the vlaue of an output
cell. Your ideas sound very clever but way too advanced for me. All
that I want to do is change values for two input cells (each has 7
choices), call a macro, and EPSV the value of an output cell into a 7
by 7 area. Can you just tell me how (give me the syntax) to do that,
hopefully with two loops that are something like:

do input1 from 85% to 115% in increments of 5%
do input2 from 9 to 23 in steps of 2
call macro1 'to produce the correct value of an output cell, don't
worry about what this does
copy output cell value to a location in a 7 by 7 area by using EPSV
end loop input1
end loop input2

Thanks
Dean

 

[GS]

Since you stated you have several tables to process, the following sub
accepts arguments so it can be used for all the tables, as long as they
all have the same structure. IOW, RngInput1Source must be a single row,
RngInput2Source must be a single column.

' Macro2
' Accepts range addresses for source/target ranges used.
' Accepts row/col start positions as long integers.
Sub Macro2(RngInput1 As String, RngInput2 As String, _
RngInputResult As String, RngInput1Source As String, _
RngInput2Source As String, StartRow As Long, _
StartCol As Long)

Dim i As Integer, j As Integer
Dim lNextRow As Long, lNextCol As Long
Dim vInput1Vals As Variant, vInput2Vals As Variant

vInput1Vals = Range(RngInput1Source)
vInput2Vals = Range(RngInput2Source)

lNextRow = StartRow '//initialize start row
Application.ScreenUpdating = False
For i = 1 To Range(RngInput1Source).Columns.Count
lNextCol = StartCol '//initialize start col
For j = 1 To Range(RngInput2Source).Rows.Count
'Set input values and run results macro
Range(RngInput1) = vInput1Vals(1, i)
Range(RngInput2) = vInput2Vals(j, 1)
Call Macro1

'Put results in table
Cells(lNextRow, lNextCol) = Range(RngInputResult).Value
lNextCol = lNextCol + 1
Next 'j
lNextRow = lNextRow + 1
Next 'i
Application.ScreenUpdating = True
End Sub

 

[DA]

Wow, this looks awesome. I guess it will find the values I want from
the row and column that are already there. I just need to range name
them, right, which i did. Is that it? That would be so nice!

However, for some reason, I am not seeing this macro in the list of
macros I can run after copying it into a module. I have never had
such a problem before. Do you have any idea why this could be
happening? I am using EXCEL 2007.

Thanks so much Garry.
Dean

 

[GS]

Wow, this looks awesome. I guess it will find the values I want from
the row and column that are already there. I just need to range name
them, right, which i did. Is that it? That would be so nice!

Yes! Just pass the details as args...

Sub Test_Macro2()
Call Macro2("C1", "C2", "C6", "D6:J6", "C7:C13", 7, 4)
'OR
Call Macro2(RngInput1:="C1", RngInput2:="C2", RngInputResult:="C6", _
RngInput1Source:="D6:J6", RngInput2Source:="C7:C13", _
StartRow:=7, StartCol:=4)
End Sub

However, for some reason, I am not seeing this macro in the list of
macros I can run after copying it into a module. I have never had
such a problem before. Do you have any idea why this could be
happening? I am using EXCEL 2007.

You can't see it in the macro list because it requires args. Use the
above sub to run it from the macros dialog. However, if you're going to
use it for many tables then I suggest you store the args info in a
dedicated area on the sheet, so it can be self-revising for each table.

Example:
Call Macro2(Range("H1"), Range("I1"), Range("J1"), Range("K1"), _
Range("L1"), Range("M1"), Range("N1"))

OR
Pass a delimited string stored in 1 location, and modify Macro2 to
parse the string into an array. This might be easier than spreading
the args over several cells.

So...
In say H1: C1,C2,C6,D6:J6,C7:C13,7,4

Then...
Sub Test_Macro2a()
Call Macro2a(Range("H1"))
end Sub

' Macro2a
' Accepts delimited string of range addresses for
' source/target ranges used, and row/col start positions.
' Order by: Input1,Input2,InputResult,
' Input1Source , Input2Source, StartRow, StartCol
Sub Macro2a(TableData As String)
Dim i As Integer, j As Integer
Dim lNextRow As Long, lNextCol As Long
Dim vInput1Vals As Variant, vInput2Vals As Variant
Dim vTableData As Variant

vTableData = Split(TableData, ",")
vInput1Vals = Range(vTableData(3))
vInput2Vals = Range(vTableData(4))

lNextRow = vTableData(5) '//initialize start row
Application.ScreenUpdating = False
For i = 1 To Range(vTableData(3)).Columns.Count
lNextCol = vTableData(6) '//reset start col
For j = 1 To Range(vTableData(4)).Rows.Count
'Set input values and run results macro
Range(vTableData(0)) = vInput1Vals(1, i)
Range(vTableData(1)) = vInput2Vals(j, 1)
Call Macro1
'Put results in table
Cells(lNextRow, lNextCol) = Range(vTableData(2)).Value
lNextCol = lNextCol + 1
Next 'j
lNextRow = lNextRow + 1
Next 'i
Application.ScreenUpdating = True
End Sub

You could even implement an enum to use intellisense in the code so
it's more self-documenting:

In the declarations section of the module:
Enum TblData
Input1
Input2
InputResult
Input1Source
Input2Source
StartRow
StartCol
End Enum

Macro2a modified to use TblData enum:

' Macro2a
' Accepts delimited string of range addresses for
' source/target ranges used, and row/col start positions.
' Order by: Input1,Input2,InputResult,
' Input1Source , Input2Source, StartRow, StartCol
Sub Macro2a(TableData As String)
Dim i As Integer, j As Integer
Dim lNextRow As Long, lNextCol As Long
Dim vInput1Vals As Variant, vInput2Vals As Variant
Dim vTableData As Variant

vTableData = Split(TableData, ",")
vInput1Vals = Range(vTableData(TblData.Input1Source))
vInput2Vals = Range(vTableData(TblData.Input2Source))

lNextRow = vTableData(TblData.StartRow) '//initialize start row
Application.ScreenUpdating = False
For i = 1 To Range(vTableData(TblData.Input1Source)).Columns.Count
lNextCol = vTableData(TblData.StartCol) '//reset start col
For j = 1 To Range(vTableData(TblData.Input2Source)).Rows.Count
'Set input values and run results macro
Range(vTableData(TblData.Input1)) = vInput1Vals(1, i)
Range(vTableData(TblData.Input2)) = vInput2Vals(j, 1)
Call Macro1
'Put results in table
Cells(lNextRow, lNextCol) = _
Range(vTableData(TblData.InputResult)).Value
lNextCol = lNextCol + 1
Next 'j
lNextRow = lNextRow + 1
Next 'i
Application.ScreenUpdating = True
End Sub

 

[DA]

OK, very helpful. Thank you Garry.
DA

 

[GS]

It happens that DA formulated :

OK, very helpful. Thank you Garry.
DA

You're welcome, Dean!