repeat calculation for different combination of input?

[Oligo]

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

 

[Bernie Deitrick]

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]

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]

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]

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]

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]

Sub MakeATable()
Dim myB2Arr As Range
Dim myB4Arr As Range
Dim myB2 As Range
Dim myB4 As Range
Dim myR As Long

myB2Arr = Worksheets("Main Data").Range("A3:A41")
myB4Arr = Worksheets("Main Data").Range("A3:A41")

For Each myB2 In myB2Arr
For Each myB4 In myB4Arr

Range("B2").Value = myB2.Value
Range("B4").Value = myB4.Value

Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myB2.Value
Cells(myR, 9).Value = myB4.Value
Range("G9:G11").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myB4
Next myB2

End Sub


i got a runtime error saying object variable or with block variable not set.
what's wrong?thx

 

[Bernie Deitrick]

My bad: When I converted to ranges from arrays, I forgot to add the Set command.

To fix it, simply change

myB2Arr = Worksheets("Main Data").Range("A3:A41")
myB4Arr = Worksheets("Main Data").Range("A3:A41")

to

Set myB2Arr = Worksheets("Main Data").Range("A3:A41")
Set myB4Arr = Worksheets("Main Data").Range("A3:A41")


HTH,
Bernie
MS Excel MVP

 

[Oligo]

actually my data combinations will be like 2^9 . haha that is too much.
now my problems is how to shift the table??? preferably to another excel
sheet. but how to reference the vba data from other sheet?

 

[Bernie Deitrick]

2^9 is only 512 rows - not big in Excel terms

To shift the table to a new sheet, insert a sheet, name it "New Sheet", and use this code

With Worksheets("New Sheet")
myR = .Cells(Rows.Count, 8).End(xlUp)(2).Row
..Cells(myR, 8).Value = myB2.Value
..Cells(myR, 9).Value = myB4.Value
Range("G9:G11").Copy
..Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
End With


HTH,
Bernie
MS Excel MVP

 

[Oligo]

u mean just add the code into the current code or onto the new sheet??

i got a with without for error.

Sub MakeATable()
Dim myB2Arr As Range
Dim myB4Arr As Range
Dim myB2 As Range
Dim myB4 As Range
Dim myR As Long

Set myB2Arr = Worksheets("Main Data").Range("A3:A4")
Set myB4Arr = Worksheets("Main Data").Range("A3:A4")

For Each myB2 In myB2Arr
For Each myB4 In myB4Arr

Range("B2").Value = myB2.Value
Range("B4").Value = myB4.Value

Application.CalculateFull

With Worksheets("aa")
myR = Cells(Rows.Count, 1).End(xlUp)(2).Row
Cells(myR, 1).Value = myB2.Value
Cells(myR, 2).Value = myB4.Value
Range("G9:G11").Copy
Cells(myR, 3).PasteSpecial xlValues, Transpose:=True
Next myB4
Next myB2
End With
End Sub

 

[Bernie Deitrick]

You have your End With in the wrong place:

Next myB4
Next myB2
End With

should be

End With
Next myB4
Next myB2


Bernie