How to resolve Runtime Error 6 Overflow

[BKrukowski]

I am trying to fill an array but keep getting a Runtime Error 6 Overflow: the code that fills the array is :
For I = 0 to count
Alpha(i) = Application.WorksheetFunction.Acos(1 - (Fh(i) / r))
Pw(i) = Alpha(i) * D
Rh(i) = (D / 4) * (1 - Sin(2 * Alpha(i)) / (2 * Alpha(i)))
Af(i) = Rh(i) * Pw(i)
Range("C1").Select
ActiveCell.Offset(rowoffset:=i, columnoffset:=0).Activate
ActiveCell.Value = Alpha(i)
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
ActiveCell.Value = Pw(i)
ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate
ActiveCell.Value = Af(i)
ActiveCell.Offset(rowoffset:=0, columnoffset:=3).Activate
ActiveCell.Value = Rh(i)
Next I

the activecell.values are just for me to see if the array was being filled. Alpha(I) and Pw(I) are filled without any issues but not Rh(I) I am sure it has something to do with using the Sin function but I do not understand why because the array is Dim Rh()As Double and is ReDim later in the code when other parameters are set. I have also tried it As Long, and As Variant. Any help or a work around would be great.

 

[Jonmo1]

Can you post the WHOLE code.
From
Sub SomeName()
al the way to the
End Sub

 

[BKrukowski]

Can you post the WHOLE code.
From
Sub SomeName()
al the way to the
End Sub

Sub FlowDepth()
' Declare Varriables
Dim Q As Double, Qcfs As Double, Qgpm As Double, S As Double, n As Double, D As Double, r As Double
Dim Rh() As Double, Af() As Double, Alpha() As Double, Pw() As Double, Fh() As Double, apoth() As Double
Dim count As Double
Dim msg As String

'Solicit Information
Q = Application.InputBox(Prompt:="Enter the proposed flow rate :", Title:="FLOW RATE", Type:=1)
If MsgBox("Is the flow enter in G.P.M ?", vbYesNo, "UNITS DEFINED") = vbYes Then
Qgpm = Q
Qcfs = Qgpm * 7.48 * 60
Else
Qcfs = Q
Qgpm = Qcfs / (7.48 * 60)
End If
S = Application.InputBox(Prompt:="Enter the slope of the proposed sewer pipe :", Title:="SLOPE", Type:=1)
If MsgBox("Is the slope " & S & " entered as a percent?", vbYesNo, "SLOPE DEFINED") = vbYes Then
Else
S = S / 100
End If
D = Application.InputBox(Prompt:="Enter the pipe diameter in INCHES :", Title:="PIPE DIAMETER", Type:=1)
r = D / 2
n = Application.InputBox(Prompt:="Enter the material roughness :", Title:="MANNINGS -N- VALUE", Type:=1)
If MsgBox(Prompt:="CONSTANTS ASSIGNED" & vbCrLf & " Flow Rate = " & Qcfs & " cfs" & vbCrLf & " Slope of Pipe = " & S & vbCrLf & "Pipe Diameter = " & D & " Inch" & vbCrLf & " Pipe Roughness = " & n) = vbOK Then
End If

'Establish an array of heights
msg = "This application uses an itterative solution." & vbCrLf
msg = msg + "The user must establish a step size." & vbCrLf
msg = msg + "Please enter the height interval you wish to use" & vbCrLf
msg = msg + "for example if you want every tenth enter 0.1 :"
step = Application.InputBox(Prompt:=msg, Title:="Step Size Establishing", Type:=1)
count = D / step
count = Round(count, 0)
If MsgBox(Prompt:="The number of itteration will be " & count, Buttons:=vbOKOnly) = vbOK Then
Else
End If
'Fill and test array of heights
ReDim Fh(0 To count)
ReDim apoth(0 To count)
For i = 0 To count
Range("A1").Select 'For checking
Fh(i) = step * i
apoth(i) = D - Fh(i)
ActiveCell.Offset(rowoffset:=i, columnoffset:=0).Activate ' print Fh to verify
ActiveCell.Value = Fh(i)
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
ActiveCell.Value = apoth(i)
Next i
i = 0
ReDim Alpha(0 To count)
ReDim Pw(0 To count)
ReDim Af(0 To count)
ReDim Rh(0 To count)
For i = 0 To count
Alpha(i) = Application.WorksheetFunction.Acos(1 - (Fh(i) / r))
Pw(i) = Alpha(i) * D
Rh(0) = (D / 4) * (1 - Sin(2 * Alpha(i)) / (2 * Alpha(i)))
Af(i) = Rh(i) * Pw(i)
Range("C1").Select
ActiveCell.Offset(rowoffset:=i, columnoffset:=0).Activate
ActiveCell.Value = Alpha(i)
Next i

End Sub

 

[Jonmo1]

It looks like this bit is causing a #Div/0! Error when i = 0
Rh(0) = (D / 4) * (1 - Sin(2 * Alpha(i)) / (2 * Alpha(i)))

When i = 0, Alpha(i) also = 0
so it becomes
Rh(0) = (D / 4) * (1 - Sin(2 * 0) / (2 * 0))
2*0 = 0
Rh(0) = (D / 4) * (1 - Sin(0) / 0)
Sin(0) = 0
Rh(0) = (D / 4) * (1 - 0 / 0)

0/0 = #Div/0!


Try changing that up so that i goes from 1 to count and redimming as 1 to count as well, instead of 0 to count.

 

[BKrukowski]

Jonmo1 thank you so much for your help this works like a charm now.

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[Guilherme Silva]

HI Guys, I´m also have this error in my code. Could you help me to fix it?

Public Sub FindText()


Dim ws As Worksheet, Found As Range
Dim myText As String, firstAddress As String
Dim AddressStr As String, foundNum As Integer
Dim MyData, myBottom As String




myText = InputBox("Digite o texto para procurar!")


If myText = " " Then Exit Sub




MyData = "None"
myBottom = Worksheets(1).Range("I:I").End(xlUp).Row



With Worksheets(1).Range("I1:I200" & myBottom)

End With

For Each ws In ThisWorkbook.Worksheets

With ws

If ws.Name = " " Then GoTo myNext


Set Found = .Range("I1:I200" & myBottom).Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)


If Not Found Is Nothing Then
firstAddress = Found.Address


Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf

Set Found = .Range("I1:I200" & myBottom).FindNext(Found)


Loop While Not Found Is Nothing And Found.Address = firstAddress


End If


myNext:
End With


Next ws


If Len(AddressStr) Then
MsgBox "Encontrado: """ & myText & """ " & foundNum & " vezes nas tabelas:" & vbCr & _
AddressStr, vbOKOnly, myText & " Encontrados nas tabelas"


Else:


MsgBox "Não foi possível encontrar " & myText & " nesta tabela.", vbExclamation


End If


End Sub