Multiple arguments used in cell function

[nosense123]

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 ,

[Yongle]

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

[nosense123]

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

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

[mancubus]

copy the formula in C2 to B2.
Change cell reference C1 to A2.
copy the formula to B3:B10

[nosense123]

copy the formula in C2 to B2.
Change cell reference C1 to A2.
copy the formula to B3:B10

Thanks for your reply.
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

[nosense123]

example.xlsx

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

[mancubus]

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

[nosense123]

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

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....

[Yongle]

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)

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

[Paul_Hossler]

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

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

[Paul_Hossler]

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

Capture.JPG

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

[nosense123]

[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

Capture.JPG





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.

[nosense123]

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)

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

Dear Yongle,


Great job!
That is exactly what I needed,
Many thanks!
Stan

[snb]

Or

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