VBA Find Function

[2000km12]

Hello! I am attempting to use the VBA "Find" function to locate a certain value in a worksheet. Once I find this value, I want to copy a value from a different column, in that same row. I'm unsure how to do this. Does anyone have recommendations?

I am relatively new to coding in general.

Thanks

 

[Akuini]

I am attempting to use the VBA "Find" function to locate a certain value in a worksheet. Once I find this value, I want to copy a value from a different column, in that same row.

Find in what column? copy from what column? then paste it where?

 

[rlv01]

Use the range offset property together with the range copy method. Example:

Sub Test()
    Dim FoundRange As Range
    Dim SearchTerm As String
    
    'Look for something
    SearchTerm = "Nugget"
    Set FoundRange = ActiveSheet.UsedRange.Find(What:=SearchTerm)
    
    'If found
    If Not FoundRange Is Nothing Then
        
        'Copy some other cell in the same row but a different column, relative to the first cell
        FoundRange.Offset(0, 4).Copy
        
        'Paste it somewhere
        ActiveSheet.Range("A1").PasteSpecial (xlPasteValues)
    Else
        MsgBox "'" & SearchTerm & "' not found"
    End If
End Sub

 

[2000km12]

Use the range offset property together with the range copy method. Example:

Sub Test()
    Dim FoundRange As Range
    Dim SearchTerm As String
   
    'Look for something
    SearchTerm = "Nugget"
    Set FoundRange = ActiveSheet.UsedRange.Find(What:=SearchTerm)
   
    'If found
    If Not FoundRange Is Nothing Then
       
        'Copy some other cell in the same row but a different column, relative to the first cell
        FoundRange.Offset(0, 4).Copy
       
        'Paste it somewhere
        ActiveSheet.Range("A1").PasteSpecial (xlPasteValues)
    Else
        MsgBox "'" & SearchTerm & "' not found"
    End If
End Sub

Thank you! I will try this out!

 

[2000km12]

Find in what column? copy from what column? then paste it where?

A value will need to be found in column A, then the value to be copied will be from that same row, but from column G. It will then be pasted on another worksheet; which i do know how to do already.

Thanks