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: Macro in Excel help! Using CTRL+V inside a CTRL+F

Discussion in 'Business Applications' started by theadventurer, Jun 25, 2012.

Thread Status:
Not open for further replies.
  1. theadventurer

    theadventurer Thread Starter

    Jun 25, 2012
    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

    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

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

    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,
  2. Garf13LD


    Apr 17, 2012
    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
    [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
    [List A.xlsm]Sheet1'!A:A,A12)>0)*1

    *1 will ensure that the result which is True or False becomes 1 or 0.
  3. theadventurer

    theadventurer Thread Starter

    Jun 25, 2012
    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
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!

Thread Status:
Not open for further replies.

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