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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 

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 .
 

Attachments

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?
 

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!
 

Attachments

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?
 

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.
 

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.
 

Attachments

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 :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top