none
In VB, what code would I use to select a range after..... RRS feed

  • Question

  • Hello, 

    In VB, what code would I use to select a range after completing the "Find" coding in order for the coding to select the current active cell and range R13 C10 instead of Range("B7:L19").Select?

    My current situation has a lengthy set of raw data that I am attempting to use the "Find" feature to locate the name and then copy the corresponding current cell and range R13 C10 and paste it to another tab. I attempted to use the record macro to see if I can find an alternative method to use instead of the Range("B7:L19").Select as the Find Search will be on different rows in the document and when the next set of data is pulled, it could be a completely different range.

    Sheets("Sheet1").Select
       Range("a1").Select
        Cells.Find(What:="Employee Name 1", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range("B7:L19").Select

    Thank you in advance for any help!

    -Frank

    Friday, October 31, 2014 2:40 AM

All replies

  • Re:  find and copy/paste

    Your request is not clear (to me).
    It appears you could paste in two steps: 
       Paste the specific data (B7:L19) and then do the Find and paste the found cell data where you want.

    Also, "Select" has very little value in most VBA code...
        Range("B7:L19").Copy Destination:=Range("H21") ... requires no selection.
        (note the space between Copy and Destination)
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Saturday, November 5, 2016 2:34 AM
    Friday, October 31, 2014 11:42 AM