How to resolve Runtime Error 6 Overflow

BKrukowski

Board Regular
Joined
May 6, 2009
Messages
88

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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Can you post the WHOLE code.
From
Sub SomeName()
al the way to the
End Sub
 

BKrukowski

Board Regular
Joined
May 6, 2009
Messages
88
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
May 6, 2009
Messages
88

ADVERTISEMENT

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

Guilherme Silva

New Member
Joined
May 17, 2018
Messages
1
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,254
Messages
5,769,055
Members
425,513
Latest member
9375021758

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
Top