Overflow Error

[krishna334]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have 2 sheet in my workbook – MASTER & PM Manual Override<o></o>
There are some headings in 5<SUP>th</SUP> row of PM Manual Override.<o></o>
The same heading (in different sequence) are present in MASTER tab & also there is data below these headings.<o></o>
What my code does is – copies the data under the heading of the MASTER tab to the row below the same heading in the PM Manual Override tab<o></o>
<o> </o>
The problem is that now I am getting an error – “overflow” when I run the below code. Why this happens & how do i correct it?
<o> </o>
Option Base 1<o></o>
Sub rearrange()<o></o>
Dim i, j, m, n, p As Integer<o></o>
Dim FindString As String<o></o>
Dim Rng As Range<o></o>
<o> </o>
Dim Arr(100) As String<o></o>
i = 1<o></o>
j = Sheets("PM Manual Override").UsedRange.Columns.Count<o></o>
<o> </o>
For i = 1 To j<o></o>
Arr(i) = Sheets("PM Manual Override").Cells(5, i).Value<o></o>
FindString = Arr(i)<o></o>
If Trim(FindString) <> "" Then<o></o>
With Sheets("MASTER").Cells<o></o>
Set Rng = .Find(What:=FindString, _<o></o>
After:=.Cells(.Cells.Count), _<o></o>
LookIn:=xlValues, _<o></o>
LookAt:=xlWhole, _<o></o>
SearchOrder:=xlByRows, _<o></o>
SearchDirection:=xlNext, _<o></o>
MatchCase:=False)<o></o>
If Not Rng Is Nothing Then<o></o>
Application.Goto Rng, True<o></o>
m = ActiveCell.Row<o></o>
n = Sheets("MASTER").UsedRange.Rows.Count<o></o>
p = ActiveCell.Column<o></o>
<o></o>
Range(Cells(m + 1, p), Cells(n, p)).Copy Destination:=Sheets("PM Manual Override").Cells(6, i)<o></o>
<o> </o>
Else<o></o>
<o></o>
<o></o>
End If<o></o>
End With<o></o>
End If<o></o>
Next i<o></o>
<o> </o>
<o> </o>
Sheets("MASTER").Range("AO3:BL3").Copy Destination:=Sheets("PM Manual Override").Range("AD4")<o></o>
Sheets("MASTER").Range("BP5:BP" & n).Copy Destination:=Sheets("PM Manual Override").Range("A6")<o></o>
<o> </o>
<o> </o>
<o> </o>
End Sub<o></o>

 

[VoG]

You need to set the data type of each variable and try Long instead of Integer

Dim i As Long, j As Long, m As Long, n As Long, p As Long

 

[krishna334]

Hi Vog,

Thanks for replying.
I changed them to long but still getting the same error

 

[VoG]

Which line of code causes the error?

 

[krishna334]

This one Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

 

[Firefly2012]

Are you using xl2007 or above? Change the Count to CountLarge:

Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.CountLarge), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

 

[krishna334]

Thank you Firefly ..that did the trick ...but what countlarge does?


i am using 2007 excel

 

[Firefly2012]

The problem is that in xl2007+ the number of cells on a worksheet (non-compatibility mode) exceeds the maximum size of a Long integer variable (Cout property returns a Long integer data type). Hence you need to use the typesafe CountLarge property which can accomodate the number of cells.