Printable View
Hi Guys,
Supose i have range of data in a A3:A10 and I want this to be used as an arguments into calculator that requires input into cell, say B2 and returns value in C3.
I would like to get all values returned for arguments in a3:a10 in b3:b10.
Any suggestions ??
Thanks ,
Your question is a little bit vague.
So how about giving us some idea of what you are trying to achieve, using a proper example. Something like:
Column A contains Number of items
Column B contains Price of each item
Column C = Number of items X Price
Quote:
Originally Posted by Yongle
As said before:
I have a random numbers in a range a2:a10;
I have a function that requires manual input into cell (argument) c1, and returns value in anoher cell- c2.
What I want to achieve is to do macro that will use all numbers from a2:a10 one by one, and place it one by one into c1 and then return value in c2. I want thi to be done for all arguments from a2:a10 and this retuned values to apear in a range b2:b10.
Is hat clear now?
cheers
copy the formula in C2 to B2.
Change cell reference C1 to A2.
copy the formula to B3:B10
Thanks for your reply.Quote:
Originally Posted by mancubus
The trouble is my 'formula' in C2 is part of an array (matrix) and it cannot be used in suggested by you way. This is a calculator rather than one cell formula. And i want to use this calculator with a range of values and return range of values in te form i mentioned i my quote.
thanks
Attachment 13468
I did attach an example I am trying to workout. In cell A23 there is an input value.
The calculator calculates several different properties. I am interested in a value from an array in cell T7.
And now I want to get this values from T7 for each of the arguments in a41:a100
and to return them in a range c41:c100
Any ideas for approach?
Thanks
welcome.
you mean;
copy all value in A41:A100 one by one to A23,
recalculate the formulas (if calculation mode is set to automatic excel will do)
copy the value in T7 to C41:C100
???
i'm sure, being the first poster, youngle will provide you a solution.
just a reminder: current value of A23 in example file is 55.00 whereas values in A41:A100 are between 0.05 and 3.15
Quote:
Originally Posted by mancubus
Unfortunately it does not work like that. T7 result is based on an input in a23 and this is complex calculations involving several operations on matrices. I cannot assign this calculations like for one cell formula and drag it down to apply to a range.
The macro will be needed here and thats what I am asking for....
This is what you are aiming for I think
Given the names of the various tables etc, I could not resist naming the macro in this way - LOL
Basically what the vba does is put each value from column A in the "range" (in turn) into cell A23 and then copies the calculated value each time from T7 and places it in column C of the "range."
NOTE - this will only work if you have calculation set to automatic. Can add 2 lines to vba (to switch it on and then off again)
Workbook incl macro attached. (Values left blank in column C)
Code:Sub Tabulate_Stiffness_By_Degree()
Dim lr As Long
Dim Range01 As Range
With Sheets("Sheet1")
'determine the lasr row in range to be tabulated
'assumes there are no empty cells in column A in the range required
lr = .Range("A41").End(xlDown).Row
'sets the range for which values are to be tabulated
Set Range01 = .Range("A41:A" & lr)
'and for each cell in that range
For Each r In Range01
'put the value of that cell into cell A23
Range("A23").Value = r.Value
'then take the (newly-calculated) value from T7 and copy that to correct row in column C
Range("C" & r.Row).Value = .Range("T7").Value
Next
End With
End Sub
If I didn't need any of the intermediate matrices I'd make a User Defined Function and just pass the barest number of parameters and do all the heavy lifting internal to the UDF
This isn't finished but you'd just use
=StiffnessMatrixOrient(151.7, 10.3, 0.25, 3.6, 55)
or
StiffnessMatrixOrient(a7, a8, a9, a11, a23)
in the worksheet
I couldn't figure out if you were returning a single number or a matrix, but the UDF could do either
Code:Option Explicit
Sub drv()
Dim v3x3 As Variant
v3x3 = StiffnessMatrixOrient(151.7, 10.3, 0.25, 3.6, 55)
End Sub
Function StiffnessMatrixOrient(E1 As Double, E2 As Double, V12 As Double, G12 As Double, OrientationDegrees As Double) As Variant
Const cPI As Double = 3.14159265358979
Dim T(1 To 3, 1 To 3) As Double, T_Inverse As Variant, T_Transpose As Variant
Dim R(1 To 3, 1 To 3) As Double, R_Inverse As Variant, R_Transpose As Variant
Dim C(1 To 3, 1 To 3) As Double, S(1 To 3, 1 To 3) As Double
Dim SinRad As Double, CosRad As Double, v21 As Double
SinRad = Sin(OrientationDegrees * cPI / 180#)
CosRad = Cos(OrientationDegrees * cPI / 180#)
'T matrix
T(1, 1) = CosRad ^ 2
T(1, 2) = SinRad ^ 2
T(1, 3) = 2 * SinRad * CosRad
T(2, 1) = SinRad ^ 2
T(2, 2) = CosRad ^ 2
T(2, 3) = -2 * SinRad * CosRad
T(3, 1) = -SinRad * CosRad
T(3, 2) = SinRad * CosRad
T(3, 3) = CosRad ^ 2 - SinRad ^ 2
T_Inverse = Application.WorksheetFunction.MInverse(T())
T_Transpose = Application.WorksheetFunction.Transpose(T_Inverse)
'R matrix
R(1, 1) = 1#
R(1, 2) = 0#
R(1, 3) = 0#
R(2, 1) = 0#
R(2, 2) = 1#
R(2, 3) = 0#
R(3, 1) = 0#
R(3, 2) = 0#
R(3, 3) = 2#
R_Inverse = Application.WorksheetFunction.MInverse(R())
R_Transpose = Application.WorksheetFunction.Transpose(R_Inverse)
'C matrix
v21 = (E2 / E1) * V12
C(1, 1) = E1 / (1 - V12 * v21)
C(1, 2) = V12 * E2 / (1 - V12 * v21)
C(1, 3) = 0#
C(2, 1) = v21 * E1 / (1 - V12 * v21)
C(2, 2) = E2 / (1 - V12 * v21)
C(2, 3) = 0#
C(3, 1) = 0#
C(3, 2) = 0#
C(3, 3) = G12
Stop ' to check
End Function
Don't know if you're still interested in this, but it was a fun exercise
Materials is not my field, but Google to the rescue
I didn't see how to take the Stiffness and the Compliance matrices and get to a single number. Explain that and we could expand this
I made a UDF out of the inputs and made it return two 3x3 matrices
Attachment 13506
Code:Option Explicit
'http://www.efunda.com/formulae/solid_mechanics/composites/calc_ufrp_cs_arbitrary.cfm
' E1 = Young 's Modulus along Fibers
' E2 = Young 's Modulus Normal to Fibers
' V12 = Poisson 's Ratio
' G12 = Shear Modulus
Function Stiffness_Compliance_Orient(OrientationDegrees As Double, E1 As Double, E2 As Double, V12 As Double, G12 As Double) As Variant
Const cPI As Double = 3.14159265358979
Dim T(1 To 3, 1 To 3) As Double, T_Inverse As Variant, T_Transpose As Variant
Dim R(1 To 3, 1 To 3) As Double, R_Inverse As Variant
Dim Stiffness(1 To 3, 1 To 3) As Double, Compliance(1 To 3, 1 To 3) As Double
Dim C_Bar As Variant, S_Bar As Variant
Dim i As Long, j As Long
Dim Answer(1 To 3, 1 To 6) As Double
Dim SinRad As Double, CosRad As Double, v21 As Double
SinRad = Sin(OrientationDegrees * cPI / 180#)
CosRad = Cos(OrientationDegrees * cPI / 180#)
'coordinate transformation matrix [T]
T(1, 1) = CosRad ^ 2
T(1, 2) = SinRad ^ 2
T(1, 3) = 2 * SinRad * CosRad
T(2, 1) = SinRad ^ 2
T(2, 2) = CosRad ^ 2
T(2, 3) = -2 * SinRad * CosRad
T(3, 1) = -SinRad * CosRad
T(3, 2) = SinRad * CosRad
T(3, 3) = CosRad ^ 2 - SinRad ^ 2
T_Inverse = Application.WorksheetFunction.MInverse(T)
T_Transpose = Application.WorksheetFunction.Transpose(T_Inverse)
'engineering-tensor interchange matrix [R]
R(1, 1) = 1#
R(1, 2) = 0#
R(1, 3) = 0#
R(2, 1) = 0#
R(2, 2) = 1#
R(2, 3) = 0#
R(3, 1) = 0#
R(3, 2) = 0#
R(3, 3) = 2#
R_Inverse = Application.WorksheetFunction.MInverse(R)
'stiffness matrix [C]
v21 = (E2 / E1) * V12
Stiffness(1, 1) = E1 / (1 - V12 * v21)
Stiffness(1, 2) = V12 * E2 / (1 - V12 * v21)
Stiffness(1, 3) = 0#
Stiffness(2, 1) = v21 * E1 / (1 - V12 * v21)
Stiffness(2, 2) = E2 / (1 - V12 * v21)
Stiffness(2, 3) = 0#
Stiffness(3, 1) = 0#
Stiffness(3, 2) = 0#
Stiffness(3, 3) = G12
'compliance matrix [S]
Compliance(1, 1) = 1 / E1
Compliance(1, 2) = -V12 / E1
Compliance(1, 3) = 0#
Compliance(2, 1) = -v21 / E2
Compliance(2, 2) = 1 / E2
Compliance(2, 3) = 0#
Compliance(3, 1) = 0#
Compliance(3, 2) = 0#
Compliance(3, 3) = 1 / G12
'The stiffness matrix for a lamina of arbitry orient can be expressed in terms of the stiffness matrix in the principal direction
'C_Bar
C_Bar = Application.WorksheetFunction.MMult(T_Inverse, Stiffness)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, R)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, T)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, R_Inverse)
'The complicance matrix for a lamina of arbitry orient can be obtained in a similar manner.
'S_Bar
S_Bar = Application.WorksheetFunction.MInverse(C_Bar)
'put in answer
For i = 1 To 3
For j = 1 To 3
Answer(i, j) = C_Bar(i, j)
Answer(i, j + 3) = S_Bar(i, j)
Next j
Next i
Stiffness_Compliance_Orient = Answer
End Function
[QUOTE=Paul_Hossler;325128]Don't know if you're still interested in this, but it was a fun exercise
Materials is not my field, but Google to the rescue
I didn't see how to take the Stiffness and the Compliance matrices and get to a single number. Explain that and we could expand this
I made a UDF out of the inputs and made it return two 3x3 matrices
Attachment 13506
Paul,
You did a great job on this. Answering your qustion about stiffness corresponding to orientation angle it would be:
E1 - value from cell E2
E2 - value from cell F3.
Dear Yongle,Quote:
Originally Posted by Yongle
Great job!
That is exactly what I needed,
Many thanks!
Stan
Or
Code:Sub Tabulate_Stiffness_By_Degree()
With Sheets("Sheet1")
sn=.cells(41,1).currentregion.resize(,3)
for j=2 to ubound(sn)
.cells(23,1)=sn(j,1)
sn(j,3)=.cells(7,20)
next
.cells(41,1).currentregion.resize(,3)
end with
End Sub