Count columns prior to TextToColumns 1

[waubain]

I am doing Text to Columns on an unknown number of columns. Columns 1-11 are always "general", but columns 12 and higher need to be text. So far the max number has been 22 columns. Making the max number some large number seems inefficient.

Is there a way to count the max number of columns before Text to Column conversion and reflect that number in the array?

Sub Text2Columns()

Dim textArray(0 To 21) As Variant
Dim i As Long

    For i = 0 To 11
        textArray(i) = Array(i + 1, 1)
    Next i
    For i = 12 To 21
        textArray(i) = Array(i + 1, 2)
    Next i

    objWB.Worksheets(1).columns("A:A").TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
    FieldInfo:=textArray, TrailingMinusNumbers:=True

End Sub

You don't know what you don't know...

 

[SkipVought]

Hi,

"Seems?"

So what's up with the array?

Why 21, when, "so far..." Could be low???

I'd do this

MaxCols = ActiveSheet.UsedRange.Columns.Count

How inefficient is that?

Skip,

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

 

[waubain]

Skip, thanks for the response, but maybe I am unclear in my request. Here is more background.

In our legacy system, patient instructions are in a memo field. When you pull data from the system, the memo field is split into fields of 70 characters each delimited by a "|" character(I have no control over this, it was hard coded many years ago). If the provider has entered an math operator(*-/+=) into the instructions and by chance the it is the first character of a split string Excel seems to make that string into a function by adding an equal sign to the front. When I am concatenating all of those strings back together then I get a type mismatch and the sub fails. Prior to the TextToColumn conversion, all of the information is in column A delimited by the "|", so I am not sure the max number of columns. I cannot wait to count columns after the conversion to columns, as you suggest, since Excel will already have added an = to the front of the string. To date the longest patient instructions was a 760 characters or 11 columns starting at column 12("L") and going to column 22("V"). There is always something in column L, the rest depend on how long are the instructions.

Clear as mud?



You don't know what you don't know...

 

[SkipVought]

would this work for you?

    Dim r As Range, a, i As Integer
    
    For Each r In Range([A1], [A1].End(xlDown))
        With r
            a = Split(.Value, "|")
            For i = 0 To UBound(a)
                Select Case Left(a(i), 1)
                    Case "=", "-", "+", "/", "*"
                        With .Offset(0, i)
                            .NumberFormat = "@"
                            .Value = a(i)
                        End With
                    Case Else
                        .Offset(0, i).Value = a(i)
                End Select
            Next
        End With
    Next

Skip,

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

 

[waubain]

Skip, that will work for me, Thank you.

In continuing with my tradition of making it more complicated than it needs. I was able to count the number of "|" characters for my upper bound of the array before I saw your post.

Sub Text2Columns()

Dim x As Range
Dim CountRange As Range
Dim intLastRow As Long
Dim textArray() As Variant
Dim i As Long
Dim j As Long
Dim Counter As Long

    intLastRow = objWB.Worksheets(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set CountRange = objWB.Worksheets(1).Range("A2:A" & CStr(intLastRow))
    i = 0

    For Each x In CountRange
                        
    Counter = Len(x) - Len(Replace(x, "|", ""))
        If Counter > i Then
            i = Counter
        End If
    Next x

ReDim textArray(1 To i)
    For j = 1 To 11
        textArray(j) = Array(j, 1)
    Next j
    For j = 12 To i
        textArray(j) = Array(j, 2)
    Next j

    objWB.Worksheets(1).columns("A:A").TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
    FieldInfo:=textArray, TrailingMinusNumbers:=True

End Sub

You don't know what you don't know...