CODEWITHSUNDEEP
excelvba
user2530086
user2530086

Reputation: 15

VBA - Macro That Finds Specified Header, Filters Values in Various Sheets where Column Locations Change

Trying to figure out a Macro that will do the following:

1) Find a specified header (ie: "status") 2) Filter this column to a specified value (ie: "discontinued") 3) Once filtered, find another specified column (ie: "replaced with")

The challenge for me is I can't figure out how to set it so the column "Field" is variable to the sheet its on.

I have multiple files that have the "status" header in different locations, (one file has it in column c, another in column f, etc). Is there a way to have excel find that specific column in each sheet is and filter it?

Upvotes: 1

Views: 1772

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Find a specified header

You would implement this quite exactly as you'd subconsciously do in your head while "finding a specific header": iterate all columns from left to right until you find it, ...and throw a fit when it's not there. The function should be generic enough to not be attached to any specific worksheet, so you should pass it a worksheet as a parameter.

Something like this:

'returns column number of specified header, or -1 if not found.
Public Function FindHeader(sheet As Worksheet, header As String) As Integer
    ...
End Function

Find your header row, and loop through the non-empty cells. Your exit conditions would be either you've iterated all non-empty cells in the header row, or you've found a cell that matches the content you're looking for. When the loop exits, you need to know two things: whether the header was found, and if it was, under which column.

Filter this column for a specified value

You don't say how the value gets specified, so I'll assume all you need is a Sub that takes an Integer for the header column to filter, and a String parameter for the value to filter with. Using AutoFilter -related methods should get you there. Again you want to be able to use this code for any sheet, so pass one as a parameter:

Public Sub FilterForSpecificValue(sheet As Worksheet, columnIndex As Integer, filter As String)
    ...
End Sub

Find another specified column

Well, just reuse the function from #1, passing it the same sheet with another column header:

result = FindHeader(ActiveSheet, "AnotherHeader")

That's about as much as I can give you for the question you've asked; feel free to ask SO for any specific programming issues you might encounter implementing this. However it's possible you won't even need to ask, because it's very likely that somebody else somewhere has documented exactly how to do it - here are a couple links that can help you:

Upvotes: 1

Related Questions