let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx

Oligo,

First, move A1:A2 down to A2:A3, and then move A3 to B2. Enter your formula in F2 and G2, not
F1:F2.When you reference B1, use $B$1, C1 use $C$1, D1 use $D$1. When you reference A2 and B2, do
not use the $ form.

Enter all the combinations of values from A2:B2 into the cells below A3 top Awhatever, and
B3:BWhatever, then copy the formulas from F2:G2 and copy down to match.

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx

i get what u mean. but because A1 and A2 are just 2 selection and excel will
lookup relevant geomeotric datas for calculation. after selecting the drop
down A1 and A2, there are a lot of calculation and interpolation involve. in
addition, those input BCD
will be change after a set of combination A1 & A2 output is done,
thus,continue to record the next set of output for a change in BCD.

what i have in mind is whether any macro recording that can be applied here,
since i just need to repeat the selection of A1 or A2 but at the same time
record the output in a table form.

"Bernie Deitrick" wrote:

Oligo,

First, move A1:A2 down to A2:A3, and then move A3 to B2. Enter your formula in F2 and G2, not
F1:F2.When you reference B1, use $B$1, C1 use $C$1, D1 use $D$1. When you reference A2 and B2, do
not use the $ form.

Enter all the combinations of values from A2:B2 into the cells below A3 top Awhatever, and
B3:BWhatever, then copy the formulas from F2:G2 and copy down to match.

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx

Something like this (but change the Arrays to the actual values). The sub will make the table in
columns H through K.

Sub MakeATable()
Dim myA1Arr As Variant
Dim myA2Arr As Variant
Dim myA1 As Variant
Dim myA2 As Variant
Dim myR As Long

myA1Arr = Array(1, 2, 3, 4)
myA2Arr = Array("Test", "Value")

For Each myA1 In myA1Arr
For Each myA2 In myA2Arr
Range("A1").Value = myA1
Range("A2").Value = myA2
Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myA1
Cells(myR, 9).Value = myA2
Range("F1:F2").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myA2
Next myA1
End Sub



HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
i get what u mean. but because A1 and A2 are just 2 selection and excel will
lookup relevant geomeotric datas for calculation. after selecting the drop
down A1 and A2, there are a lot of calculation and interpolation involve. in
addition, those input BCD
will be change after a set of combination A1 & A2 output is done,
thus,continue to record the next set of output for a change in BCD.

what i have in mind is whether any macro recording that can be applied here,
since i just need to repeat the selection of A1 or A2 but at the same time
record the output in a table form.

"Bernie Deitrick" wrote:

Oligo,

First, move A1:A2 down to A2:A3, and then move A3 to B2. Enter your formula in F2 and G2, not
F1:F2.When you reference B1, use $B$1, C1 use $C$1, D1 use $D$1. When you reference A2 and B2,
do
not use the $ form.

Enter all the combinations of values from A2:B2 into the cells below A3 top Awhatever, and
B3:BWhatever, then copy the formulas from F2:G2 and copy down to match.

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx

can i know what is the meaning of these 2 statement?
myA1Arr = Array(1, 2, 3, 4)
myA2Arr = Array("Test", "Value")

array is the range of data for the A1 drop down menu (ie. "A1:A39")? what if
it's on another excel sheet? what about array for A2?


"Bernie Deitrick" wrote:

Something like this (but change the Arrays to the actual values). The sub will make the table in
columns H through K.

Sub MakeATable()
Dim myA1Arr As Variant
Dim myA2Arr As Variant
Dim myA1 As Variant
Dim myA2 As Variant
Dim myR As Long

myA1Arr = Array(1, 2, 3, 4)
myA2Arr = Array("Test", "Value")

For Each myA1 In myA1Arr
For Each myA2 In myA2Arr
Range("A1").Value = myA1
Range("A2").Value = myA2
Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myA1
Cells(myR, 9).Value = myA2
Range("F1:F2").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myA2
Next myA1
End Sub



HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
i get what u mean. but because A1 and A2 are just 2 selection and excel will
lookup relevant geomeotric datas for calculation. after selecting the drop
down A1 and A2, there are a lot of calculation and interpolation involve. in
addition, those input BCD
will be change after a set of combination A1 & A2 output is done,
thus,continue to record the next set of output for a change in BCD.

what i have in mind is whether any macro recording that can be applied here,
since i just need to repeat the selection of A1 or A2 but at the same time
record the output in a table form.

"Bernie Deitrick" wrote:

Oligo,

First, move A1:A2 down to A2:A3, and then move A3 to B2. Enter your formula in F2 and G2, not
F1:F2.When you reference B1, use $B$1, C1 use $C$1, D1 use $D$1. When you reference A2 and B2,
do
not use the $ form.

Enter all the combinations of values from A2:B2 into the cells below A3 top Awhatever, and
B3:BWhatever, then copy the formulas from F2:G2 and copy down to match.

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx

IF the source of the data in A1 and A2 are on another sheet, you can loop through those values like
this

Sub MakeATable()
Dim myA1Arr As Range
Dim myA2Arr As Range
Dim myA1 As Range
Dim myA2 As Ramge
Dim myR As Long

myA1Arr = Worksheets("OtherSheet").Range("A1:A39")
myA2Arr = Worksheets("OtherSheet").Range("B1:B39")

For Each myA1 In myA1Arr
For Each myA2 In myA2Arr
Range("A1").Value = myA1.Value
Range("A2").Value = myA2.Value
Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myA1.Value
Cells(myR, 9).Value = myA2.Value
Range("F1:F2").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myA2
Next myA1
End Sub

This will result in a table that is 39x39 (or about 1,521) rows long

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
can i know what is the meaning of these 2 statement?
myA1Arr = Array(1, 2, 3, 4)
myA2Arr = Array("Test", "Value")

array is the range of data for the A1 drop down menu (ie. "A1:A39")? what if
it's on another excel sheet? what about array for A2?


"Bernie Deitrick" wrote:

Something like this (but change the Arrays to the actual values). The sub will make the table in
columns H through K.

Sub MakeATable()
Dim myA1Arr As Variant
Dim myA2Arr As Variant
Dim myA1 As Variant
Dim myA2 As Variant
Dim myR As Long

myA1Arr = Array(1, 2, 3, 4)
myA2Arr = Array("Test", "Value")

For Each myA1 In myA1Arr
For Each myA2 In myA2Arr
Range("A1").Value = myA1
Range("A2").Value = myA2
Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myA1
Cells(myR, 9).Value = myA2
Range("F1:F2").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myA2
Next myA1
End Sub



HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
i get what u mean. but because A1 and A2 are just 2 selection and excel will
lookup relevant geomeotric datas for calculation. after selecting the drop
down A1 and A2, there are a lot of calculation and interpolation involve. in
addition, those input BCD
will be change after a set of combination A1 & A2 output is done,
thus,continue to record the next set of output for a change in BCD.

what i have in mind is whether any macro recording that can be applied here,
since i just need to repeat the selection of A1 or A2 but at the same time
record the output in a table form.

"Bernie Deitrick" wrote:

Oligo,

First, move A1:A2 down to A2:A3, and then move A3 to B2. Enter your formula in F2 and G2, not
F1:F2.When you reference B1, use $B$1, C1 use $C$1, D1 use $D$1. When you reference A2 and
B2,
do
not use the $ form.

Enter all the combinations of values from A2:B2 into the cells below A3 top Awhatever, and
B3:BWhatever, then copy the formulas from F2:G2 and copy down to match.

HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
let's say i have this excel calculation.

cell A1 air surface (drop down menu)
cell A2 water surface (drop down menu)

Cell B1,C1,D1 are all input value to calculate my output heat load and
pressure drop.

Cell F1 Heat load=
Cell F2 Pressure drop=

my question is how do i tabulate the set of F1 and F2 results into a table
for different set of A1 and A2 combination provided all the b1,c1,d1 stay
constant.
A1 and A2 are drop down menu containing diff type of surfaces.
if my question is not clear do prompt me. thx