1

I'm looping through columns and storing them in values s and s1. I need to add count order for as many lines as it's needed for which I'm trying to use j index. Getting picture like this all the time:

Current result

And the desired result should be as follows:

enter image description here

s = ""
s1 = ""
j = 1
Do            
s = j & ". " & s & Workbooks(Filename).Sheets(1).Cells(i, 2).Offset(1, 0).Value & vbCrLf
s1 = j & ". " & s1 & Workbooks(Filename).Sheets(1).Cells(i, 3).Offset(1, 0).Value & vbCrLf
i = i + 1
j = j + 1            
Loop While Workbooks(Filename).Sheets(1).Cells(i, 3).Value <> ""
Vityata
  • 42,633
  • 8
  • 55
  • 100
Adren
  • 83
  • 7

1 Answers1

1

The code should be incremented like this:

s = s & j & ". " & Cells(i, 2).Offset(1, 0) & vbCrLf

Thus, the s value is incrementing over itself.

In general, whenever such problems appear and it is tough to understand why a string is being formatted, try to debug step-by-step. E.g., write Stop on the code and print the string, which is avialable up to now. Like this:

Do
    s = s & j & ". " & Cells(i, 2).Offset(1, 0) & vbCrLf
    s1 = j & ". " & s1 & Cells(i, 3).Offset(1, 0) & vbCrLf
    i = i + 1
    j = j + 1
    Debug.Print s
    Stop
Loop While Cells(i, 3) <> ""

Then the magic becomes easier to understand. Pressing F8 and checking the current values with hovering over them in VBE is another option for step-by-step debugging.

Debugging VBA, Locating Problems, and Troubleshooting Methods

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    Thank you, it works! You wouldn't believe, F8 was the first thing I tried to do, I also dragged s and s1 values by alt clicking to watchlist and checked their values at immediate window, still dont understand how I would come up with the right solution, guess it's more of 'practice' thing. One last question: do you know why it's adding last index as empty? https://imgur.com/j2Zko69 – Adren Feb 19 '19 at 10:40
  • 1
    @MDoskarin - it is reading from here - `Cells(i, 2).Offset(1, 0)` and the condition is `Cells(i, 3) <> ""`. If you change the condition of the loop like this - `Do While Cells(i, 2).Offset(1, 0) <> ""`, it would be ok. – Vityata Feb 19 '19 at 10:45
  • 1
    Gosh, simplicity is genius. Thank you! – Adren Feb 19 '19 at 10:49