VBA Need to Match cells based on if column selection in an array

[Elle Bee Dee]

I have been working on trying to make a macro that will check if a value is in a list selected based on a header row match.

The workbook is set up so i have the "TableSheet" which is something comperable to the table below, on the "HeaderSelectionSheet" a selection that matches the header rows of the table, and on "Sheet1" that gets copied to "Sheet3" I want to check the vaules in column C to make sure that thevalue is in the table under the header row selected.

Below i have pasted the table and the code.

I got the macro below to work but i will be adding more columns to the table and it would be great if i could have it work for however many columns are in the table.

Any Suggestions or help would be greatly appreciated.


Dances, Art, Sports, Philanthropy, Other
Jazz, Paiting, Basketball, Amnesty International, Chess Club
Tap, Sculpture, Baseball, Habitat for Humanity, Drama
Modern, Drawing, Football, Food Bank, Academic Decath
Ballet,, Soccer, SADD, Mathletes
,, Track , , Student Council



____

'Check

'Delete Check sheet if it exists
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Sheet3").Delete
Application.DisplayAlerts = True

'Copy the pasted data

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(4)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Sheet3"

Application.Sheets("Sheet3").Activate

EndRowPasted = Cells(Rows.Count, "A").End(xlUp).Row
counter = 2
Check = Cells(counter, "G").Value

'Check to see that the data matches the header selection

Range("G2").Value = "=MATCH(Sheet3!C2,CHOOSE(MATCH(HeaderSelection!$B$13,TableSheet!$A$2:$F$2),TableSheet!$A$2:$A$9,TableSheet!$B$2:$B$9,TableSheet!$C$2:$C$9,TableSheet!$D$2:$D$9,TableSheet!$E$2:$E$9,TableSheet!$F$2:$F$9,TableSheet!$G$2:$G$9,TableSheet!$H$2:$H$9,TableSheet!$I$2:$I$9,TableSheet!$J$2:$J$9),0)"

Range("G2").Select
Selection.Copy

Range("G3:G" & EndRowPasted).Select
ActiveSheet.Paste

'If there is a #N/A error Give message and exit macro

Do While counter < EndRowPasted

Cells(counter, "G").Select
If Application.IsNA(ActiveCell.Value) Then
MsgBox "EPIC FAIL"
Exit Sub
Else
counter = counter + 1
End If
Loop

'Delete Check sheet

Application.DisplayAlerts = False
Sheets("Sheet3").Delete
Application.DisplayAlerts = True



'End Check

 

[mirabeau]

... make a macro that will check if a value is in a list selected based on a header row match.

Hi,

This, on the face of it, doesn't seem a particularly difficult thing for which to write a macro.

However, from your rather jumbled data (as presented) and your macro as given, trying to ascertain specifically what it is that you want seems to me the more difficult task.

Would it be unreasonable to ask that you try to re-explain, in simpler terms and with a simplified example, just what it is you are wanting?

Good luck!

 

[Elle Bee Dee]

Sure ill do my best to clarify...

I have a workbook with 3 sheets....

On the first I have a table. A comparable table is listed below (delimited by comma). I called this "Table Sheet"

Dances, Art, Sports, Philanthropy, Other
Jazz, Paiting, Basketball, Amnesty International, Chess Club
Tap, Sculpture, Baseball, Habitat for Humanity, Drama
Modern, Drawing, Football, Food Bank, Academic Decath
Ballet,, Soccer, SADD, Mathletes
,, Track , , Student Council

On the Second I have a drop down data validated list of the header rows on my table Sheet. I callled this "HeaderSelectionSheet".

and on the 3rd sheet "Sheet 1" i have values i have pasted in from a seperate database.

my goal is to check the values in column "C" on sheet 1 to make sure they match the table values on the "Table Sheet" that fall under the header selected on the "HeaderSelectionSheet" and if not to exit the macro.

Let me know if you need more clarification. Thank you. I appreciate any insights you may have