computing
  • 0

Solved Find multiple words/values in Excel

  • 0

 

Hello,

What I would like to do is to use the for example the Ctrl-F function in Excel to look for different words/values at once. Is this possible?

Even better would be a macro that searches a certain worksheet for lets say 10-100 predefined words/values. If the word/value is found, the row in which the word/value appears should be copied to a second worksheet.

Thanks in advance,

E.

I suggest that you try the code in a backup copy of your workbook since macros can not be undone.

1 – Press Alt-F11 to open the VBA Editor
2 – Click on the name of your workbook in the left hand column
3 – Click on Insert…Module
4 – Paste the code into the right hand pane that opens
5 – You should see Blue, Green and Black text. If you see any Red, something’s not right. Post back here describing what’s Red.
6 – To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.

The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it’s done.

You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you’re satisfied that the code is doing what you want.

In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.

You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for various suggestions on automating that. You’ll learn more by searching for a method that you like than if I just tell you how to do it.

Posting Tip: Before posting Data or VBA Code, read this How-To.

 

Share

0 Answers

  1. I suggest that you try the code in a backup copy of your workbook since macros can not be undone.

    1 – Press Alt-F11 to open the VBA Editor
    2 – Click on the name of your workbook in the left hand column
    3 – Click on Insert…Module
    4 – Paste the code into the right hand pane that opens
    5 – You should see Blue, Green and Black text. If you see any Red, something’s not right. Post back here describing what’s Red.
    6 – To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.

    The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it’s done.

    You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you’re satisfied that the code is doing what you want.

    In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.

    You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for various suggestions on automating that. You’ll learn more by searching for a method that you like than if I just tell you how to do it.

    Posting Tip: Before posting Data or VBA Code, read this How-To.

    • 0