searching a table - fast!
 

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

searching a table - fast!
 

Look at Find in VBA Help.

--

HTH

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


"mas" wrote in message
...
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

searching a table - fast!
 

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
-----Original Message-----
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




.

searching a table - fast!
 

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