PDA

View Full Version : VBA Dynamic Loop in Columns and Rows to find and copy values



Nilton
01-09-2021, 12:47 PM
Hi,good morning!


I'm new in VBA and my english isn't that good so, please, be patient with me =D

I need to create a multi loop for the table below in the image. This is the logic of the loop:




The loop need to find and copy all the rows where (for instance): If Bat=1 and Bet=1 and Bit=F and Bot = 1 and But=1, copy all the rows that meet this condition and create and paste the whole row in another sheet.
It needs to be dynamic because despite the structure of the table will always be the same, the data changes everyday, and I will need to create one loop for each "categorical" row, for instance: 1-1-F-1-1, 1-1-J-1-1 and so on.


I really need your help to build just one, because I tried this but it didn't work:
Option Explicit


Sub Test()


Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("base")
Dim ARow As Long, BRow As Long, CRow As Long, DRow As Long, ERow As Long, MyCellA As Range, MyCellB As Range, MyCellC As Range, MyCellD As Range, MyCellE As Range, Names As String
ARow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
BRow = ws.Range("B" & ws.Rows.count).End(xlUp).Row
CRow = ws.Range("C" & ws.Rows.count).End(xlUp).Row
DRow = ws.Range("D" & ws.Rows.count).End(xlUp).Row
ERow = ws.Range("E" & ws.Rows.count).End(xlUp).Row


For Each MyCellA In ws.Range("A2:A" & ARow)
For Each MyCellB In ws.Range("B2:B" & BRow)
For Each MyCellC In ws.Range("C2:C" & CRow)
For Each MyCellD In ws.Range("D2:D" & DRow)
For Each MyCellE In ws.Range("E2:E" & ERow)
If MyCellA = 2 And MyCellB = 3 And MyCellC = "F" And MyCellD = 1 And MyCellE = 1 Then
MsgBox "Category 1 Achieved!"
End If
Next MyCellE
Next MyCellD
Next MyCellC
Next MyCellB
Next MyCellA


End Sub

27700

p45cal
01-09-2021, 07:56 PM
Supply a workbook with these data in, not a picture.
Do you want new sheets every time, or adding data to existing sheets if those category sheets already exist?