How to change vb script to use active row

mattkirby

New Member
Joined
Jan 5, 2016
Messages
11
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:

Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,712
Office Version
365
Platform
Windows
This would do that:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,277
Matt

Perhaps something like this.
Code:
strSearchTerm = "Something"

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

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

Forum statistics

Threads
1,077,674
Messages
5,335,603
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top