Search code examples
excelvbaexcel-2013

VBA to find specific word and copy value to another cell

I am looking for help with vba.

I want to search column A for "Summary of CAMBUSLANG" and if found assign the value that is in column D to another cell for talking sake say Column A of another spreadsheet.

any help would be greatly appreciated.

Solution

  • Use the Range.Find Method to find your specific string in column A and the Range.Offset Property to move to column D:

    Option Explicit

Public Sub Example()
    Dim FoundAt As Range
    Set FoundAt = Worksheets("SearchSheet").Columns("A").Find(What:="Summary of CAMBUSLANG", LookIn:=xlValues, LookAt:=xlWhole)

    If Not FoundAt Is Nothing Then 
        Worksheets("AnotherSheet").Range("A1").Value = FoundAt.Offset(ColumnOffset:=3).Value
    Else 'nothing found
        MsgBox "'Summary of CAMBUSLANG' not found.", vbCritical
    End If
End Sub