William C Johnson

asked on 4/18/2020

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.xlsm Craft-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

Last Comment

Saqib Husain

8/22/2022 - Mon

Norie

4/18/2020

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

4/18/2020

ASKER

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

gowflow

4/18/2020

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

4/18/2020

ASKER

Do Until Loop within a table without altering the formulas

gowflow

4/18/2020

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

4/18/2020

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

4/18/2020

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

4/18/2020

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

4/18/2020

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

William C Johnson

4/19/2020

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

9/21/2020

The author acknowledged my solution.