VBA - Use Cell as search criteria for Find All

Hello,

I'm new to VBA and everything I've compiled so far is from help with other Excel forum members and online Searches. I'm working on creating a Cypher in which I could use to communicate with those who have a matching Cypher key using Excel. Part of the set up process (in order for more efficient running of the cypher on a slower computer) is to run a search and paste all those search results into a column and the next results into the next column and so on and so forth. The code I have posted works so far but I need to be able to use the cells in the first row as my search criteria in order to connect the circle and have Excel automatically finish populating the search results.

I have included the file with the original code. Go to the Srch Tab, Click Find All Button, and type in a letter. Excel will do all the work and all you have to do is go to the Srch Tab and pick another letter (one that corresponds to the next column.

This code, instead of continuing with the 2nd Row, it ends on the first row of the column with the next search criteria I want to use. How do I get VBA to search for that particular cell it lands on and how do I get it to stop when it lands on a blank cell?

Thanks in advance for your help.

~MsBBS

Please Login or Register  to view this content.

Sample.xlsm

Originally Posted by AlphaFrog

I undersand the code, but I don't follow what you're asking.


Cells in the first row of what?

First Row of the "Rand" Tab. Basically the Top Cell of All Columns after E but ending on the last Column that Is Blank.

Originally Posted by AlphaFrog

I don't understand this part?

By this, I'm talking about the very last cell that's selected when the Macro Ends. The original version of my Macro Ends in row 2 of the Column that it's in.

What I want the code to continue to do is go to the First Cell of the Column next to it, read the contents of that cell, use those contents as the search criteria, and paste the address of all the search results in A1 of the "Search Results" Tab, have =Rand() fill from the B2 of the Search Results Tab until the last Row of the A Column that has Data, Sort Column A by Smallest to Largest of Column B, Copy only the cells in Column A with data, paste those cells starting in "Col Letter" Row 2, move one cell right and one cell up (1st Row of the Column), Use the contents of that cell as the search criteria, paste the addresses of the search results into Cell A1 of the "Search Results Tab" - and the circle of events continues until the code reaches a blank cell in the Cell of the First Row of the Column next to it.

If you open the file I posted here; Open the Search Tab; Click the "Find All" Button; Put the letter D in the Box that appears; and hit "Enter", The code will run perfectly but it will stop in cell Y2. Cell Y1 is labeled E. Hit that Find All Button again and search for the letter "E", the code will end in Cell Z2 and Cell Z1 is already labeled F. Now, if you press "CTRL + SHIFT + F" it should run the Macro called "FindCopyAll_Alt". This will do the same exact same thing as hitting the "Find All" button except this time, it will end in cell AA1 instead of Cell AA2. AA1 has the letter G in it. I would like for this Macro to continue running by searching for that G then the H next and so on until it reaches the blank cell at location AU1.

Thanks for the inquiry. Really having a hard time explaining it which is why I posted the File.

~MsBBS

bump up to top

Bump up to top