+ Reply to Thread
Results 1 to 2 of 2

Macro to make Excel press F2 and Enter for me a bunch of times

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2010
    Posts
    1

    Question Macro to make Excel press F2 and Enter for me a bunch of times

    Hi all.

    I work for a procurement company. We ask companies to submit prices to us for comparison. It's been my job to set up pricing analysis sheets. You can see an example of one attached.

    As you can see, the second sheet in the workbook (Analysis) is pulling data from the first worksheet (Data Drop). What I've been trained to do is to set up the first two lines of data in Analysis, which I've done already. Then, so that we don't have to set up each line of data one at a time, I've been trained on this procedure:
    1) Highlight the first two rows of data on Analysis (Row 5 and 6)
    2) Right Click the highlighted cells, Format Cells, change cells to Text
    3) Enable Show Formulas
    4) Press F2 then Enter for EVERY cell (sometimes I have to do this more than 1,000 times if it's a big sheet!)
    5) Keep the two rows highlighted, then Fill down as many rows as there are companies submitted.
    If all's been done right, you should see that the Analysis sheet is now pulling Data from all the submitted company data on the Data Drop sheet. (For instance, reading down column C you'll see ='Data Drop'!C6 then ='Data Drop'!C56 then ='Data Drop'!C106, etc.)
    6) Keep the cells highlighted. Right Click, Format Cells, change cells back to General
    7) Disable Show Formulas
    8) Do a Find and Replace. Find = and replace with =. (Yes, = with =) (This changes the cells back to reading all the data from the Data Drop sheet)

    I don't know why this ridiculous process works, but it does. (If anyone can tell me why, I'd be fascinated to know why)

    Can a Macro be made to automate this whole process? If not, can even just the F2 then Enter bit be automated so I reduce my risk of carpal tunnel? Perhaps a Prompt that comes up to ask how many times F2 then Enter has to be done?

    I tried to make a Macro to automate this. I can't program, so my Macros are just Excel copying what I'm doing on screen. Trying my limited skill, I can make a Macro for that specific workbook, but for the F2 then Enter step for instance, any time I try it on another workbook, it will fill each cell with ='Data Drop'!B4 or whatever the original Data sheet is named...if that makes sense.

    Let me know if I need to explain anything better. Thanks for looking, everyone.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-25-2008
    Location
    Poland
    MS-Off Ver
    MSO 2K3, 2K10
    Posts
    84

    Re: Macro to make Excel press F2 and Enter for me a bunch of times

    In the example given by you.
    Select the range C5:H19 and enter the formula:
    Please Login or Register  to view this content.
    and confirm with Ctrl + Enter.
    Similarly proceed with the next ranges by changing the formula properly.

    Artik

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1