[theadventurer]

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]

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]

Works wonders. Life saver! Thanks so much! Even easier than macros haha.

I guess I'll learn those some other day then