vba code to search last 4 numbers of a 10 digit policy number


New Member
Sep 18, 2014

Can anybody assist me with the following vba code? I have a VERY long list of policy numbers that are 10 digits long that I would like to enter in the last 4 numbers of that policy number in a search field. Basically trying to save time instead of typing in the whole policy number. Then after finding the full policy number from the last 4 digits, it would automatically input the full policy number and name in another blank cell. Also, if for some odd reason there are two different policies with the same last 4 numbers, then a pop up box would populate and I would be able to click on the full policy number on which policy I need to view. Anybody know how I can write this up? I tried to attach my spreadsheet but maybe i am a newer member, so it wouldnt let me. Below I posted my explanation inside the spread sheet. Thanks in advance.

" My</SPAN> goal for this project is to have a quicker way of entering the policy number with just the last 4 digits of the 10 digit number. When a processor first opens up this workbook, they will put their processor ID in on the main page. The processors are assigned their own policy numbers, so only policy numbers will come up for the one's they are assigned. Then they will type in the last 4 digits of the policy number in cell A6. After clicking search, a macro will find the policy number from the last 4 and enter in the full policy number in cell A2 on the main page with the first name in cell D2. Also, you can see on the policy number sheet there are multiple policy numbers that have the same last 4 digits. So my ultimate goal is when this happens, a pop up box will generate and it will ask which one the processor would like to pick. After clicking on the one they want the full policy number and name would show up on the main page just like if there wasnt a duplicate. when the pop up box comes up, I would like to show only the full policy number and the name. thanks again for any help I can get. </SPAN>One other thing ,</SPAN> If a processor enters in a number that does not assigned to them, then a note will come up saying something like "policy not assigned to "processor ID" AAAA,BBBB etc. thanks"</SPAN>

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics