Link to home
Create AccountLog in
Avatar of William C Johnson
William C JohnsonFlag for United States of America

asked on

Do Until Loop within a table

I want to run this macro on every row in the Product table.  What is the best way to loop this macro from top to bottom of the Product table?  The number of lines will always vary.

Sub Reset_Part2()
'
' Reset_Part2 Macro
'
' Test for ORDER NOW and reset values for each row in the Product table.Craft-Paint-Inventory.xlsmCraft-Paint-Inventory.xlsm
    If ActiveCell.Value = "ORDER NOW" Then
        ActiveCell.Offset(0, -3).Range("Products[[#Headers],[Code]]").Select
        ActiveCell.FormulaR1C1 = "0"
        ActiveCell.Offset(0, 1).Range("Products[[#Headers],[Code]]").Select
        ActiveCell.FormulaR1C1 = "0"
        ActiveCell.Offset(0, 2).Range("Products[[#Headers],[Code]]").Select
    End If
    ActiveCell.Offset(1, 0).Range("Products[[#Headers],[Code]]").Select
       
End Sub
Avatar of Norie
Norie

Try this.
Sub Reset_Part2()
Dim arrData As Variant
Dim idxRow As Long

    arrData = Sheets("Products").ListObjects(1).DataBodyRange.Value
    
    For idxRow = 1 To UBound(arrData, 1)
    
        If arrData(idxRow, 7) = "ORDER NOW" Then
            arrData(idxRow, 4) = 0
            arrData(idxRow, 5) = 0
            arrData(idxRow, 6) = 0
        End If
    Next idxRow
    
    Sheets("Products").ListObjects(1).DataBodyRange.Value = arrData
        
End Sub

Open in new window

Avatar of William C Johnson

ASKER

This solution erases all formulas in column  F.   Does not work as designed.
This solution erases all formulas in column  F.   Does not work as designed.
Can you please advise what you want to achieve ? Rather than posting a code and asking a partial question ?
Gowflow
Do Until Loop within a table without altering the formulas
What I understood is that you want to loop through the table and for every row where Col G is: ORDER NOW change something in Col D and E but you are not saying what you want to change in D and E.

Please advise and I will give you what you want.
Gowflow
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you, Syed.  This is exactly what I was looking for.  It works great.  Thanks again.
Yes coyboywm but you have attributed the points to me not Syed  you should correct this !!!! and still you did not say what you want to change in D E !!!
Anyway.

GOwflow
Doesn't the original code you posted also delete the formulas in column F.
No, it doesn't.
The author acknowledged my solution.