+ Reply to Thread
Results 1 to 5 of 5

VB Contact Search (Find, Copy and paste) Excel 2007

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    818

    VB Contact Search (Find, Copy and paste) Excel 2007

    Good day everyone,
    I was fortunate to have some help on building this code and now I am trying to figure out why a new code is not working.
    I have the following code in a macro

    Private Sub CommandButton1_Click()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Clients Contact")
    Dim lr2 As Long
    Dim iFind As Range
    Application.ScreenUpdating = False
    
    lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
    
    Set iFind = ws2.Range("A5:A" & lr2).Find(What:=TextBox1.Value, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
        If Not iFind Is Nothing Then
            iFind.Resize(1, 6).Copy
            ws1.Range("C3").PasteSpecial xlPasteAll, Transpose:=True
        Else
            MsgBox ("That name cannot be found.")
        End If
        
    Application.CutCopyMode = False
    Range("A1").Select
    End Sub
    This is searching in my Column A (Sheet2) starting at A5 then when finding the information it copies itself to my sheet1 in C3.

    I need a Cmd button3 to search for the information in Column “B5: B” and when it gets this info, to copy the matching row (A to F) the same way it is doing for my previous code, in C3 while transposing the info.

    What do I need to change to make this happend?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VB Contact Search (Find, Copy and paste) Excel 2007

    Like so:

    lr2 = ws2.Range("B" & Rows.Count).End(xlUp).Row
    
    Set iFind = ws2.Range("B5:B" & lr2).Find(What:=TextBox1.Value, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
        If Not iFind Is Nothing Then
            iFind.Offset(, -1).Resize(1, 6).Copy
            ws1.Range("C3").PasteSpecial xlPasteAll, Transpose:=True
        Else
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    818

    Re: VB Contact Search (Find, Copy and paste) Excel 2007

    Pure genious…Thank man

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VB Contact Search (Find, Copy and paste) Excel 2007

    I have marked this thread solved for you.
    In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

  5. #5
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    818

    Re: VB Contact Search (Find, Copy and paste) Excel 2007

    Thank you for making this Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1