how to make a loop that filters based on a table?

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
Code:
A           B                C
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl190, width: 64"]Column[/TD]
[TD="class: xl190, width: 64"]Exclude[/TD]
[TD="class: xl190, width: 64"]Label[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]3[/TD]
[TD="class: xl192"]*9/1/2016*[/TD]
[TD="class: xl191"]DONE[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]2[/TD]
[TD="class: xl193"]*342*[/TD]
[TD="class: xl193"]NEXT[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]2[/TD]
[TD="class: xl193"]*423*[/TD]
[TD="class: xl193"]NEXT[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]5[/TD]
[TD="class: xl193"]*MAE*[/TD]
[TD="class: xl193"]RE[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]5[/TD]
[TD="class: xl193"]*RTX*[/TD]
[TD="class: xl193"]VEN[/TD]
[/TR]
[TR]
[TD="class: xl191, align: right"]5[/TD]
[TD="class: xl193"]*FES*[/TD]
[TD="class: xl193"]VEN[/TD]
[/TR]
[TR]
[TD="class: xl193, align: right"]7[/TD]
[TD="class: xl193"]*1232*[/TD]
[TD="class: xl193"]MR[/TD]
[/TR]
[TR]
[TD="class: xl193, align: right"]7[/TD]
[TD="class: xl193"]*3212*[/TD]
[TD="class: xl193"]MR[/TD]
[/TR]
[TR]
[TD="class: xl193, align: right"]7[/TD]
[TD="class: xl193"]*4236*[/TD]
[TD="class: xl193"]MR[/TD]
[/TR]
</tbody>[/TABLE]

Right now, I have that table on the front page. I would like to filter values based on the information in the table.
So I would like to use Column as fields that I am filtering and Exclude should criteria that I am filtering. Then I would like to add Lable column data in column 10 after filtering.

So far this is what I got

Code:
Dim endrow As String: endrow = Sheets("Main").Range("A100").End(xlUp).Row
Dim LastRowColumnA As Long: LastRowColumnA = Sheets("Datamanage").Cells(Rows.Count, 1).End(xlUp).Row
Dim  Exclude As String
Dim Label As String
Dim Column as String


Sheets("Main").Select
    For i = 14 To endrow
        'Declare Search and Quick label located on the instructions sheet
        Exclude = Cells(i, 2).Value
        Label = Cells(i, 3).Value
         Column= Cells(i, 1).Value  


        Sheets("DataManage").Select
            ActiveSheet.Range("A1:C" & LastRowColumnA).AutoFilter Field:=Column, Criteria1:= _
        Search, Operator:=xlAnd


        On Error Resume Next
        Range("J2:J" & LastRowColumnA).SpecialCells(xlCellTypeVisible).Value = Label
            ActiveSheet.ShowAllData
                Sheets("Instructions").Select
                
                   Next i

I can't figure it out how to automatically filter based on field information from the table.

Do you guys have any idea?

Thank you
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,215,578
Messages
6,125,642
Members
449,245
Latest member
PatrickL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top