runtime error 6 overflow when running a macro

hi. im trying to run a macro and it works perfectly till about row 30,197
then it crashes and gives a runtime 6 error (overflow).

Dim g As Double


For g = b To Sheet1.Rows.Count
If UCase(Trim(Sheet1.Cells(g, nSecondDefnColBegin).Value)) =
firstcell Then
nSecondDefnRow = g
Let b = g
Exit For
End If

Next g

the error happens in the row nSecondDefnRow = g. Any reasons why this
happens? and how can i fix the problem? Thanks.

Kim

Did you declare nSecondDefnRow as Integer or Long.

Integer only goes up to 32,767.

Is there a reason to process all 65536 rows?

Maybe you can find the last row (based on the stuff in column A)???

dim LastRow as long

with sheet1
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

for g = b to lastrow
....



Kim wrote:
>
> hi. im trying to run a macro and it works perfectly till about row 30,197
> then it crashes and gives a runtime 6 error (overflow).
>
> Dim g As Double
>
>
> For g = b To Sheet1.Rows.Count
> If UCase(Trim(Sheet1.Cells(g, nSecondDefnColBegin).Value)) =
> firstcell Then
> nSecondDefnRow = g
> Let b = g
> Exit For
> End If
>
> Next g
>
> the error happens in the row nSecondDefnRow = g. Any reasons why this
> happens? and how can i fix the problem? Thanks.
>
> Kim

--

Dave Peterson

Try change g to data type long instead of Double
--
Tony Green


"Kim" wrote:

> hi. im trying to run a macro and it works perfectly till about row 30,197
> then it crashes and gives a runtime 6 error (overflow).
>
> Dim g As Double
>
>
> For g = b To Sheet1.Rows.Count
> If UCase(Trim(Sheet1.Cells(g, nSecondDefnColBegin).Value)) =
> firstcell Then
> nSecondDefnRow = g
> Let b = g
> Exit For
> End If
>
> Next g
>
> the error happens in the row nSecondDefnRow = g. Any reasons why this
> happens? and how can i fix the problem? Thanks.
>
> Kim

Kim,

How have you declare your other variables. If you declared one as
Integer, change it to Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Kim" <[email protected]> wrote in message
news:[email protected]...
> hi. im trying to run a macro and it works perfectly till about
> row 30,197
> then it crashes and gives a runtime 6 error (overflow).
>
> Dim g As Double
>
>
> For g = b To Sheet1.Rows.Count
> If UCase(Trim(Sheet1.Cells(g,
> nSecondDefnColBegin).Value)) =
> firstcell Then
> nSecondDefnRow = g
> Let b = g
> Exit For
> End If
>
> Next g
>
> the error happens in the row nSecondDefnRow = g. Any reasons
> why this
> happens? and how can i fix the problem? Thanks.
>
> Kim

Tried that. didn't solve the error.

"ADG" wrote:

> Try change g to data type long instead of Double
> --
> Tony Green
>
>
> "Kim" wrote:
>
> > hi. im trying to run a macro and it works perfectly till about row 30,197
> > then it crashes and gives a runtime 6 error (overflow).
> >
> > Dim g As Double
> >
> >
> > For g = b To Sheet1.Rows.Count
> > If UCase(Trim(Sheet1.Cells(g, nSecondDefnColBegin).Value)) =
> > firstcell Then
> > nSecondDefnRow = g
> > Let b = g
> > Exit For
> > End If
> >
> > Next g
> >
> > the error happens in the row nSecondDefnRow = g. Any reasons why this
> > happens? and how can i fix the problem? Thanks.
> >
> > Kim