Looping through Columns using for next

[Mozzz]

I need to loop through columns 5 to LastColumn where LastColumn = Cells(1,Columns.Count).End(xlToLeft). Columns. I need to select each one individually so that I can convert the Text to a number using Text to column.

Here is what I tried along with a few other changes, nothing of course which worked
:

  For i = 5 To LastColumn
'        Columns(I:I)) _
            .TextToColumn Destination:=Range(i, & 1), _
'            DataType:=xlDelimited, TextQualifer:=xlDoubleQuote, _
'            Tab:=True, FieldInfo:=Array(1, 1)
'    Next i

Help Please

 

[Mozzz]

Sorry so long getting back. Here is what finally worked for me.

  With Selection
            .TextToColumns Destination:=ActiveCell.Range("A1")
            .NumberFormat = "0.00"
        End With

I really appreciate all the help and feel bad that I was not very punctual in thanking you.

Mozzz

 

[Rick Rothstein]

For a different approach, I think this macro will do what you want (just assign your column letters where indicated)...

Sub MakeNumbersIntoNumbers()
  Dim V As Variant
  Const ColumnsToConvert = "B:C,E,H,M:Z,AA,AE"
  Application.ScreenUpdating = False
  For Each V In Split(ColumnsToConvert, ",")
    With Intersect(ActiveSheet.UsedRange, Columns(V))
      .Value = .Value
    End With
  Next
  Application.ScreenUpdating = True
End Sub

The assumption is that none of those columns are formatted as Text.

Oh, and yes, the .Value=.Value statement is intentional... as a matter of fact, that is the line doing all the work.

 

[Mozzz]

Thanks for the suggestion, I am a little leary of Assigning the Columns, this is what seems to work best for me. I am trying (maybe too much) to make this as dynamic as possible.

'   Converts Text to Numbers
    
    For i = 5 To LastColumn + 1
        If Application.CountA(Columns(i)) > 0 Then
            Columns(i).Select
            
        With Selection
            .TextToColumns Destination:=ActiveCell.Range("A1")
            .NumberFormat = "0.00"
        End With
                    
        ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
            
        End If
    Next i

 

[Rick Rothstein]

Okay, if that looping structure works for you, then use it to surround my .Value=.Value approach...

    For i = 5 To LastColumn + 1
        If Application.CountA(Columns(i)) > 0 Then
            With Intersect(ActiveSheet.UsedRange, Columns(i))
               .Value = .Value
            End With
        End If
    Next

Again, the assumption is that none of those columns are formatted as Text.