Searching for cell

[fkheng]

I have an excel file with many columns that denote fields, like in a database

Can I search for an ID in a particular column, and once the ID is found, modify a cell of another field in the same row as the ID just found? Anybody done this before?

[WorkHorse]

Sure thing. Just find the text you are looking for in the column, the offset the column from there and do what you want with the cell. This finds "ID123" in column "E" and turns the cell three coumns over red.

Sub FindAndChange()
    
    Dim rngFound As Range
    Dim strFind As String
    
    strFind = "ID123"
    
    ' Find strFind in Column "E".
    Set rngFound = Columns("E:E").Find(What:=strFind, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
    
    ' If found, make the cell three coumns over red.
    If Not rngFound Is Nothing Then
        rngFound.Offset(ColumnOffset:=3).Interior.ColorIndex = 3
    End If
 
End Sub

[fkheng]

what does the offset part mean?

[WorkHorse]

It means from the found cell, it "offsets" three columns--the cell three columns to the right.

Offset Property (Range Object)


Returns a Range object that represents a range that’s offset from the specified range. Read-only.

Syntax

expression.Offset(RowOffset, ColumnOffset)

expression Required. An expression that returns a Range object.

RowOffset Optional Variant. The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.

ColumnOffset Optional Variant. The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0.