How to change vb script to use active row

[mattkirby]

Hi

I've got a macro that searches column A for a specific text then moves across to column D to copy the data in the cell. The vb script looks like this:

Range("D22").Select

However, the search might not always find the specific text in row 22 but will always copy the data from column D. Is there a way to change the script to use the current/active row after the search instead of using row 22?

Thanks
Matt

 

[steve the fish]

This would do that:

Dim str_find As String, c As Range

str_find = "whatever"
Set c = Columns("A").Find(What:=str_find, LookAt:=xlWhole)
If Not c Is Nothing Then c.Offset(0, 3).Copy

 

[Norie]

Matt

Perhaps something like this.

strSearchTerm = "Something"

Res = Application.Match(strSearchTerm,  Columns(1), 0)

If Not IsError(Res) Then
    Range("D" & Res).Copy
End if

 

[mattkirby]

This would do that:

Dim str_find As String, c As Range

str_find = "whatever"
Set c = Columns("A").Find(What:=str_find, LookAt:=xlWhole)
If Not c Is Nothing Then c.Offset(0, 3).Copy

Thanks

 

[mattkirby]

Thanks