Setting a column to a Named Range to Changing the data with Case 1

[Evil8]

Every month I get an excel "workshop" workbook so that I can input the information into an SQL database to update the client webpage. I'm working on a macro that will reformat the worksheet to match the database table. I've gotten everything as I want except the column C ("wTime") has times in a text format of 10am, 11am, 12pm, 1pm, and 2pm. I need the cell data to convert to 10:00 AM, 11:00 AM, 12:00 PM, 1:00 PM, and 2:00 PM.

I thought it would be a simple case statement:

   Select Case wTime
        Case "10am"
            wTime = "10:00 AM"
        Case "11am"
            wTime = "11:00 AM"
        Case "12:00pm"
            wTime = "12:00 PM"
        Case "1pm"
            wTime = "1:00 PM"
        Case "2pm"
            wTime = "2:00 PM"
    End Select

For this to work I'm assuming that wTime must be a Named Range so I added this above it:

  'Set wTime name to wTime column
    Set Rng = ActiveSheet.Range("C:C")
    ActiveSheet.Names.Add Name:="wTime", RefersTo:=Rng

I didn't get an error, but the data in column C didn't change either.

What did I miss?

Thanks everyone!

 

[Evil8]

Okay as I'm doing a bit more looking around trying to figure this out I may be off track with the named range idea. Do I need a for loop and check cells for null values too?

This is my total macro so far:

Sub workshops()
'
' workshops Macro
' Macro recorded 4/29/2013 by evil8
'

'

  Dim rng As Range
  Dim wTime As Long

    Cells.Select
    Selection.ClearFormats
      
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    
    Range("C:C").Select
    Selection.Delete Shift:=xlToLeft
    
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
            
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "workshop_id"
    
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "wDate"
    
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "wTime"
    
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Location"
    
    Columns("B:B").Select
    Selection.NumberFormat = "yyyy-mm-dd;@"
  
'Set wTime name to wTime column
    Set rng = ActiveSheet.Range("C2:C200")
    ActiveSheet.Names.Add Name:="wTime", RefersTo:=rng
    
    Select Case wTime
        Case "10am"
            wTime = "10:00 AM"
        Case "11am"
            wTime = "11:00 AM"
        Case "12:00pm"
            wTime = "12:00 PM"
        Case "1pm"
            wTime = "1:00 PM"
        Case "2pm"
            wTime = "2:00 PM"
    End Select
    
        
    Range("A1").Select
    
End Sub

Thanks

 

[SkipVought]

hi,

This handles the time

    Dim r As Range, a
    
    For Each r In [YourRange]
        a = Split(r.Value, "am")
        
        If IsNumeric(a(0)) Then
            r.Value = TimeSerial(a(i), 0, 0)
        Else
            a = Split(r.Value, "pm")
            If a(0) = "12" Then a(0) = "0"
            r.Value = TimeSerial(a(0) + 12, 0, 0)
        End If
       
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

Thanks Skip, That works great, but I'm getting a Run-time Error '9': Subscript out of range. The editor points to:

If IsNumeric(a(0)) Then

It doesn't matter either way I define the range

Dim r As Range, a
Set r = Range("C2:C200")
    
    For Each r In r.Cells

or

Dim r As Range, a
    
    For Each r In Range("C2:C200")

Otherwise your solution works excellently! I do these things maybe once a year and have to retrain myself all over.

Again thanks for your help skip.

 

[SkipVought]

]code]
Dim r As Range, a

For Each r In Range("C2:C200")

[/code]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

simpler

    Dim r As Range, a
    
    For Each r In Range("C2:C200")
        a = Split(r.Value, "am")
        
        If IsNumeric(a(0)) Then
            r.Value = a(0) & " am"
        Else
            a = Split(r.Value, "pm")
            r.Value = a(0) & " pm"
        End If
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

The figured out the error occurs because I only have 15 records in this particular worksheet. When I set the range to "C2:C16" no error. To have this work on different worksheets the row count in the range will need to be dynamic.

I tried to find the last row and use that in the range:

 Dim LastRow As Long, r As Range, a

  'Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  For Each r In Range("C2:C" & LastRow)

This is still throwing the error.

 

[SkipVought]

For each r in range(cells(3,3), cells(3,3).end(xldown))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

Hey Skip I ended up using this to define the range.

   For Each r In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)

So I've run it as it was and added more records so it looks good so far. Thanks for all your help.

 

[Evil8]

Ha we posted at the same time...