Help needed with VBA to iterate through a worksheet
I am trying to make a macro that will check an export and copy certain cells if specific requirements are met.
In a nutshell, I want to iterate through the cells in column P. If Px=0, then go on to cell P(x+1) and check the value. When Px=1 then I need to iterate through column N to find N(x+y)=1. Once this condition is met I want to copy the range Ax:I(x+y) to another sheet within the same workbook.
After copying I want the iteration to go on, checking the conditions every time and copying when necessary, until it reaches the end. Every time it copies to the next sheet, I would like the copy to be underneath the previous copy.
My question is, can this be done with VBA? I haven't touched VBA in decades, and so am quite rusty. I'm not asking that anyone solve this, but rather that they point me in the right direction. Which VBA functions should I considering using, etc.?
Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
Posts
30,916
Re: Help needed with VBA to iterate through a worksheet
What is the significance of "1" in column P as your post "suggests" that column C could be used to delimit your data (assumimg I have understood your requirement). Adding expected results in Sheet1 would help
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Re: Help needed with VBA to iterate through a worksheet
I'm only interested in suppliers that have a date attributed to them (in the attached example, the supplier on line 7 has a date in cell I7; the suppliers on lines 10 & 14 have no date, so they don't interest me). Given that the export has no formatting I had to come up with the system of 0s and 1s so that eventually the loop can identify which cells in column I have dates in them. Does that make sense?
Re: Help needed with VBA to iterate through a worksheet
Originally Posted by Br4c4r4
... to iterate through the cells in column P. If Px=0, then go on to cell P(x+1) and check the value. When Px=1 then I need to iterate through column N to find N(x+y)=1. Once this condition is met I want to copy the range Ax:I(x+y) to another sheet within the same workbook ...
The data in your example is not very representative, but try the code below (you must have a "Result" sheet in your workbook):
(not tested)
Re: Help needed with VBA to iterate through a worksheet
Originally Posted by Br4c4r4
Does that make sense?
Hello. These helper formulas are useful in explaining the problem, but should not necessarily be used in a macro.
Here is another way to solve your problem:
PHP Code:
Sub Macro8()
Dim ws As Worksheet, LR&, a, Q&, i&, C As Range, D As Range
Application.ScreenUpdating = False
With ActiveSheet
LR = .Cells(Rows.Count, "G").End(xlUp).Row
a = Application.Transpose(Evaluate("IF( (C7:C" & LR & "<>"""")*(I7:I" & LR & "<>""""), Row(C7:C" & LR & "))"))
a = Filter(a, False, False): Q = UBound(a)
If Q = -1 Then Exit Sub
Set ws = Worksheets.Add(After:=Worksheets(.Name))
For i = 0 To Q
Set C = ws.Cells(Rows.Count, "G").End(xlUp)(2, -5)
C.Range("A1:I1") = ">----------<"
Set D = .Range("G" & a(i), "G" & LR).Find(What:="Amount BOL-No", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext)
.Range("A" & a(i), "I" & D.Row).Copy C(2)
Next
End With
ws.Columns("a:i").AutoFit
End Sub
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
LinkBack URL
About LinkBacks
Help needed with VBA to iterate through a worksheet
Register To Reply
Originally Posted by Br4c4r4
Bookmarks