Create a VBA formula with a "do until" function

TSpan

New Member
Joined
Jan 26, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello guys,

I am attempting to calculate the length and qty of horizontal lines on a triangle that are a set distance apart from the bottom. (Wall battens in the gable of a shed) I can use this formula =((Ridge-Eave)-Spacing)/TAN(RADIANS(AngleOfRoof)) but then i need to duplicate that formula this many times ROUNDDOWN((Ridge-Eave)/Spacing,0)

Can this be done in a conventional formula or will it need to be a custom VBA formula in this format?

VBA Code:
Public Function ListItemsPrice(Quant As Range, Price As Range)
Dim Qs As New Collection, Ds As New Collection, Ps As New Collection
Dim c As Range, mTotal As Currency

For Each c In Quant
Qs.Add c
Next

For Each c In Price
Ps.Add c
Next


For i = 1 To Qs.Count
If Not Qs(i) = Empty Then
    mTotal = mTotal + (Ps(i) * Qs(i))
End If
Next i

ListItemsPrice = mTotal

'just incase selcted ranges are not equal in length
If Not Price.Count = Quant.Count Then
    ListItemsPrice = "Range Selection Error"
End If

End Function

If it has to be done in VBA what will it look like...? Im only new to it and have found the above code in a workbook i am using! :)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If anyone else is looking for this i worked it out this way...
VBA Code:
Public Function GableGirts(Height, Spacing, Angle)

Set Func = Application.WorksheetFunction
Const pi = 3.14159265358979

Gqty = Func.RoundDown(Height / Spacing, 0)
gLength = ((Height - Spacing) / Tan((Angle * pi / 180))) * 2
If Gqty < 1 Then
    gLength = 0
    Exit Function
End If
For i = 1 To Gqty
    Height = Height - Spacing
    If Height - Spacing <= 0 Then
    GableGirts = gLength
    Exit Function
    End If
    gLength = gLength + ((Height - Spacing) / Tan((Angle * pi / 180)) * 2)
Next i

GableGirts = gLength

End Function
 
Upvote 0
Solution

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top