Case Statements

**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

**shg** · 07-07-2008, 11:04 AM

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

Application.WorksheetFunction.Rept(...)

**anazarian** · 07-07-2008, 11:48 AM

Sorry about not providing the example. My workbook was on my computer at work and I was at home for the weekend. This is what I thought would work, but it doesnt. Going to look at what Broro and shg just suggested. I am a novice and all this stuff is new to me so...
-Arthur

Sub SkuBuilder()
'SkuBuilder Macro
'Keyboard Shortcut: ctrl+s

Dim Design As String
Dim Color As String
Dim Size As String

Design = Range("C1").Value
Select Case Range("C1").Length
    Case 3
        Range("C1").Value = Range("C1").Value & "."
End Select

Color = Range("D1").Value
Select Case Range("D1").Length
    Case 2
        Range("D1").Value = Range("D1").Value & "....."
    Case 3
        Range("D1").Value = Range("D1").Value & "...."
    Case 4
        Range("D1").Value = Range("D1").Value & "..."
    Case 5
        Range("D1").Value = Range("D1").Value & ".."
    Case 6
        Range("D1").Value = Range("D1").Value & "."
    Case 7
        Range("D1").Value = Range("D1").Value & ""
End Select

Size = Range("E1").Value

Do While Not IsEmpty(ActiveCell.Offset(0, 1))
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(1, 0).Range("F1").Select
ActiveCell.Value = "Design" & "Color" & "Size"
Loop
End Sub

**anazarian** · 07-07-2008, 02:04 PM

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

**shg** · 07-07-2008, 02:07 PM

Select Case Range("D1").Length

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

**anazarian** · 07-07-2008, 04:05 PM

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.

Dim Design As String
Dim Color As String
Dim Size As String
Dim SKUDesign As String
Dim SKUColor As String
Dim CharcountD As Integer
Dim CharcountC As Integer

CharcountD = Len(Range("C1"))
Design = CharcountD

Select Case Design
    Case 3
        SKUDesign = Range("C1").Value & "."
    Case Else
        SKUDesign = Range("C1").Value
End Select


CharcountC = Len(Range("D1"))
Color = CharcountC
Select Case Color
    Case 2
        SKUColor = Range("D1").Value & "....."
    Case 3
        SKUColor = Range("D1").Value & "...."
    Case 4
        SKUColor = Range("D1").Value & "..."
    Case 5
        SKUColor = Range("D1").Value & ".."
    Case 6
        SKUColor = Range("D1").Value & "."
    Case Else
        SKUColor = Range("D1").Value
End Select

Size = Range("E1").Value

ActiveCell.Range("F1").Select
ActiveCell.Value = SKUDesign & SKUColor & Size

End Sub

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?

Case Statements

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions