How to use loop IF, if range is unknown
Requirement:
The user would like to create a macro. The user is suggesting the use of do while loop but doesn't know exactly how in IF formula case would work. Normally, when range is known the user can use for next loop but in the user's report amount of data changes everyday.
The normal formula looks like =IF(AND(A2>26000000;A2<60500000);"AUTOMATIC";"MANUAL")
The user needs to have it transformed into vba code.
| A~VBELN | Manual/Automatic |
| 3448113 | Manual |
Solution:
Delete the formulas in column B and try this macro:
Code:
Sub Test()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=">26000000", Operator:=xlAnd, Criteria2:="<60500000"
Range("B1:B" & LastRow).SpecialCells(xlCellTypeVisible) = "AUTOMATIC"
Range("A1").AutoFilter
Range("B:B").SpecialCells(xlCellTypeBlanks) = "MANUAL"
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to loop a macro with various length columns |
| How to change reference columns in another worksheet using VBA looping |
| How to loop each row if there is data after green colour cell then delete |
| How to copy from cell into next empty cell and loop through |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
