Better way to search through 35000 lines?

[instanceofti]

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

[SamT]

Dim Found As Range
Set Found = Range("A:A").Find(Val(Response))
If Found is nothing then
MsgBox "Item Number Not Found!", vbExclamation
Else
MsgBox ("Item Number " & Val(Response)) & " Found at " & Found.Address)
End If

[D_Marcel]

Greetings instaceofti, be welcome to VBAX!
Certainly you're taking the slowest way possible to search the desired value but don't worry, you can do this in a glance.
I would recommend the .Find Method.

Sub Find_Values()

Dim MyList As Range
Dim Target As Range
Dim MyValue As String

Set MyList = Plan1.Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
MyValue = "Test"

With MyList
Set Target = .Find(MyValue)
If Not Target Is Nothing Then
Target.Select
Else
MsgBox "No results for " & MyValue & "."
End If
End With
End Sub

Hope it helps.

Regards,

Douglas

[instanceofti]

Will the find method still allow me to get "active cell" for procedure further down?

And thank you, glad to be part of the group. Although limited perhaps at somepoint I may be able to help someone :)