VBA Large If Then Else and Autopopulate help

[Hernandez248]

Hey everyone,

I've searched this forum and I haven't found an exact answer to my question; sorry if this is found somewhere else, but here it is:

For my job I have to get rid of a lot of HDDs(1400 of them, and growing every day).

In excel I have to scan the serial number into the "A" column, then "B" is the make of the HDD, and "C" is the model.

This is extremely time consuming.

I noticed that the first 4 characters of the S/N for the HDD corresponds to the make and model. So, for a Western Digital HDD, model WD2500AAJS, the S/N is WMAV2A983220.

I can't figure out, in VBA, how to code an IF statement that first looks for that string of 4 characters at the beginning(WMAV) and then populates the Make and Model in adjacent cells in the same row, from a list of what different Makes and Models it could be.

I have about 50-90 different models of HDDs. Also, if the S/N doesn't correspond to any of them (FALSE) I need it to write "Unknown" in both fields.

If someone could get me started on this, I'd appreciate it greatly.

[AB33]

I am sure you will get help, but it would be easier for anyone to see if you could attach a sample with a desired result. You do not need to put the whole data-just a sample with dummy data.

[Hernandez248]

Thanks, here is a sample of the excel with the 3 fields I'm having trouble with: S/N, Make, Model.

Hopefully this helps you help me, haha.

[RHCPgergo]

Hi,

you can do that without VBA code, with a simple VLOOKUP. I made an example workbook for you.

Edit: I didn't see the last two posts, but still...

[Hernandez248]

HRCPgergo,

Thanks so much! That's exactly what I needed.
Rep'd.

[Cutter]

@ Hernandez248

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.