Case Statements

Hi, I was wondering if anybody could help me with macro I am trying to build. I know the concept of Case statements sounds simple, but trying to write it in my scenario seems a lot more difficult than I thought it would be. I have three columns with an unspecified number of rows (which are generated from another macro). Lets say those colums are C, D, E, and they should be strings because my criteria are based on the number of digits contained in each cell. I want to add a certain number of periods to a given cell, then add that string onto two other cell strings, and form one combined string in a separate cell.

Basically I want to sort of Concatenate the cells into a given cell in column F for each row, but there are many conditions. If the cells in column C contain 3 digits, I want to add one period "." onto the original string. If it has 4 digits, then I add nothing, and then, add that cell to the string in cell D, and then Cell E. [It is like the function Concatenate(C1&".",D1&"..",E1)]. Cells in Column D have 6 criteria, and cells in Column E have zero criteria.

And I need this to loop down until it reaches an empty row or cells.

I would sooo much appreciate any help anyone has. Thank you so much Excel Forum. You have helped me enormously in general.
-Arthur

Welcome to the forum.

Posting an example and explaining in context would help ...

Conditions:
Cells in Column C
if len(selection) = 3, do cell.selection & "."
if len(selection) = 4, do cell selection & ""
Cells in Column D
if len(selection) = 2, do cell.selection & "....."
if len(selection) = 3, do cell.selection & "...."
if len(selection) = 4, do cell.selection & "..."
if len(selection) = 5, do cell selection & ".."
if len(selection) = 6, do cell.selection & "."
if len(selection) = 7, do cell.selection & ""
Cells in Column E
'(no conditions)
I need a macro to run through Column F that will concatenate the cells in columns C,D,E based on those conditions. The only problem is, I dont know how to write the code and only know the concatenate function and limited VBA. Any help? What commands do I use to a) combine the cell strings and b) do so based on these conditions?

I would appreciate any knowledge anyone might have. Thank you.
-Arthur Nazarian

I meant a workbook ...

You might use VBA's native String function in lieu of

Please Login or Register  to view this content.

Here is the file attached showing the cells if anyone wants a better look of what I'm going for.

Please Login or Register  to view this content.

There is no Length property for a range. Did you try Rob's code?

I tried Rob's code and it wasnt what I was looking for. BUT, I did find it. I had to create three variables, but it works great! This is the first real VBA code that I've written (i.e. without the help of the recorder or other peoples' code). I have never been so happy to see a period appear like magic. I feel like a magician. Now I need to try to figure out how to make the cells referential and to add a loop.

Please Login or Register  to view this content.

I don't know why this inserting the values into Cell F1. I think I am going to put a DoWhileNot isEmpt(Activecell.offset(0,1)), loop.

When using ActiveCell and Offset selections, how do you define where the selection begins? If I want it to start and return values in cell F1 and down until there is an empty cell, how do write that?

Correction, it the loop needs to loop until the cells it refers to are empty