Identify row number of the data in a listbox

[dss28]

I have a listbox which is filled with data transferred from textboxes in the userform and not from sheet. The listbox has multiple rows and columns.
I have also added a code to delete a row before final saving of the data to excel sheet.

Hence if I try to give / add a row number through a textbox in the userform to rows, it might get altered if the user deletes any of the rows before final saving the data to sheet.

e.g. if initially I transferred data in 5 rows to the listbox1, with Sr. No. 1 to 5 assigned to them through a textbox, but then decide to delete 3rd and 4th row so finally it will have only three rows numbered 1st, 2nd and 5th (now 3rd after deletion of two row) to save to the sheet. Here the sequence of row number is not proper. I want to save them as Sr. No. 1, 2, 3

My idea is to assign row number by any code to these three final rows so that the same can be captured in one of the columns (say ColumnH) in the sheet. is it possible?
Can anyone help..

 

[dss28]

@dss28
hi
just guess . not guaranteed if this works for you
try this
option1
delete this

        .Offset(0, 0).Value = "=Row()-1"

 With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
           .Formula = "=ROW()-1"
           .Value = .Value
           End With

option2

            listbox1.List(lindex, 0) = lindex + 1

I hope this help

I replaced my code between '+++++ these two lines with the option 1 but it numbered the CellA1 by 0 and A2 by 1 when I transferred two row data to sheet for first entry but thereafter it left blank all during subsequent entries.
i could not figure out where to place the option 2 code.

 

[abdelfattah]

I supposes the row1 is the headers about option1. the data should begins from A2 . am I right?
about option2 I 'm not sure your code how work
but change this

        ws.Cells(eR, 1).Value = iRow + 1 ' irow no.

to this

            listbox1.List(lindex, 0) = lindex + 1

 

[dss28]

I supposes the row1 is the headers about option1. the data should begins from A2 . am I right?
about option2 I 'm not sure your code how work
but change this

        ws.Cells(eR, 1).Value = iRow + 1 ' irow no.

to this

            listbox1.List(lindex, 0) = lindex + 1

i could resolve my irow issue for column A based on my code given earlier.

however the original question - the sequential numbering of the rows while transferring the data from listbox to sheet is unresolved.
If i use a loop :

For ii = 0 To ListBox1.ListCount 
ws.Cells(eR, 8).Value = ii + 1 
next i

i
it prints the no. of rows in listbox +1 value in all the rows being transferred to sheet in ColumnH but not in increment value 1,2,3,4 etc

 

[Norie]

How/where are you using that code?

 

[dss28]

How/where are you using that code?

sandwiched like this :

ws.Cells(eR, 7).Value = ListBox1.Column(12, i) 

 For ii = 0 To ListBox1.ListCount   
ws.Cells(eR, 8).Value = ii + 1 ' sr no 
next ii

 ws.Cells(eR, 9).Value = ListBox1.Column(1, i)

 

[Norie]

Add this declaration at the top of the code,

Dim rowId As Long

and replace that code with this.

rowId = rowId + 1
ws.Cells(eR, 8).Value = rowId  ' sr no

 

[dss28]

Add this declaration at the top of the code,

Dim rowId As Long

and replace that code with this.

rowId = rowId + 1
ws.Cells(eR, 8).Value = rowId  ' sr no

No sir,
I dont want to number the columnH cells sequentially. but it should be linked to the rows present in the listbox each time it is saved on the sheet.

With above code for first set of data addition - if my listbox has three rows - it adds 1,2,3 in column H - perfectly ok

In the second instance of adding another data set - it should again evaluate the number of rows in the listbox now and again start numbering from 1 to the no. of rows in list box. and so on.

 

[Norie]

Did you try what I suggested?

Each time you transfer data from the listbox to the sheet rowId will increment from 0.

For example if 4 rows were transferred to the sheet the values for rowId would be 1, 2, 3, 4.

If you subsequently transferred 10 rows the values for rowId would be 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.

 

[dss28]

Did you try what I suggested?

Each time you transfer data from the listbox to the sheet rowId will increment from 0.

For example if 4 rows were transferred to the sheet the values for rowId would be 1, 2, 3, 4.

If you subsequently transferred 10 rows the values for rowId would be 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.

my mistake and great learning on placement of code lines

first I added the Dim statement below option explicit which resulted in sequential numbering 1,2,3,4,5,6,7,8 when data was saved in two sets first 3 rows, second 5 rows.

however upon your second post above ... it made me realize that I must have done wrong may be wrongly placed the dim statement

if Dim statement is added in the code, it gives me the result I wanted. 1,2,3,1,2,3,4,5...... at last .......

strange... but will like to know the reason behind this sir dim statement placement


thanks a lot sir ...

 

[Norie]

By placing the declaration at the top of the userform module the variable rowId would be initialized to 0 when the userform was loaded and would be incremented from that until the userform was unloaded.

By declaring it within the sub it would be initialized to 0 every time the sub was executed.

Hope that makes some kind of sense.