Thread: Row limit

I hope someone can help me better understand this. I have a macro that I have created (as shown below). That is fairly simple it takes Data from one sheet (Input) uses a look up to find matches in common with another sheet (black box) and inputs it into the other sheet (Output).

Right now in my black box I have a list of manufacturing part numbers by a specific Vendor. This is about 26K rows.

My question is I would love to be able to add all vendors MFPNs into the blackbox worksheet at about 220K rows, and have this filter againts my data. I'm using MS Excel 2007. Every time I try increasing the "Dim ArrBB(30000) As String" by much more than 30K (with data put in) it tells me I have Run-time error '6': Overflow.

Is it just too much data to even be possible? an example Manufacturing Part Number that i deal with would look something like: AMA3200BEX5AP. There are smaller MFPNs as well as Larger one. This is probably more in the larger end.







Sub Button1_Click()
UserForm1.Label1.Width = 0
UserForm1.Show
End Sub
Sub Macro1()
'
' Macro1 Macro
'
Application.ScreenUpdating = False
Sheets("Input").Select
Range("C2").Select
If ActiveCell.Value <> "" Then
If ActiveCell.Offset(0, 2) <> "" And ActiveCell.Offset(0, 3) <> "" Then
Sheets("BlackBox").Select
Range("A2").Select
Dim ArrBB(30000) As String
Dim BBCount As Integer

'populate array with values
For BBCount = 2 To ActiveSheet.UsedRange.Rows.count + 1
If ActiveCell.Value = "" Then
Exit For
End If
Range("A" & BBCount).Select
ArrBB(BBCount) = ActiveCell.Value
Next BBCount

'loop through input and check each against bb array
Dim InputCount As Integer
Dim Loopcount As Integer
Dim place As Integer

Sheets("Input").Select
place = 2

For InputCount = 2 To ActiveSheet.UsedRange.Rows.count
Range("C" & InputCount).Select
For Loopcount = 1 To BBCount
If ActiveCell.Value = ArrBB(Loopcount) And ActiveCell.Value <> "" Then
Worksheets("Output").Range("A" & place) = ActiveCell.Offset(0, 0).Value
Worksheets("Output").Range("B" & place) = ActiveCell.Offset(0, 1).Value
Worksheets("Output").Range("C" & place) = ActiveCell.Offset(0, 2).Value
Worksheets("Output").Range("D" & place) = ActiveCell.Offset(0, 3).Value
Worksheets("Output").Range("E" & place) = ActiveCell.Offset(0, 4).Value
Worksheets("Output").Range("F" & place) = ActiveCell.Offset(0, 5).Value
Worksheets("Output").Range("G" & place) = ActiveCell.Offset(0, 6).Value

place = place + 1
Exit For
End If
Next Loopcount

UserForm1.FrameProgress.Caption = Round(((InputCount / ActiveSheet.UsedRange.Rows.count) * 100), 2) & "%"
UserForm1.Label1.Width = (InputCount / ActiveSheet.UsedRange.Rows.count) * 200
DoEvents

Next InputCount

Unload UserForm1

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

Sheets("Instructions").Select

MsgBox "Successfully completed with " & place - 1 & " matches and no errors."
Else
MsgBox "Please make sure there are values for columns: Partner Name, Invoice #, MFPN:, Quantity:, Region:, and Date:"
End If
Else
MsgBox "Please put data into Input page first."
End If

End Sub



Thanks,

Jeff

Thank you sir it worked perfect!