instanceofti
10-28-2015, 05:39 PM
I use the following code to search 35000 lines. I am not familiar with an array.
If the item is not found or near end of spreadsheet there seems to be a good lag.
Would loading all 35000 lines into an array then search array work better? The line count shrinks also but wont go beyond 35000.
[CODE]
Range("A2").Select
'This searches for a specific number in column A
Do Until ActiveCell.Value = Val(Response)
If ActiveCell.Value = "" Then
MsgBox "Item Number Not Found!", vbExclamation
NotFound = 1
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
'When found the following is supposed to put the corresponding B, C & D cells in a 3 column listbox
If ActiveCell.Value = Val(Response) Then
R = ActiveCell.Row
Frame1.Visible = True
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address 'this part not correct yet
End With
'this section should put additional rows of same search into the listbox as well
'column A is sorted - duplicates in column A expected
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> Val(Response)
R = ActiveCell.Row
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address 'this part not correct yet
End With
ActiveCell.Offset(1, 0).Select
Loop
End If
If the item is not found or near end of spreadsheet there seems to be a good lag.
Would loading all 35000 lines into an array then search array work better? The line count shrinks also but wont go beyond 35000.
[CODE]
Range("A2").Select
'This searches for a specific number in column A
Do Until ActiveCell.Value = Val(Response)
If ActiveCell.Value = "" Then
MsgBox "Item Number Not Found!", vbExclamation
NotFound = 1
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
'When found the following is supposed to put the corresponding B, C & D cells in a 3 column listbox
If ActiveCell.Value = Val(Response) Then
R = ActiveCell.Row
Frame1.Visible = True
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address 'this part not correct yet
End With
'this section should put additional rows of same search into the listbox as well
'column A is sorted - duplicates in column A expected
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> Val(Response)
R = ActiveCell.Row
With ListBox1
.RowSource = Sheet2.Range("B" & R, "D" & R).Address 'this part not correct yet
End With
ActiveCell.Offset(1, 0).Select
Loop
End If