For each listrow - update data

[dotsent]

HI! I'm a beginner (at best) with VBA loops and would need to adjust data in Excel table rows, depending on a single column cell value. I want to loop through all rows for this table and adjust data depending on value in 3rd column ("Check"). If column "Check" value is 1, insert value "Disable" to 2nd column ("Status"). Would be nice to make the code a little bit more advanced, so that under the same condition, "Check" = 1 would also clear contents from 4th column ("Quantity") but if one thing works, it's already great.

I think I should use For Each listrow function here and run IF-clause inside each loop, but as simple as it looks, I'm not sure how to address correct listcolumn within a single listrow (e.g. address correct cell). Thanks!

Sub Update_listrow()

Dim lstobj As ListObject
Dim lstrw As ListRow

Set lstobj = ActiveSheet.ListObjects("Table1")

For Each lstrw In lstobj.ListRows

'If...

Next lstrw

End Sub

.

 

[Norie]

You could try something like this which uses the column headers, i.e. Check, Quantity and Status, to identify the columns of interest as we loop through the rows.

Option Explicit

Sub Update_Table()
Dim lstobj As ListObject
Dim lstrw As ListRow

Set lstobj = ActiveSheet.ListObjects("Table1")

    For Each lstrw In lstobj.ListRows

        If Intersect(lstrw.Range, lstobj.ListColumns("Check").Range).Value = 1 Then
            Intersect(lstrw.Range, lstobj.ListColumns("Status").Range).Value = "Disable"
            Intersect(lstrw.Range, lstobj.ListColumns("Quantity").Range).Clear
        End If

    Next lstrw

End Sub
[/vba]

 

[dotsent]

Thank you very much! That's exactly what I needed. Haven't come across Intersect function before.