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
Last Comment
Saqib Husain
8/22/2022 - Mon
Norie
Try this.
Sub Reset_Part2()Dim arrData As VariantDim 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 = arrDataEnd Sub
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
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.
Open in new window