One way to do this is using a input box to prompt the user for a value then use the 'Find' method to search the entire sheet or a defined range. Another way to search the values would be to use a Loop to check each cell in a column, then exit the loop when a match is found.
This is an example I adapted from code I use which uses the 'Find' method.
Code:
Sub FindInput()
Dim stringToFind As String
Dim Found As Range
stringToFind = Application.InputBox("Enter invoice Number to find?", _
"Search String")
If Trim(stringToFind) = "" Then Exit Sub
Set Found = Cells.Find(What:=stringToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False)
If Found Is Nothing Then
Else
With Found
.Activate
If Not Len(.offset(rowOffset:=0, columnOffset:=1)) = 0 Then
.offset(rowOffset:=0, columnOffset:=2) = "paid"
Else
' here I would just activate the cell insted of showing a message
msgbox "The value does not Exist"
End If
End With
End If
End Sub
dBforums
>
VBA code to find a value in a cell
07-29-06, 02:39
Linear Mode
