Looping through sheets and changing text to number based on a variable range

[furt0414]

This should be easy, but I can't for the life of me figure it out. I have a workbook with 20 sheets, and column C in all of the sheets contain numbers that are stored as text. I want to loop through each sheet, define the max rows in column C as a variable, then convert C2:Cvariable from text to number. My code works on a single sheet, but won't loop through all of them. What's missing?

Sub Convert()
Dim NumRows As Long
Dim i As Long
Dim v As Variant


For Each WS In ActiveWorkbook.Worksheets
    NumRows = Cells(Rows.Count, "C").End(xlUp).Row
    For i = 2 To NumRows
        For Each v In Array("C")
            If Range(v & i) <> "" Then
                Range(v & i).Value = Val(Range(v & i).Value)
            End If
        Next
    Next
Next WS

End Sub

I appreciate any help!

 

[furt0414]

Ok, I got it to loop through the sheets a different way, but I need it to end the sub if it errors out. Here's what I came up with:

Sub Convert()

Dim NumRows As Long
Dim i As Long
Dim v As Variant
Dim ws As Worksheet
Dim cursht As Worksheet


Worksheets(ActiveSheet.Index + 1).Select


For Each ws In ThisWorkbook.Worksheets
    NumRows = Cells(Rows.Count, "C").End(xlUp).Row
    For i = 2 To NumRows
        For Each v In Array("C")
            If Range(v & i) <> "" Then
                Range(v & i).Value = Val(Range(v & i).Value)
            End If
        Next
    Next
    Set cursht = ActiveSheet
    Sheets.Add After:=cursht
    ActiveSheet.Name = cursht.Name & " Pivot"
    
    'Pivot code here
    
    Worksheets(ActiveSheet.Index + 1).Select
Next ws


End Sub

 

[furt0414]

Solved. It's clunky, but gets the job done.

Sub Convert()

Dim NumRows As Long
Dim a As Integer
Dim i As Long
Dim v As Variant
Dim ws As Worksheet
Dim cursht As Worksheet
Dim pt As PivotTable
Dim PC As PivotCache
Dim PR As Range


Application.ScreenUpdating = False


Worksheets(ActiveSheet.Index + 1).Select


a = 1


On Error GoTo GetOut


For Each ws In ThisWorkbook.Worksheets
    NumRows = Cells(Rows.Count, "C").End(xlUp).Row
    For i = 2 To NumRows
        For Each v In Array("C")
            If Range(v & i) <> "" Then
                Range(v & i).Value = Val(Range(v & i).Value)
            End If
        Next
    Next
    Set cursht = ActiveSheet
    
    'Pivot code here
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Name = "Range" & a
    Set PR = Range("Range" & a)
    Sheets.Add After:=cursht
    ActiveSheet.Name = cursht.Name & " Pivot"
    Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PR)
    Set pt = PC.CreatePivotTable(TableDestination:=ActiveSheet.Range("A1"), TableName:="Table" & a)
    Range("A1").Select
    With ActiveSheet.PivotTables("Table" & a).PivotFields("Store Number")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Table" & a).AddDataField ActiveSheet.PivotTables("Table" & a).PivotFields("Week Total"), "Sum of Week Total", xlSum
    With ActiveSheet.PivotTables("Table" & a).PivotFields("Employee Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Table" & a).PivotFields("Employee Type").ClearAllFilters
    ActiveSheet.PivotTables("Table" & a).PivotFields("Employee Type").CurrentPage = "Non-Exempt"
    
    Worksheets(ActiveSheet.Index + 1).Select
    a = a + 1
    
Next ws


GetOut:
Sheets("Macro").Select
MsgBox "Excel Magic Has Completed"


Application.ScreenUpdating = True


End Sub