VBA - Get row number of a value in a sheet, and then insert a different value into a different column of that same row

Question

Is there a way to search for a value in a list such as in column A, identify the row number of that value (for e.g. it could be Row 40 of Column A), go to a different column (e.g. Row 40, Column B) and then insert data into that column but through the macro (so it is done automatically).

I have tried to play around using the code below but cannot seem to get anywhere;

Dim Cell As Range
Dim RowNumber As Long
Columns("B:B").Select
Set cell = Selection.Find(What:="celda", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
RowNumber = Cell.Row

If cell = "celda" Then
    'find row, go to Column B of that row, and insert "abc"

Else
    'do it another thing
End If

I found the code above in the link below;

How to find a value in an excel column by vba code Cells.Find (not my own work but props to the creator)

Harun24hr · Accepted Answer · 2020-07-24T04:00:05.070

You need little modification to your code. Try below...

Sub FindAndAdd()
Dim fCell As Range
Dim strSearch As String

strSearch = "Harun"

With ActiveSheet
   Set fCell = .Columns("A:A").Find(What:=strSearch, _
        After:=.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
        'Lookat:=xlWhole to match whole word.
End With

    If Not fCell Is Nothing Then
        fCell.Offset(0, 1) = "New Value"
          Else
        MsgBox "No match found.", vbInformation, "Search Result"
    End If
End Sub

VBA - Get row number of a value in a sheet, and then insert a different value into a different column of that same row

1 Answers1