Macro to FIND first cell in column A beginning with a specific letter.

**WISHiKNEW** · 12-10-2014, 05:27 PM

Hello All,

I keep a spreadsheet with all my internet passwords in it. Column B contains the website names which I sort A to Z. The spreadsheet has a row-header from row 1 - 5, which is kept permanently displayed using the freeze-pane function. The first row of data occurs on row 6.

Basically I am trying to come up with a way to "automate my Rolodex list" of website passwords. I have command buttons at the top of the spreadsheet for each letter of the alphabet: A, B, C.......Z. By selecting command button F, I want the "F macro" to FIND the first word in column B beginning with the letter F, then scroll that row to the top of the spreadsheet, i.e., the next row below row 5.

I am having trouble figuring out how to look specifically at the "left" character in the cell. For instance, if I select the command button Z, the code takes me to the word Amazon.com rather than Zappos.com since I don't give any instruction to look at the first character in the cell.

Here is the code I'm using so far:

Please Login or Register  to view this content.

I would prefer using the FIND command rather than looping, which I totally don't understand.

THANX in advance for any assistance that is offered?

**YasserKhalil** · 12-10-2014, 05:45 PM

Try editing this line:

Please Login or Register  to view this content.

**WISHiKNEW** · 12-11-2014, 10:04 AM

YasserKhalil, thanx for the suggestion, but it doesn't correct my problem. The program still hangs up on the first occurrence of the letter that I am looking for, whatever its position is in the word.

*EDIT POST*
OK, so using YasserKhalil's suggestion, I played around with the matchcase True/False conditions by entering a few new test words. One thing I realized is that I forgot to mention that all my data I've enter with UPPER-CASE letters. When I entered the test words in lower-case and ran the code trying to FIND the letter "f", the program did what I wanted. I'm thinking of adding a column containing just the first letter *FORMULA* =Left(A:A,1), to return just the first letter and then use that column as my data search column. Then use the OFFSET function to move over to the actual data column.

Anyone see a better/simpler method of accomplishing this?
THANX!

**RokuP** · 02-05-2015, 11:52 AM

Hello WISHiKNEW,

I just had to solve this exact problem for myself and I came up with the perfect solution, IMHO. Although I do admire the ingenuity of your formula/offset workaround, I think a more direct solution without adding extraneous data to your spreadsheet is better.

First, I created the buttons as Form Controls. This makes things much easier as my solution uses a single macro that is assigned to all of the buttons.
Rolodex.jpg

The key to this is using ".Buttons(Application.Caller).Text" to retrieve the search letter from the button you press, and then using ".Goto" before clearing the AutoFilter.
Here is the complete code with comments on what to change for your particular Worksheet:

Please Login or Register  to view this content.

Let me know if you have any questions or if anyone has improvements to this. Also, please let me know whether this helped or was useful to you in any way.