problem with Updating alll of the values of a listcolumn

[mahhdy]

Hi,
I want to update values of ID row of my list box by adding 1 to them. But VBA shows me error. Can anyone help?

Sub id_update()
    
    Dim mn As ListColumns
    Set mn = Worksheets("ShipReceive").ListObjects("tbl").ListColumns(1).DataBodyRange
        For Each Cell In mn
            Cell.Value = Cell.Value + 1
        Next
End Sub

 

[Sektor]

Your mn variable is of type ListColumns, but DataBodyRange is of type Range.

 

[mahhdy]

Your mn variable is of type ListColumns, but DataBodyRange is of type Range.

You are absolutely right, I Already figured it out, But the procedure takes a long time to run. Is there any lighter/ faster way to do so?
in my list there are about 5000 rows of data.
Tnx

 

[Sektor]

1. Insert 1 into some cell outside ListBox.
2. Ctrl+C
3. Select desired range.
4. Go to Paste Special and select Values (in Paste section) and Add (in Operation section)
5. Press OK.

 

[mahhdy]

I think you know that I am working on a procedure, and in VBA mode.
I was working on that too.
Now I am trying to send the number to the clipboard and then paste special it.
Yours,
M

1. Insert 1 into some cell outside ListBox.
2. Ctrl+C
3. Select desired range.
4. Go to Paste Special and select Values (in Paste section) and Add (in Operation section)
5. Press OK.

 

[Sektor]

' Insert 1 somewhere (in this case - AA1 cell).
Range("AA1").Value=1
Range("AA1").Copy
' Add one to each cell in DataBodyRange
Worksheets("ShipReceive").ListObjects("tbl").ListColumns(1).DataBodyRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd