How to dynamically fill empty space in a column?

Macro to fill in column data based on another columns prefix

• QUESTION: First of all I just want to say thank you for taking the time to help me on this...I know you don't have to, but it is nice to get help from someone. This question has really been bothering me and I can't seem to figure it out. I can't even figure out where to get started =( I read a previous post in which you helped someone with a macro to assign a value to a column based on the left most characters in a different column. This is similar, but more difficult... I have a macro that I pieced together from code on the internet that takes all our monthly sales data from all the other worksheets and combines it into a Master Sheet called "All Contracts." It searches the sheets for the last row which contains data, and copies that section of data, then copies it over to the All Contracts Sheet. Now what I need to do is add a macro that goes through the All Contracts sheet row by row, and looks at the data in column H (Model Number) and assign it the correct Business Unit tag in column Q. What I need the macro to do is have a bunch of if/then statements (I assume) with Left functions so that if a model number starts with a certain prefix it is assigned the correct business unit tag. Example: Model Prefix AAB belongs to business unit 01; so if model number in column H is AAB123ABF I need the macro to put 01 into column Q (because that model number started with AAB). I have roughly 20 prefixes. Some are 3 letters long, others are 2 units long, and they are ALL followed by a series of numbers (usually 3 or 4 numbers), and then those numbers are followed up by a few more letters and numbers denoting color code. I have 5 Business Units to sort those 20 model prefixes into: 01, 02, 05, 06, 07. I would think that the macro would look something like (not in code, just rationale): If H_column_value(left(3 places))="AAB" then:"01" or If H_column_value(left(2 places))="BC" then:"03" or If H_column_value(left(3 places))="DAK" then:"01" or If H_column_value(left(3 places))="ZYZ" then:"05" Else:"Blank" End if Like I said though, I need to have this macro run down the entire set of rows until it reaches the last row of data. I would think something like this could be done...but I am not sure how difficult it might be. Any help would be appreciated, even if it is just a starting point. ANSWER: Ryan, Whomever you sent this too was unable to help for one reason or another and dumped your question in the question pool where I just found it. I would see the approach to be something like this Dim cell as Range, r as Range Dim s as String, s1 as String with worksheets("All contracts") set r = .Range("H2",.cells(.rows.count,"H").end(xlup)) for each cell in r s = Left(cell,3) s1 = "" select case s Case "AAB" : s1 = "01" Case "DAK" : s1 = "03 Case "ZYZ" : s1 = "05 end select if s1 = "" then s = Left(cell,2) Select Case s Case "BC" : s1 = "01" Case "XX" : s1 = "03" end Select end if if s1 <> "" then ' so you don't loose leading zeros, format cell as Text .Cells(cell.row,"Q").Numberformat = "@" ' text .Cells(cell.row,"Q").value = s1 end if Next cell end with if that isn't clear or I missed something, then post back with clarification/further questions. ---------- FOLLOW-UP ---------- QUESTION: Thank you very much for the response! I have added it and tested it and it works GREAT!!! The only follow up I would have would be to ask where I would add a piece of code so that it isn't looking for the prefix in a Case Sensitive approach. For example, if an entry was accidentially input as "aAB" it would still apply the correct Business Unit code.

• Answer:

  -- Ryan, we just need to ucase what is in the s variable: Dim cell as Range, r as Range Dim s as String, s1 as String with worksheets("All contracts") set r = .Range("H2",.cells(.rows.count,"H").end(xlup)) for each cell in r s = Ucase(Left(cell,3)) s1 = "" select case s Case "AAB" : s1 = "01" Case "DAK" : s1 = "03 Case "ZYZ" : s1 = "05 end select if s1 = "" then s = Ucase(Left(cell,2)) Select Case s Case "BC" : s1 = "01" Case "XX" : s1 = "03" end Select end if if s1 <> "" then ' so you don't loose leading zeros, format cell as Text .Cells(cell.row,"Q").Numberformat = "@" ' text .Cells(cell.row,"Q").value = s1 end if Next cell end with so then s will always be all upper case, so in your case statements make sure the letter combinations are all uppercase (as I have them now).