searching a table - fast!

[mas]

Hi,

What is the best way to search a table for a specific value using VBA code
in Excel.

Code follows:

i = 0
Do While i = 0
If ActiveCell.Value = 11111 Then
ActiveCell.Offset(0, 1).Select
Do While j = 0
If ActiveCell.Value = 1 Then
ActiveCell.Offset(0, 1).Select
price = ActiveCell.Value
i = 1
j = 1
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

 

[Bob Phillips]

Look at Find in VBA Help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

hi
try this. it will search all sheets.
Private Sub CommandButton2_Click()
Dim sStr As String
Dim sh As Worksheet
Dim rng As Range

Sheets("yoursheet").Select
sStr = InputBox("Enter something.")
For Each sh In ThisWorkbook.Worksheets
If sStr <> "" Then
Set rng = Nothing
Set rng = sh.Range("A1:IV65536").Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
MsgBox "Found on sheet " & sh.Name & " at cell " & _
rng.Address
rng.Select
Exit Sub
End If
Next sh
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If

End Sub

 

[Bob R.]

For a fixed range something like this:

Sub Macro1()

Range("D5:Z65000").Select
Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
price = ActiveCell.Value


End Sub