Find or lookup function?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Find or lookup function?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    A translation of my earlier enquiry - which is probably baffling most people!

    I want my macro to copy a row from worksheet A and paste it into a row in worksheet B.

    The problem is that the rows in Worksheet B change daily - so I need to instruct the macro to find the appropriate row in Worksheet B first to paste the data in.

    Worksheet B contains people's names all the way down column A. All I want my macro to do is "find Joe Blogg's name in column A and paste the data in cells C:E of his row (i.e. a couple of cells to the right of joe blogg's name).

    Can anyone help?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    whats in the row that you want to copy?
    is it the whole row you want to copy?

    [ This Message was edited by: willlobb on 2002-04-04 04:27 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The data that is to be pasted is just numbers - the first part of my code which does the copying is as follows:

    Sub transfer_data()
    Application.ScreenUpdating = False
    Workbooks.Open Filename:= _
    "C:My DocumentsClients.xls”
    Range("C129:G129").Select
    Selection.Copy
    ActiveWorkbook.Save
    ActiveWorkbook.Close


    Now, I want to switch to another Workbook and paste this data in cells C:G in the row that has Joe Bloggs name in Column A. Today Joe Bloggs' name happens to be in A9 but tomorrow, after I've inserted other names, it will be on a diferent row.

    I've searched the Mr Excel Noticeboard and and I've seen this code which I've been playing around with because it seems like it might be what I'm looking for - but I can't get it to work:

    Columns("A:A").Find(What:=Joe Bloggs, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
    ActiveCell.Offset(0,3).Select
    ActiveSheet.Paste

    Hope you can help,

    Thanks

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Would something like this do for you

    Sub FindName()
    Dim ThisRow
    (this part I presume is where you have already selected your range to copy)
    Sheets("Sheet1").Select
    Range("C4:E4").Select
    (this is the part that copies your selection to the next sheet)
    Sheets("Sheet2").Select
    Columns("A:A").Select
    Selection.Find(What:="Joe Bloggs", After:=ActiveCell, LookIn:=x1Values, _
    LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, _
    MatchCase:=False).Active
    ThisRow = ActiveCell.Row
    Range("C" & ThisRow).Select
    ActiveSheet.Paste
    End Sub

    Is this what you were looking for?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Richard - your reply came in while I was posting mine - the second half still applies. What I did was place the active cell row number into a variable called This Row and then used it for pasting the data.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Sam,

    Thanks for that - this works great but with one small problem: the cell gets located correctly but the data wont paste - is this because it has been dropped from the memory after closing the first worksheet?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thats exactly what happened, because i wrote a similar piece of code for you and thats what hqppened to me.

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for your help guys - I have to say that this site has helped me on many ocassions and I think it's the most useful resource on the net - really appreciate it!

User Tag List

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
  •  

 

 
DMCA.com