Do While Not IsEmpty(Range("A1")) condition evaluation failure

Question

I have a problem with this code, which I made specifically to "load" data in an already create graph, from 3 associated columns like this:

Data1 X1 Y1
Data2 X2 Y2
Data3 X3 Y3

Its intended behavior is very simple, It selects the active cell, and enters a loop: select the first graph of the sheet, evaluate if active cell is not empty, if it is, it should go outside the loop and end the macro, if it isn't, it then adds new series to it, puts Y as abscissa value and puts X as ordinate value, increments a tracking value which allows to increase the number of the next series in a graph (it was initialized outside the loop as 1), changes the row of the active cell with the one immediately below.

The problem is that it never goes outside of the loop (and eventually crashes because you can't add more that 256 series to a graph, which I improved the code in another version with a if condition to make it better, it works; but still adds empty series; which are very tedious to delete and may mess with things if left there).

It seems that the condition: Do While Not IsEmpty(Range("A1")) fails to evaluate correctly. I tried to use Do While Not IsEmpty(Range("A1").value) instead, or Do While IsEmpty(Range("A1")); Do While Not IsEmpty(Selection) (which send back an error about global), I tried a lot of other things but I think my type of value is ok, it is a boolean, so it should work as is. I also tried on a new workbook with a new sheet and only one line to be sure nothing had messed with cells before and would have changed their state so they would not be empty for the program, but actually while I thought about the most evident mistakes, I still fail to obtain the correct behavior.

Sub ScatterSeries()

    ActiveCell.Offset(0, 0).Range("A1").Select
    Serie = 1
    Do While Not IsEmpty(Range("A1"))
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(Serie).Name = ActiveCell
        ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2)
        ActiveChart.SeriesCollection(Serie).Values = ActiveCell.Offset(0, 1)
        Serie = Serie + 1
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop

End Sub

I read few references like that https://www.udemy.com/blog/excel-vba-do-while/ or http://www.fontstuff.com/ebooks/free/fscodeloops.pdf but I fail to see what I am doing differently, which would cause the program to be wrong. Could anyone please give me insights about that? I feel really stupid now. Thanks.

(I must say that I am a beginner with vba, so it is possible I made very obvious mistakes, I just run some actions to see what would appear in a macro, then tweaked this with scraps of code read in examples here and there (I still plan to learn properly the language but I had to solve the problem for today...:/)

Answer 1

IsEmpty() is used in VBA to check if a variable has been assigned a property, not to check if a cell is blank. Also your offset method isn't properly declared.

Use something like this:

Sub ScatterSeries()

Range("A1").Activate

Serie = 1

While Not ActiveCell = ""

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(Serie).Name = ActiveCell.Value
    ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2).Value
    ActiveChart.SeriesCollection(Serie).Values = ActiveCell.Offset(0, 1).Value

    Serie = Serie + 1

    ActiveCell.Offset(1, 0).Activate
Wend

End Sub

Also, you should always specify the .Value property of the range object (Yes, I know it's the default method of a Range object) but when you are testing a condition on the value, you want to be sure that it's definitely looking at the value and nothing else.

Answer 2

Tried this as a comment but it was too long.

It looks like the problem is an infinite loop. The cell A1, I assume, has something in it (likely the text Data1?) and nothing in your code ever looks anywhere else or changes the value in this cell.

Do While Not IsEmpty(Range("A1"))

You already have an integer "counter" named Serie, so I would suggest just altering the Do While to be

Do While Not IsEmpty(Range("A1").Offset(Serie-1,0))

The reason for the minus one is Serie is set to 1 initially and you actually want offset zero in your first pass.

If this doesn't solve your problem post again and I can take a better look later in the day.