Select Case Code for Column A Values to Populate Results in Column B in Same Row

Hello and thanks in advance.

I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").

I think I'm close, but my struggles I believe are in coding the destination range since I can get it to work for just one cell in B!

Below is my code that is not working...

Please Login or Register  to view this content.

Any help is much appreciated! Thanks, Chris

Nice, you're awesome Norie! Thanks, works great! I was trying to think of how to use offset, although I would have never come up with it. Another of many lessons I've received here!

In gratitude,
Chris

Thanks xladept...great minds apparently!

One thing I've noticed is that while the code works, it seems to be bit slow (in Excel terms anyways) and takes about 10 seconds or so to roll through all the cases and rows. Any ideas on how to make it run faster? Not all the rows will have entries, I'm curious if there's a snipet of code to say "at the first blank row, exit the sub" or set the range to be more dynamic by identifying the range in column A where it starts at A14, finds the last entry (say A25) and then runs on that range.

Thoughts?

Thanks,
Chris

Wow! Lightning quick (like your response time)! Using it with Worksheet_Activate (since the A range prefills before a user goes to that sheet) and I couldn't even see it fill in. Sweet. Thanks much Norie!

Chris