Case Statements

**anazarian** · 07-03-2008, 04:32 PM

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

**shg** · 07-03-2008, 04:38 PM

Welcome to the forum.

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

**anazarian** · 07-07-2008, 01:07 AM

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

**shg** · 07-07-2008, 01:20 AM

I meant a workbook ...

**broro183** · 07-07-2008, 04:19 AM

hi,

As Shg says, you're more likely to get useful answers when some context is provided in the form of a sample workbook with sample data & expected outcomes. Here's a rough attempt without this context:

Sub EgOfCaseStatements()
Dim cll As Range
Dim DesiredLength As Long
For Each cll In Selection
    With cll
        Select Case .Column
            Case 3
                DesiredLength = 4
            Case 4
                DesiredLength = 7
            Case Else
                DesiredLength = Len(.Value)
        End Select
        '(needs tweaking) _
            .Value = .Value & Application.WorksheetFunction.Rept(".", DesiredLength & -Len(.Value))
        'seems to work
        .Value = .Value & Application.Evaluate("=REPT("".""," & DesiredLength & -Len(.Value) & ")")
    End With
Next cll
End Sub

hth
Rob