Consulting

Results 1 to 6 of 6

Thread: Solved: permuations

  1. #1
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location

    Solved: permuations

    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

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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

  3. #3
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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)

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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

  5. #5
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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?

  6. #6
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    it is something like this:
    [VBA]
    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
    [/VBA]

    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
    [VBA]
    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
    [/VBA]

    any idea will be great.thx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •