Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Macro in Excel help! Using CTRL+V inside a CTRL+F


(!)

theadventurer's Avatar
theadventurer theadventurer is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
25-Jun-2012, 10:42 PM #1
Solved: Macro in Excel help! Using CTRL+V inside a CTRL+F
Hi all!
I need help to use macros properly. It is my first attempt at doing this, and I don't understand what I am doing wrong.

I have found this thread which resembles my problem, but I still do not understand what to do
http://forums.techguy.org/business-a...cro-using.html

Basically, I have 2 Excel spreadsheets open.
One is List A, the other is List B (Master List).

I have worker codes in List A, and have to find them in the B list to assign them a code (1) in column M.

What I did was go in my first spreadsheet, and selected the first worker code. Then, I started recording my Macro from there.
Ctrl+C worker code
Ctrl+Tab to second spreadsheet
Ctrl+F to open find box
Ctrl+V to paste the worker code copied from before
Close the search box
Cursor now over Worker code, Move over to column M by pressing the RIGHT arrow 12 times (from A to M)
Type "1" and then Enter
Ctrl+Tab to come back to first spreadsheet
Down Arrow to move on to next worker code
STOP MACRO

However, the Macro looks like this :
Selection.Copy
ActiveWindow.ActivateNext
Cells.Find(What:="A4WL7CPA3BRX5", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("M1204").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("M1205").Select
ActiveWindow.ActivateNext
Range("P3").Select

In Bold, you see the specific worker code I had copy-pasted. So whenever I rerun the macro, it redoes the same worker code over and over again, and doesn't seem to go with "whatever was just copied", but rather with "this one worker code you recorded". Also, the "range select" seems to specifically select this ONE cell, instead of moving over by 12 cells (from cell A# we just worked hard to find, to its corresponding M#).

Is there a way to have the macro just follow the EXACT steps I listed above, and stop looking for that one specific worker code? To just copy, and then paste whatever it just copied, find that, and go from there? It seems like Excel Macros were recording very specific cell values and text when it rather should stay open and variable depending on what just got copied.

Thank you very much for your help!
It is greatly appreciated

A grad student looking to save hours of time doing this,
JC
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
27-Jun-2012, 03:26 AM #2
Macro recorder only record specific actions, not generic actions.
So you can't use it to repeat the steps for other cells.
You have to code it specifically in VBA.

As for your case you can have a lookup formula in column M of the Master list to do the check.

eg in cell M12 of List B, type
=COUNTIF('[List A.xlsm]Sheet1'!A:A,A12)

this goes to your List A first column to count the number of instances the value in cell A12 occurs.
Assuming there is no duplicate, the result is either 1 if found or 0 if not found.

If there are more than one instances in List A, the formula can be adapted to
=(COUNTIF('[List A.xlsm]Sheet1'!A:A,A12)>0)*1

*1 will ensure that the result which is True or False becomes 1 or 0.
theadventurer's Avatar
theadventurer theadventurer is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
27-Jun-2012, 10:43 PM #3
Works wonders. Life saver! Thanks so much! Even easier than macros haha.

I guess I'll learn those some other day then
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
copy & paste, excel, macro, macro code, macro help

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑