PDA

View Full Version : Solved: permuations



white_flag
09-08-2010, 06:25 AM
hello .. I neeed your help (again). I have to make an calculation of the surface and I have the following dates
Diameter (columns)
Thicknesses (rows)
then, the calculation need to be drop in an excel range, I think it is something like this? or I am far away from VBA reality

'array dia.
Dia(1) = "21"
Dia(2) = "28"
Dia(3) = "34"
.
.
etc
'array tk
tk(1) = "1.2"
tk(2) = "1.4"
tk(3) = "1.8"
.
etc

Formula(1) = Dia(1)*2*tk(1)*Pi()
Formula(2) = Dia(1)*2*tk(2)*Pi()
Formula(3) = Dia(1)*2*tk(3)*Pi()
.
.
.
Formula(n) = Dia(3)*2*tk(3)*Pi()

how can I approach this subject? thx for the advises
ps. I do not have an data sheet for example ..and the correct title will be: combination

GTO
09-08-2010, 06:57 AM
Hi there,

What do you mean by the "calculation of the surface?" Given that you are showing a diameter and also showing 'thickness' or height, are we dealing with a cylinder?

If that is correct, are we looking at returning the surface area of the cylinder walls, or that number added to the surface areas of the top and bottom of the cylinder.

Hope I am not terribly mis-reading?

Mark

white_flag
09-08-2010, 07:10 AM
hi, Mark ..thx for reading. surface is:the length of the interior pipe + thicknesses of the pipe walls: the table look like this:
______________Diameter:_18_21_34_48____etc (mm)
Thicknesses(mm):1.2
_______________1.4____________DATA
_______________1.6

than in interior (of the table) I have some values that will be taked and add to "surface" via index and match function (but this is another story). For me (now) I do not know, how to wright an formula that will calculate the surface (mē) via VBA.

Did you understand something? (because my English is not so good)

GTO
09-08-2010, 08:27 AM
I am sorry, but I am not understanding. I do not understand where the pipe's wall thickness would come into play.

By example, if we had a pipe:


Outside Diameter: 10
Length: 50


Then I believe take the result of Pi * 10 and multiply this by the length.

What am I missing?

Mark

white_flag
09-08-2010, 09:15 AM
no problem
the length it is basically 1 m (unitary), then, the surface of the pipe is pi()*interior_Dia*2*thiknesses >> mē=m(lenght)*m(circle surface=pi()...etc)

anyway: then the formula will become:

Formula(1) = Dia(1)*2*tk(1)*Pi() 'note the meter for the lenght is not there because it is 1. so this formula need to have

Dia(x) - but I do not know what type of variable can be
TK(x) - the same

Formula(x) = Dia(x)*2*tk(x)*Pi()

Formula(x)-loop will be drooped in an range:
Sheet1.Range("C2:W10") = Formula(x)

in the end, the table will look like this
___________Diameters(mm) ___________21____________48__________etc
Thickness(mm) 1.2________mē=pi()*21*2*1.2__mē=pi()*48*2*1.2_____
_____________1.6________mē=pi()*21*2*1.6__mē=pi()*48*2*1.6_____
_____________1.8________mē=pi()*21*2*1.8__mē=pi()*48*2*1.8_____

better like this?

white_flag
09-09-2010, 12:24 AM
it is something like this:

Sub Surfaces()
Dim rTopLeft As Range
Dim Dia(1 To 4) As Double
Dim tk(1 To 3) As Double

Set rTopLeft = Worksheets("Sheet1").Range("B1")

'array dia.
Dia(1) = 21
Dia(2) = 28
Dia(3) = 34
Dia(4) = 40

'array tk
tk(1) = 1.2
tk(2) = 1.4
tk(3) = 1.8

'Write the table
With rTopLeft
.Offset(0, 1).Resize(1, UBound(Dia)).Value = Dia
.Offset(1, 0).Resize(UBound(tk), 1).Value = Application.Transpose(tk)
With .Offset(1, 1).Resize(UBound(tk), UBound(Dia))
.FormulaR1C1 = "=2*PI()/1000*r" & rTopLeft.Row & "c*rc" & rTopLeft.Column
.Value = .Value
.NumberFormat = "0.00"
End With
End With

End Sub


but now, I do not know how to sum some dates from an text_boxes to the previews code

code to add and to be summed

Private Sub CommandButton1_Click()
Dim Formula As Double
Dim i As Long

For i = 31 To 40
If Me.Controls("CheckBox" & i - 30) = True Then
If Me.Controls("TextBox" & i).Text = "" Then Me.Controls("TextBox" & i).Text = 0
Formula = Formula + CDbl(Me.Controls("TextBox" & i).Text)
End If
Next

Sheet1.Range("C2:W10").Value = Formula
End Sub


any idea will be great.thx