Results 1 to 5 of 5

Thread: How to change vb script to use active row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to change vb script to use active row

    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

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How to change vb script to use active row

    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
    Looking for opportunities

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to change vb script to use active row

    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
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Jan 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to change vb script to use active row

    Quote Originally Posted by steve the fish View Post
    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

    Thanks

  5. #5
    New Member
    Join Date
    Jan 2016
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to change vb script to use active row

    Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •