Avatar of William C Johnson
William C Johnson
Flag 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
Microsoft OfficeMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
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

William C Johnson

ASKER
This solution erases all formulas in column  F.   Does not work as designed.
gowflow

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
William C Johnson

ASKER
Do Until Loop within a table without altering the formulas
gowflow

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
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
William C Johnson

ASKER
Thank you, Syed.  This is exactly what I was looking for.  It works great.  Thanks again.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
gowflow

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
Norie

Doesn't the original code you posted also delete the formulas in column F.
William C Johnson

ASKER
No, it doesn't.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Saqib Husain

The author acknowledged my solution.