NumberFormat Across Multiple Worksheets

[will1128]

I'm using Excel 2003 and have copied values from Sheet1 to Sheet2, Sheet3... Sheet.

If a value is a date on I need to keep the date format on all sheets. (MM/DD/YY HH:MM:SS)

If the values is 0,1 or 2 I need to keep the values as is.
If it's any other number I need it to be NumberFormat = "0.0".

I'm trying to use a for loop and a Select case for this scenario, but I think I'm missing a line of code.

Error message is "Compile Error: Next without For"

Here's my code:
<CODE>
Public Sub format_sheets()
Dim i As Integer
Dim ws As Worksheet
Dim c As Range
For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
Select Case c.value
Case IsDate(c.value) = True
c.value = c.value
Case 0, 1, 2
c.NumberFormat = "0"
Case Else
c.NumberFormat = "0.0"
Next c
Next ws
End Sub
</CODE>

Any help would be great

 

[Joe4]

The problem is not with your For...Next statements, it is that you are missing your END SELECT statement, i.e.

Public Sub format_sheets()
 
    Dim i As Integer
    Dim ws As Worksheet
    Dim c As Range
 
    For Each ws In ActiveWorkbook.Worksheets
        For Each c In ws.UsedRange
            Select Case c.Value
                Case IsDate(c.Value) = True
                    c.Value = c.Value
                Case 0, 1, 2
                    c.NumberFormat = "0"
                Case Else
                    c.NumberFormat = "0.0"
            End Select
        Next c
    Next ws
    
End Sub

Note: I haven't tried running your code or verified the logic behind it, I just analyzed the structure for correct syntax and compiled it to make sure there were no errors.

 

[will1128]

I'm not missing the End Sub...just didn't copy it in.

Here's the entire Sub:

<code>
Public Sub format_sheets()
Dim ws As Worksheet
Dim c As Range
For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
Select Case c.value
Case IsDate(c.value) = True
c.value = c.value
Case 0, 1, 2
c.NumberFormat = "0"
Case Else
c.NumberFormat = "0.0"
Next c
Next ws

Dim DR As Range
For Each ws In ActiveWorkbook.Worksheets
Set DR = ws.Range("A5:IV65536")
Call DeleteEmptyCols(DR)
Sheets(1).Range("A1:F3").Copy Destination:=ws.Range("A1:F3")
Next ws
'will work across all worksheets in activeworkbook regardless of the # of worksheets
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A2:IV2").Font.Bold = True
ws.Range("A4:IV4").Font.Bold = True
ws.Range("A4:IV4").Orientation = 90
ws.Range("A4:IV4").VerticalAlignment = xlVAlignCenter
ws.Range("A4:IV4").HorizontalAlignment = xlCenter
ws.Columns.ColumnWidth = 15
Next ws
End Sub

 

[Joe4]

I didn't say you were missing the END SUB, I said you are missing the END SELECT, which you still are.

 

[will1128]

Thank you very much. You were correct that I was missing the select. I should be more careful in my reading. I'll have to look more into formatting dates now. My first case state doesn't recognize it as a date.