PDA

View Full Version : .xlsm row restrictions?



pdx_2188
03-31-2011, 10:12 AM
I currently have a macro that I have been working on with a VBA guru from this forum.

Background: This macro has been created to take the input of one worksheet and filter it againts essentially a lookup column in another worksheet, and provide all unmatched items in one tab and all matched items in another tab.

Issue: For some reason when my lookup column is roughly 60k rows it works perfect; however, when my lookup column is the amount i need it to be, roughly 190K rows it does not actually filter it correctly. It just says all items in my input worksheet are moved to my unmatched worksheet. I will attach the code, but I can not reduce the larger file enough to be able to attach. If you need to see a working version let me know. For now I'm trying to see if anyone else has had a similar issue:




Sub Macro1()

Dim Arr
Dim x As Long
Dim Inputt As Range
Dim Cel As Range
Dim r As Long, rw As Long, i As Long

Application.ScreenUpdating = False

'populate array with values
With Sheets("BlackBox")
Arr = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

With Sheets("Input")
Set Inputt = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

For Each Cel In Inputt
i = i + 1
On Error Resume Next
x = Application.Match(Inputt(i), Arr, 0)
If x > 0 Then
'Match Found
r = r + 1
Worksheets("Found").Range("A" & r).Resize(, 7).Value = Cel.Resize(, 7).Value
Else
'No match
rw = rw + 1
Worksheets("Output").Range("A" & rw).Resize(, 7).Value = Cel.Resize(, 7).Value
End If
x = 0
Next
MsgBox "Matched -" & vbTab & r & vbCr & "Unmatched -" & vbTab & rw


End Sub

stanleydgrom
03-31-2011, 02:40 PM
pdx_2188,

Sorry, wrong post.

pdx_2188
03-31-2011, 03:31 PM
No worries I believe I figured it out, I had to declate my range and set my range. They were basically wanting to do their own thing haha:devil2: