Inserting row non integer

Getting Started
1

Hi,

I want to inserting row between the different code from listbox.

If the number is integer, its worked.
but if its string or non integer it won’t work.

Listbox1.AddRow "A01"
Listbox1.AddRow "A01"
Listbox1.AddRow "B02"
Listbox1.AddRow "B02"
Listbox1.AddRow "C03"
Listbox1.AddRow "C03"
Listbox1.AddRow "D04"
Listbox1.AddRow "D04"
Listbox1.AddRow "01"
Listbox1.AddRow "01"
Listbox1.AddRow "02"
Listbox1.AddRow "02"
Listbox1.AddRow "03"
Listbox1.AddRow "03"
Listbox1.AddRow "04"
Listbox1.AddRow "04"
Dim rowMax as Integer = ListBox1.LastRowIndex
Dim rowIndex, IDNumber, lastRowNumber as Integer

while rowIndex <= rowMax
  IDNumber = ListBox1.CellValueAt(rowIndex, 0).ToInteger
  if IDNumber > lastRowNumber then
    if lastRowNumber <> IDNumber then
      ListBox1.AddRowAt(rowIndex, "Total of item : ")
      rowIndex = rowIndex + 1
      rowMax = rowMax + 1
    end if
    lastRowNumber = IDNumber
  end if
  rowIndex = rowIndex + 1
wend

any helps ?

thanks
arief

2

In this code, where do the original values come from?
Here, you insert 2 copies of each: the total is obviously 2 for each item

Listbox1.AddRow "A01"
Listbox1.AddRow "A01"
Listbox1.AddRow "Total of item : 2"

I assume in real life you will get the values from somewhere else?
if they come from a database, do a query that gets the count() of each item, then add the items to the list followed by the total.

if they are from a text file, a simple way to count them is to use a dictionary.

3

Hi,
Actually I want to Insert total of item, if the code from next row is different from the previous one.
Total Item value comes from second column of the listbox.

The Listbox is contains 3 columns, Code, Quantity, Item Name.

The Problem is, I can’t insert row “Total of item :” if the code is string.

thanks
regards,
arief

4

Not all the rows have text that can be considered ‘a number’.

Try this, which just compares the text itself… this will allow you to insert items like ‘banana’ and it will still work by counting the rows that are the same.
Assuming the list is properly populated in some kind of order.

Listbox1.AddRow "A01"
Listbox1.AddRow "A01"
Listbox1.AddRow "B02"
Listbox1.AddRow "B02"
Listbox1.AddRow "C03"
Listbox1.AddRow "C03"
Listbox1.AddRow "C03"
Listbox1.AddRow "D04"
Listbox1.AddRow "D04"
Listbox1.AddRow "01"
Listbox1.AddRow "01"
Listbox1.AddRow "02"
Listbox1.AddRow "02"
Listbox1.AddRow "02"
Listbox1.AddRow "02"
Listbox1.AddRow "03"
Listbox1.AddRow "03"
Listbox1.AddRow "04"
Listbox1.AddRow "04"


dim last_text as string
dim runningtot as integer


ListBox1.addrow "Dummy"
for x as integer  = 0 to ListBox1.LastRowIndex
  if  ListBox1.CellValueAt(x, 0) <> last_text  then
    //found a new value
    ListBox1.celltag(x,0) = cstr(runningtot)  //tag the row as the last one
    runningtot = 1
    last_text = ListBox1.CellValueAt(x, 0) 
  else
    //same as before, so add one
    runningtot = runningtot +1
  end if
next



for x as integer  = ListBox1.LastRowIndex downto 1
  
  if ListBox1.celltag(x,0) > "" then
    ListBox1.AddRowAt(x, "Total of item : " + ListBox1.celltag(x,0) )
  end if
  
next

ListBox1.RemoveRowAt(ListBox1.LastRowIndex) //remove the dummy row
5

if your cell contains “04” toInteger returns the integer 4
if the cell contains “A04” then toInteger returns 0
so your total don’t work.
you must remove the letters at the beginning of the cell’s text

from the documentation :

Numbers are converted only if they are found at the beginning of the String. Any numbers that follow a non-numeric value are ignored.So,“1AA2” returns 1 “AA2” returns 0 “12AA54” returns 12

6

Hi,
Yes, This is works nicely. Thanks

when I do some modification. add extra row and putting the value on second column to be calculated,
the total of item is not calculated anymore.

How to calculate the second column.

Listbox1.AddRow "A01","1"
Listbox1.AddRow "A01","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "04","2"
Listbox1.AddRow "04","2"


dim last_text as string
dim runningtot as integer


ListBox1.addrow "Dummy"
for x as integer  = 0 to ListBox1.LastRowIndex
  if  ListBox1.CellValueAt(x, 0) <> last_text  then
    //found a new value
    ListBox1.celltag(x,0) = cstr(runningtot)  //tag the row as the last one
    runningtot = 1
    last_text = ListBox1.CellValueAt(x, 0) 
  else
    //same as before, so add one
    runningtot = runningtot +1
  end if
next



for x as integer  = ListBox1.LastRowIndex downto 1
  
  dim total as double
  Dim xRow,nRow,oCount As Integer
  xRow=Listbox1.ListCount
  oCount=0
  total = total + CDbl(Listbox1.Cell(x, 1))
  
  
  if ListBox1.celltag(x,0) > "" then
    ListBox1.AddRowAt(x,"")
    ListBox1.AddRowAt(x, "Total of item : " + str(total) )
  end if
  
next

ListBox1.RemoveRowAt(ListBox1.LastRowIndex) //remove the dummy row

thanks
regards,
arief

7

when I do some modification. add extra row and putting the value on second column to be calculated,
the total of item is not calculated anymore.

What do you mean ‘any more’?
Previously you counted the rows.
Now you want to sum a field that didn’t exist in the first post.

Consider: to count the rows, I add one to runningtot
You want to add ‘some other value’ to runningtot

I know you know how to get that other value.
Please try amending the code to use it instead of ‘+1’

8

yes, really sorry, Its my mistake. I didn’t include the actual listbox content. I thought it was the same as normally to calculate a listbox.

I do some modification, but Still not calculated.

Listbox1.AddRow "A01","1"
Listbox1.AddRow "A01","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "04","2"
Listbox1.AddRow "04","2"


dim last_text as string
dim runningtot as integer
dim runningtot2 as integer

ListBox1.addrow "Dummy"
for x as integer  = 0 to ListBox1.LastRowIndex
  if  ListBox1.CellValueAt(x, 0) <> last_text  then
    //found a new value
    dim total as integer
    total = total + CDbl(Listbox1.Cell(x, 1))
    runningtot=total
    
    ListBox1.celltag(x,0) = CStr(runningtot) //tag the row as the last one
    last_text = ListBox1.CellValueAt(x, 0) 
  else
    //same as before, so add one
    
    
  end if
next


for x as integer  = ListBox1.LastRowIndex downto 1
  if ListBox1.celltag(x,0) > "" then
    ListBox1.AddRowAt(x,"")
    ListBox1.AddRowAt(x, "Total of item : " + str(runningtot))
  end if
  
next

ListBox1.RemoveRowAt(ListBox1.LastRowIndex) //remove the dummy row

any way thanks for the help.

regards,
Arief

9

This line is in the wrong place. It should be in the Else section.
Try

dim total as integer   // declare the variable outside the loop, otherwise it gets erased

for x as integer  = 0 to ListBox1.LastRowIndex
  if  ListBox1.CellValueAt(x, 0) <> last_text  then
    //found a new value
    ListBox1.celltag(x,0) = total //tag the row as the last one, no need for CStr
    last_text = ListBox1.CellValueAt(x, 0) 
    total = CDbl(Listbox1.Cell(x, 1))   // initialize for next set of last_text values
  else
    //same as before, so add 
    total = total + CDbl(Listbox1.Cell(x, 1))
  end if
next
10

Hi,

Thanks for the explanation.
but I still got zero on total of item. Its still not calculating the value in column 1.

thanks
regards,
Arief

11

By adding 2 lines instead of one, you moved the rows about.
By introducing a new variable called total , and then reporting on the value of runningtot instead, the counting was not being reported.

On the assumption that maybe you now want both a count of the rows, and the sum of the values, here is working code once more.
You will see that all that was needed to sum the values was to switch from +1 to cdbl(…)
The extra variables I have added are in order to maintain 2 different running values.


Listbox1.AddRow "A01","1"
Listbox1.AddRow "A01","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "B02","1"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "C03","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "D04","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "01","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "02","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "03","2"
Listbox1.AddRow "04","2"
Listbox1.AddRow "04","2"


dim last_text as string
dim runningtot as integer
dim runningsum as double

ListBox1.addrow "Dummy"
for x as integer  = 0 to ListBox1.LastRowIndex
  if  ListBox1.CellValueAt(x, 0) <> last_text  then
    //found a new value
    ListBox1.celltag(x,0) = cstr(runningtot)  //tag the row as the last one
    ListBox1.celltag(x,1) = cstr(runningsum) 
    
    runningtot = 1
    runningsum = cdbl(ListBox1.CellValueAt(x, 1) )
    last_text = ListBox1.CellValueAt(x, 0) 
  else
    //same as before, so add one
    runningtot = runningtot + 1
    runningsum = runningsum + cdbl(ListBox1.CellValueAt(x, 1) )
  end if
next


dim t,s as string
for x as integer  = ListBox1.LastRowIndex downto 1
  if ListBox1.celltag(x,0) > "" then
//for simplicity, I grab the values we need to display **before** adding a row.
//this means you are free to add a blank row without breaking the code
//if you wish.

    t= ListBox1.celltag(x,0)
    s = ListBox1.celltag(x,1)
    
    ListBox1.AddRowAt(x, "Tot: " + t)
    ListBox1.CellValueAt(ListBox1.LastAddedRowIndex, 1) = "Sum : " +s
  end if
  
next

ListBox1.RemoveRowAt(ListBox1.LastRowIndex) //remove the dummy row
12

Thanks alot…

regards,
Arief