Tool to lookup policy numbers when given a string to decode

Hello Forum,

I am a first time poster, long time viewer. I have been a techie for a long time, but never thought myself a programmer. I currently work for a fortune 100 Insurance company as a lower-man on the totem pole, looking to develop this tool to take a lot of the waste out of the process, make it better for our customers, and cut down on wasted labor costs. This being said, this code is mostly what I have taught myself, through some old school text books and from Google. So here is what I have started, I understand the formatting will likely make the more tenured folks have meltdown, so I am open to any revisements, BUT I am most interested in how to get the main tool to reference back and forth between worksheets.

I had been working on only one sheet in the workbook, and it was working fine. I have 15 cells ready for input, when the go button is selected it takes these cells and searches a table of example policy numbers and should bring up a message box with the department this is and the phone number to call. I started wanting it to just highlight the section where it explains the formatting of the number given, but think in order for the higher-ups to want to take it and run, it will need to be relatively polished and require little training... haha. So with all that, here is the code.

Please Login or Register  to view this content.

Perhaps a workbook example would be better, with or without the code.

In your workbook, show some examples of the inputs that could be given and the expected results of the lookup. It may well be possible with no VBA at all, but we'll have a look either way.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Ok, since you didn't give me the example results/samples I asked for, I'll try to answer blindly.

Your ButtonClick1 macro has numerous hardcoded references to the sheet "Long". I would recommend declaring a sheet variable, then using that variable all through the code. If the code continues to work as originally designed with this one change, you would then link that variable to your new sheet to test.

Here's your original code with the new variable created for the original test:

Please Login or Register  to view this content.

Sorry, I wasn't understanding how you wanted the examples. In this, I would type WC5-35S-456789-123 into the search box and then it would use the coding where one of the elseif statements has

Please Login or Register  to view this content.

So it would select the range on Sheet "Long" and highlight this section that discusses Involuntary markets. At this point this is all that I had, the selection of the right info. Once I had most of the impossibly many if then elseif statements to separate the many ways our policy numbers can be coded, and had this working 99% of the time, then I wanted to make it display this info neatly and not simply show the selection in the table.

*Very similar to Middle Markets* Ex Pol. WC2-35S-456852-012
6th character is a S WC5-53S-456000-015 Involuntary Markets Service

**Call 800-###-####**

I am very appreciative for the info so far, I will try this code later today and get back to you. I don't expect you to do all the legwork here, but the help around the seemingly obvious is very helpful

I've added the missing CODE tags above again, please try to remember.

Another tip, this can be simplified:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

So now I am getting this error. I think its related to how I am defining things. I need the pos1 - pos15 to be able to accept input of one character and then to be able to assess If this is a number, letter, or in some cases certain specific characters and do the search based on this.

So I have my pos variables dimmed as Strings
Then when I do the pos1 = wsLookup.Cells(M5) line
If I use .Cells or .Range or add .Value on the end - it doesn't seem to like the way I am asking for this type of data, is this correct?

Sorry I just saw the message about CODE tags and am going to read this.
I have now read and will do this when posting code.

CELLS(5, 13)

That's M5.


Perhaps you meant:

Please Login or Register  to view this content.