Unexpected behavior in VBA code

[MyGiG]

Hello, I've written some code that converts 1500 data points to about 200 (copies every 20th data point to the right), since Exel can't use more than 250 to draw a graph. So the wird part is, if I use the code below, everything works fine, but if I devide the result by 10, I get "0" in the fields.

[/URL][/IMG]

Sub Gumb_graf()


navpicno = 2
navpicnoBrezPreskoka = 2
obmocjePolnihCelic = 2

'prešteje polne celice leve tabele
Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""
obmocjePolnihCelic = obmocjePolnihCelic + 1
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop

'določanje celic, katere se bodo zajele
vsakiKolikiPodatek = Round(obmocjePolnihCelic / Worksheets("1").Cells(16, 16), 0)

navpicnoBrezPreskoka = 2

'praznjenje desne tabele, da se ne prekriva
Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) <> ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = ""
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop

navpicnoBrezPreskoka = 2
navpicno = 2

Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = Worksheets("List1").Cells(navpicno, 5)
Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = Worksheets("List1").Cells(navpicno, 2)
Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = Worksheets("List1").Cells(navpicno, 3)
Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = Worksheets("List1").Cells(navpicno, 4)
navpicno = navpicno + vsakiKolikiPodatek
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop


End Sub

Now, if i devide the result by 10 (code below) I get zeros at the end

Sub Gumb_graf()


navpicno = 2
navpicnoBrezPreskoka = 2
obmocjePolnihCelic = 2

'prešteje polne celice leve tabele
Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""
obmocjePolnihCelic = obmocjePolnihCelic + 1
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop

'določanje celic, katere se bodo zajele
vsakiKolikiPodatek = Round(obmocjePolnihCelic / Worksheets("1").Cells(16, 16), 0)

navpicnoBrezPreskoka = 2

'praznjenje desne tabele, da se ne prekriva
Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) <> ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = ""
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop

navpicnoBrezPreskoka = 2
navpicno = 2

Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""
Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = Worksheets("List1").Cells(navpicno, 5)
Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = Worksheets("List1").Cells(navpicno, 2) / 10
Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = Worksheets("List1").Cells(navpicno, 3) / 10
Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = Worksheets("List1").Cells(navpicno, 4) / 10
navpicno = navpicno + vsakiKolikiPodatek
navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
Loop


End Sub

[/URL][/IMG]

Does anyone know why this happens?

 

[pgc01]

Hello, I've written some code that converts 1500 data points to about 200 (copies every 20th data point to the right), since Exel can't use more than 250 to draw a graph.

Hi

There's no limit in excel current and last versions for the number of points in a chart series (other than available memory).
Even in old versions like excel 2007 you could have 32000 points in a chart series.

In your case since you just have 1500 points per series I don't think that that will be a problem.

Please clarify.

 

[MyGiG]

First of all, thank you for taking the time to look at my issue.
Well, I’ve tried using all of the data, but when the graphs are drawn (~500 data points per force plus ~500 for distance) it only draws for the first ~250. But in anycase, taking less data points is actually helpful for me, since the graphs are only for visual representation and are more pleasing to the eye that way.
Do you have any idea what is causing the “0”s i keep getting?

 

[MyGiG]

If it helps, on the left is data i get out of a machine, to the right is data, that I use to draw 3 graphs. But that is not the problem, the problem is, when i run the code it works fine, but when I add “/10” to the output, zeros happen (2nd picture).

 

[pgc01]

Hi

I suspect you are performing the division with empty cells.

Let's say that B1 is empty.

If you use:

Range("A1").Value = Range("B1").Value

then A1 will also be empty.

but if you use

Range("A1").Value = Range("B1").Value / 10

then vba, since you want to perform an arithmetic operation will use the default 0 value for the cell value, and then you get also 0 in A1

For ex., if you'd use

Range("A1").Value = Range("B1").Value + 1

you'd get 1 in A1.

Please comment.

 

[pgc01]

Also, if you want to check excel limits for charts, see the section

Charting specifications and limits

here:

https://support.office.com/en-us/ar...d-limits-1672b34d-7043-467e-8e27-269d656771c3

 

[MyGiG]

Thank you for the reply.
I've allready considered that and double checked it. the line

[COLOR=#333333][I]Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""[/I][/COLOR]

looks for data in colum A and if there is data, there should also be data in colum B, C, D and E (since that's how the machine outputs data). so if the cells is empty the code shouldn't execute and there for always devide cells with a number in it.
thanks for the link, will check it.

 

[pgc01]

OK.

Try this simple test

Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""
             Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = Worksheets("List1").Cells(navpicno, 5)
             Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) =[B][COLOR=#800000] "Test"[/COLOR][/B]
             Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = Worksheets("List1").Cells(navpicno, 3) / 10
             Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = Worksheets("List1").Cells(navpicno, 4) / 10
        navpicno = navpicno + vsakiKolikiPodatek
        navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
    Loop

 

[MyGiG]

Great idea, haven't thought of that yet. Will do it tomorrow at work and post the results!

 

[MyGiG]

I tested it and here are the results.
When using the code:

    Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""        
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = Worksheets("List1").Cells(navpicno, 5)
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = Worksheets("List1").Cells(navpicno, 2) / 10
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = "Test"
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = Worksheets("List1").Cells(navpicno, 4) / 10
        navpicno = navpicno + vsakiKolikiPodatek
        navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
    Loop

comes the following output:

and if I use this code:

    Do While Worksheets("List1").Cells(navpicnoBrezPreskoka, 1) <> ""        
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 16) = Worksheets("List1").Cells(navpicno, 5)
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 17) = Worksheets("List1").Cells(navpicno, 2)
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 18) = "Test"
        Worksheets("List1").Cells(navpicnoBrezPreskoka, 19) = Worksheets("List1").Cells(navpicno, 4)
        navpicno = navpicno + vsakiKolikiPodatek
        navpicnoBrezPreskoka = navpicnoBrezPreskoka + 1
    Loop

I get this as output:

So yes, there is something wrong with my loop. Thanks for the help! :D