1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Need Help - Macro using ctrl-f and paste

Discussion in 'Business Applications' started by bricey, Jul 13, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. bricey

    bricey Thread Starter

    Joined:
    Jul 13, 2007
    Messages:
    7
    Hello, I'm a new member who has stumbled upon this place in a desperate search for a simple answer!

    I am recording a macro (I can write adjustments, but don't generally try to write macro's from scratch).

    I have tried to explain what I want it to do below (it works as I am recording it, but when I change data and then try to run it, the thing fails)

    Select Sheet 1
    Select cell B1
    Copy cell B1
    Select Sheet 2
    Select Column B
    Find (control-f)
    Paste (ie the data copied from cell B1 in sheet 1, to be pasted into the find function)
    Find next (so that the cell selected matches the data pasted into find)

    The data in cell B1 gets changed, hence using the ctrl-c,ctrl-f,ctrl-v combo, but when recorded the "ctrl-v" part doesn't show as a paste, but rather a find for the data as it was when I recorded the macro (which as mentioned, gets changed by the user).

    Anyone able to point me in the right direction?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You haven't said what you want to do with the information when you have found it.
    The attached workbook has some VBA that does basically what you want .
     

    Attached Files:

    • Find.xls
      File size:
      21.5 KB
      Views:
      278
  3. bricey

    bricey Thread Starter

    Joined:
    Jul 13, 2007
    Messages:
    7
    What I have is two sheets, one a list of invoices with various column headings (inv#, date, client, etc....)

    The second sheet is a pro-forma invoice. Outside of the print area is a cell to type in the invoice number you wish to view. The rest of this sheet is VLookup's from the list of invoices (hence as you change the number in the cell, the information in the invoice changes).

    However, and this is where I am floundering, whilst in the pro-forma invoice the user will type in a narrative of what the invoice is for (this is not picked up from the list at that point). I then want to have a macro that allows the user to click a button and the following to happen:

    Copy the invoice number entered on the pro-forma invoice, and find that invoice in the invoice list.
    Copy the narrative hard keyed into the invoice into a blank column on the invoice list sheet.
    Change the narrative in the pro-forma invoice back to a vlookup (which will pick up the newly pasted information).

    When I recorded the macro it appeared to work. However although I had used ctrl-c, ctrl-f, ctrl-v as the way of finding the invoice number from the proforma in the invoice list, the code actually showed it as ctrl-c, ctrl-f, then the invoice number at the time of recording.

    Thus when I changed the invoice number from 1 to 2, the macro simply pasted the narrative into invoice 1 again (despite me now wanting to find invoice number 2).

    Does that make sense, or am I making something easy sound incredibly difficult?
     
  4. bricey

    bricey Thread Starter

    Joined:
    Jul 13, 2007
    Messages:
    7
    This attachment shows what I am trying to do. It works for invoice 1, but only for invoice 1.

    The macro code is:

    Sheets("Invoice").Select
    Range("B1").Select
    Selection.Copy
    Sheets("Sales Day Book").Select
    Columns("A:A").Select
    Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    ActiveCell.Offset(0, 3).Select
    Sheets("Invoice").Select
    Range("A7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sales Day Book").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Invoice").Select
    Range("A7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-6]C[1],lookup,4,FALSE)"
    Range("A8").Select

    I know it is this bit that is the problem:

    Selection.Find(What:="1"

    But don't know how to get it to paste the copied cell after "What:=" rather than the value when I recorded the macro!
     

    Attached Files:

  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What Cell holds the narrative that on sheet1 that you want to copy and what cell on sheet 2 do you want to paste it in?
     
  6. bricey

    bricey Thread Starter

    Joined:
    Jul 13, 2007
    Messages:
    7
    Glad you don't give up easily!

    although all of the information is going from sheet 1 to sheet 2, I want this peice of information to go the other way. The narrative to copy is on sheet 2 in cell sheet 1 narrative is in cell A7. It needs to be copied into sheet 1, but the cell will depend on the invoice number, the column would be D, but the row would depend on the invoice number.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This versions takes the data from cell a7 on sheet 2 and puts it in whatever row on sheet 1 that matches the value a2 on sheet 2.
    You should be able to change the ranges do get it to do what you want.
     

    Attached Files:

  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Just a tiny catch for OBP's code, possibly. This line:

    With ws.Range("b2:b35666")

    I'm guessing B35666 should be B65536, i.e. number of rows in a worksheet. Or even:

    With ws.Range("b2:b" & Rows.Count)

    HTH :)
     
  9. bricey

    bricey Thread Starter

    Joined:
    Jul 13, 2007
    Messages:
    7
    Many, many thanks for helping me out.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/595235

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice