VBA Column loop issue

[cooper645]

Hello again,

im working on a much larger macro, which runs into problems when it hits my latest bit of code.

I am am trying to delimit columns from Column G to LastColumn.
The target range of data to delimit is in Row 6 down to LastRow.

i already have the LastRow and LastCol calculated. I have tried many different approaches for this to work and all seem to fail.

Any help is appreciated.

For i = 7 To LastCol
 
 Columns(i).Select
      [COLOR=#0000ff]Selection.TextToColumns , Destination:=[/COLOR][COLOR=#ff0000]Cells(6, i)[/COLOR][COLOR=#0000ff], DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True[/COLOR]
            
  i = i + 1

Next

 

[cooper645]

That code is basically like an 'expanded' version of the code from the original post.

What was the problem with the code I posted and/or the original code?

Errors?

Incorrect results?

Nothing happening?

For i = 7 To LastCol

Columns(i).Select
      [COLOR=#0000ff]Selection.TextToColumns , Destination:=[/COLOR][COLOR=#ff0000]Cells(6, i)[/COLOR][COLOR=#0000ff], DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True[/COLOR]
            
  i = i + 1

Next

Run-time error '1004':
destination refernce is not valid

Dim col As Range
    
    For Each col In Range("G6:AF" & LastRow).Columns

        col.TextToColumns , Destination:=col.Cells(1, 1), DataType:=xlDelimited, _
                          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                          Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                          FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

    Next col

Yours runs with no problem but doesn't give the desired result.

and the long one i recorded runs also but doesn't produce the required reslts.

This is what i am trying to achieve so any other suggestions are welcome


Going to try and run through the range by slecting each cell and sending the return key press command if i can.

 

[Norie]

Try this.

For i = 7 To LastCol

    Columns(i).TextToColumns , Destination:=Cells(1, i), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True
      
Next i

What is it you are actually trying to do anyway?

Is it convert numbers stored as text to 'real' numbers?

 

[cooper645]

no errors, but no results either.

Basically i use the following in my code to extract dates where there is a date, and extract the text if there is no date.

[COLOR=#008000]'Convert Table contents to proper case
[/COLOR]Set rng = Range("B6:AF" & LastRow + 4)
    For Each cell In rng
            If Not cell.HasFormula Then
                cell.Value = WorksheetFunction.Proper(cell.Value)
            End If
        Next cell
    
    
[COLOR=#008000]'format range as dates and apply formatting
[/COLOR]Range("G6:AF" & LastRow + 4).Select
        Selection.NumberFormat = "dd/mm/yyyy" [COLOR=#008000]'format date are as date (working)
[/COLOR]    Selection.FormatConditions.Add Type:=xlTimePeriod, DateOperator:=xlNextMonth   [COLOR=#008000]'Orange format if date within next month
[/COLOR]    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=TODAY()"  [COLOR=#008000]'Format Red if less than today
[/COLOR]        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.399945066682943
        End With
                
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=TODAY()+31"  [COLOR=#008000]'Format green if greater than 1 month left
[/COLOR]            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
            End With
            
            
    
[COLOR=#008000]'Extract the 10 characters from the right for date
[/COLOR]Set rng = Range("G6:AF" & LastRow + 4)
    For Each cell In rng
            If Not cell.HasFormula And Len(cell.Value) > 8 Then
                cell.Value = Right(cell, 10)
                Else
                cell.Value = Right(cell, 6)    [COLOR=#008000]'if 8 or less only extract 6 characters
[/COLOR]            End If
        Next cell

        
[COLOR=#008000]'Create a loop to delimit somehow
[/COLOR]For i = 7 To LastCol
    Columns(i).TextToColumns , Destination:=Cells(1, i), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True
      
Next i

but the conditional formatting only works on some of the cells even though they are all set as dates.
i found out that if i double click on the cells that are not conditionally formatted correctly, (or use F2) then press enter the formatting rules apply correctly.

a google search turned up the column delimiting idea.

 

[Norie]

So are youtrying to convert 'text' dates to real dates?

 

[cooper645]

i believe i am as the original cell contents were formatted as General containg text strings followed by a date in a csv file, so i guess stored as text. i find it odd that formatting rules apply correctly to some but no all the cells

 

[cooper645]

Thanks for pointing out what i am actually doing


i have now used the following and all is well:

'convert range to dates
Set rng = Range("G6:AF" & LastRow + 4)
    For Each cell In rng
        If Len(cell.Value) = 10 Then cell.Value = DateValue(cell.Value)
    Next cell
        
        Range("G6:AF" & LastRow + 4).Select
        Selection.NumberFormat = "dd/mm/yyyy" 'format date are as date (working)

Aaarrgghhh. Still not capturing all the dates though

 

[cooper645]

wait...... now all formats correctly, i am using run out dates so it does infact format fine. ALL IS WELL in the world

Thanks again Norie for your help.