Code Skipping Second Cell, Not Supposed To

Question

This code is a part of bigger code that takes words from a listbox and places into another listbox, which with this code separates the words in the listbox and establishes into words that are able to be inserted into a cell, for some reason second strsplt is not showing, everything else is working very well, it's just this one, I need help with and there is no error that is thrown out. I've looked it over with F8 and breakpoints and the problem seems to be with

If ii < .ColumnCount - 1 Then
    str = str & .List(i, ii) & vbCrLf
Else
    str = str & .List(i, ii)
End If

The Whole Code:

With Me.selecteditems
    ThisWorkbook.Sheets(9).Range("A:B").ClearContents
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            found = True
            For ii = 0 To .ColumnCount - 1
            ReDim strsplt(0 To i)
                If str = "" Then
                    str = .List(i, ii) & vbCrLf
                Else
                    If ii < .ColumnCount - 1 Then
                        str = str & .List(i, ii) & vbCrLf
                    Else
                        str = str & .List(i, ii)
                    End If
                End If
            Next ii
            message = "How much" & vbCrLf & str & "?" & vbCrLf
            title = "Amount"
            defaultval = "1"
            quantity = InputBox(message, title, defaultval)
            strsplt = Split(str, "*")
        End If
        'On Error Resume Next
        With ThisWorkbook.Sheets(9)
            .Range("A" & (i + 1)).Value = strsplt(i)
            .Range("B" & (i + 1)).Value = quantity
        End With
        'On Error GoTo 0
    Next i
End With

EDIT: The way it looks like using debug.print str

item1
item2 item3 item4 ...

Just a heads up, it is common practice to use `j` instead if `ii` as an inner for loop if you are going to be nesting your loops! — Jsleshem, Aug 14 '17 at 15:01
Multi-line cells in a worksheet use line feeds (LF), not carriage returns (CRLF). The 0x013 (CR) character is typically stripped out leaving oly the 0x010 (LF) character. — , Aug 14 '17 at 15:26
I need it to, in short, go to the next line, which thinking about, I should have done `vbNewline` — MaxAttack102, Aug 14 '17 at 17:14

Vityata · Answer 1 · 2017-08-14T18:33:31.630

0

Try a bit brute forcing like this:

If ii < .ColumnCount - 1 Then
    str = str & .List(i+1, ii) & vbCrLf
Else
    str = str & .List(i+1, ii)
End If

I have changed i to i+1 in your code. Then debug again. If it does not work, try i-1, ii+1, ii-1. One of these will work and it may give an out of range error. Then fix the array length and have fun.

edited Aug 14 '17 at 18:33

answered Aug 14 '17 at 15:07

Vityata

42,633
8
55
100

It just gives me a subscription out of range – MaxAttack102 Aug 14 '17 at 17:10
@MaxAttack102 - but before this? Is the 2. Line present? Do you debug with F8? Did you try all 4 possibilities? – Vityata Aug 14 '17 at 18:18
Yeah 2 of them gave me sub our of range and one gave me something about the list property. none said anything about the overflow, I can try again – MaxAttack102 Aug 14 '17 at 18:32
Just try again all 4 and put a `debug.print str` after the `end if`. Out of range is the correct error, not overflow. :) – Vityata Aug 14 '17 at 18:33
When you say fix the array, you mean put i-1 or whichever one works on it in the `Redim()` – MaxAttack102 Aug 14 '17 at 18:42
@MaxAttack102 - the out of range error is because the array is smaller than the iterator. Thus, you have to increase the size of it a bit. I mean `ReDim strsplt(0 To i+1)`. – Vityata Aug 14 '17 at 18:44
should I return back to `(i,ii)` – MaxAttack102 Aug 14 '17 at 19:32
It doesn't like that that either , it throws out Subscription out of Range when I put it in the array – MaxAttack102 Aug 14 '17 at 19:34
Also, I don't know if this might help, but it always show the first item in it's own line and the rest of the items in the same line – MaxAttack102 Aug 14 '17 at 19:39
Found the problem, Thank you though, it was `If str = "" Then` . I took it out and it fixed it – MaxAttack102 Aug 14 '17 at 20:05
1

@MaxAttack102 - congrats! :) In general, consider using `vbNullString` in stead of `""` - https://stackoverflow.com/questions/32435320/is-there-any-difference-between-vbnullstring-and – Vityata Aug 14 '17 at 20:30

Code Skipping Second Cell, Not Supposed To

1 Answers1