Finding a column via header search and then adding everything in it into an array.

Hi All,

I'm currently adding various columns/cells into arrays and then transposing them into a separate workbook (An updated template format). The code is going to be generic so I can use it on all old templates to import to the new. This issue I have is I have a column that's dynamic as many people didn't follow the original template and created their own columns.

Because of this I cannot simple state - x = Range("A2:A50").value etc as the data I need may not be in column B in a template etc.

Not a problem in regards to finding the column as the title never changes meaning I can search for that column as shown below:

Please Login or Register  to view this content.

The above code finds my title (Yay) and even copies the column into an array (yay) however it also copies the title! I've tried to create a .offset(1) at the end of the Set SelRange line but it comes up with an error. It lets me offset a row strangely....

Any ideas on how I can add that entire row without the title into an array? If it helps, the title is always in 'X1,2, 3 etc'

Thanks in advance

Welcome to the forum.
Please read the forum rules first here (Rules No. 3 in this case) : http://www.excelforum.com/forum-rule...rum-rules.html

Please Login or Register  to view this content.

Hi Scoobster_doo,

Thanks for adding the code tags. Please tell me the result of the code.

Regards

Sir daboho,

When you enable an error-handling routine using "On Error ...." statement, VBA will start to reporting to "Err object" for every statements after "On Error ...." line.
There are several properties of Err object, such as .Number property and .Description, that can tell us what error is produced.
A page that lists these errors is :
http://onlinelibrary.wiley.com/doi/1...57616.app3/pdf

"Err.Number = 0" means no error is occurred, anything else means an error is occurred.

An example :

Please Login or Register  to view this content.

As for the "If Err.Number <> 0 Then Exit Sub" statement on post #2, using "Application.WorksheetFunction.Match()" will not always return a number, it will return an error if nothing is matched (found), so, if this is happenned then Exit Sub immediately.

Instead of the On Error construction you could also use.

Please Login or Register  to view this content.

Also note that it's only when using the Application object your code doesn't go into Debug when no match is found whereas using the WorksheetFunction the code errors out.

@daboho : You are wellcome sir.

@Scoobster_doo :
You are welcome. Do you want to replace the #N/A with blank, or the data must be shifted up when #N/A occurred ?

You can check each member of array whether it is an error, using IsError() function and replace it with vbNullString.
After that, you can continue adding your validating code.

Please Login or Register  to view this content.