Stepping through yields different result then just running

Question

I have a piece of code that is used to split up the text in a cell. The data is outputted by a survey program that doesn't make use of any useful delimiter, so unfortunately converting text to columns isn't of any help to me.

I wrote this piece of code, but it turns out that the outcomes are different in two cases.

I run the code step by step until the first column is added, and then let it finish
I run the code from the execute macro's menu

In the first case the output is as I designed it to be. After a column with the header Crop: XXX (Which contains the raw data that needs to be split) there are columns for each separate entry numbered 1 to X. Data from every row starts to be split in column X and then moves as far as there are entries. Like this:

| Crop XXX    | 1  | 2  | 3  | 4  |
|-------------|----|----|----|----|
| X1,X2,X3    | X1 | X2 | X3 |    |
| X1,X2,X3,X4 | X1 | X2 | X3 | X4 |

In the second case all columns are numbered 1, and every new row enters its data before the data of the previous row. Like such:

| Crop XXX    | 1  | 1  | 1  | 1  | 1  | 1  | 1  |
|-------------|----|----|----|----|----|----|----|
| X1,X2,X3    |    |    |    |    | X1 | X2 | X3 |
| X1,X2,X3,X4 | X1 | X2 | X3 | X4 |    |    |    |

The code I use to input and number these columns is this:

    If Not UBound(inarray) = 0 Then

        For i = UBound(inarray) To LBound(inarray) Step -1
            If ws.Cells(1, col + i).Value = i Then
                If i = UBound(inarray) Then
                    Exit For
                End If
                col_to_add = col + i + 1
                Exit For
            Else
                addcol = addcol + 1
            End If
        col_to_add = col + i
        Next i

        If Not i = UBound(inarray) Then
            col1 = ConvertToLetter(col_to_add)
            col2 = ConvertToLetter(col_to_add + addcol - 1)
            ws.Columns(col1 & ":" & col2).Insert shift:=xlToRight
        End If

        If Not addcol = 0 Then
            For j = 1 To addcol

                If col_to_add = col + j Then
                    If j = 1 Then
                        ws.Cells(1, col_to_add) = 1
                    Else
                        ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1
                    End If
                Else
                    ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1
                End If
            Next j
        End If

        For k = UBound(inarray) To LBound(inarray) Step -1
            ws.Cells(row, col + k) = inarray(k)
        Next k
    End If

In this example Inarray() is a 1d array containing the below values for the first row:

| Inarray() | Value |
|-----------|-------|
| 1         | X1    |
| 2         | X2    |
| 3         | X3    |

ConvertToLetter is the following function:

Function ConvertToLetter(iCol As Integer) As String
Dim vArr
vArr = Split(Cells(1, iCol).Address(True, False), "$")
ConvertToLetter = vArr(0)
End Function

Could anyone point out why this difference occurs between scenario 1 and 2? Usually these things happen when objects aren't fully classified, but I thought I tackled that problem this time.

You still have implicit activesheet calls as far as I can see in the above. — QHarr, Oct 30 '18 at 09:38
That's a lot of code for what is for all intents and purposes, a Text-to-Columns operation. — , Oct 30 '18 at 09:47

score 3 · Accepted Answer · answered Oct 30 '18 at 09:38

The difference is because the Cells and the Range are not fully qualified. Thus, when you go step-by-step you are also selecting the correct worksheet and automatically you are not.

Whenever you have something like this:

ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1

make sure that you always write the Worksheet before the Cells() like here - ws.Cells. Or before the range - ws.Range(). Otherwise it takes the ActiveSheet or the sheet where the code is (if not in a module).

A similar issue with screenshot explanation

score 2 · Answer 2 · answered Oct 30 '18 at 10:14

TextToColumns can accomplish that split and DataSeries can put a sequence of numbers into row 1.

Sub Macro4()

    Dim lc As Long

    With Worksheets("sheet9")
        .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).TextToColumns _
                Destination:=.Cells(2, "B"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                Comma:=True, Tab:=False, Semicolon:=False, Space:=False, Other:=False
        lc = .Cells.Find(What:=Chr(42), After:=.Cells(1, 1), LookAt:=xlPart, LookIn:=xlFormulas, _
                   SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1
        .Cells(1, "B") = 1
        .Cells(1, "B").Resize(1, lc).DataSeries Rowcol:=xlRows, Type:=xlLinear, _
                                                Date:=xlDay, Step:=1, Stop:=4
    End With

End Sub

Thanks for your answer Jeeped, but unfortunately I made my example to minimal for this to work. In reality X1 etc. are strings containing `,` as well. Hence my rewrite. I split strings on an entire word, the first word of the substring. — Luuklag, Oct 30 '18 at 10:32
Nothing stopping you from splitting on a space in TextToColumns. — , Oct 30 '18 at 10:34
Unfortunately the only delimiter in my string is the word "Period", which I need to conserve in the seperate substrings as well. — Luuklag, Oct 30 '18 at 12:50

Stepping through yields different result then just running

2 Answers2

Linked