I need to pull certain columns out of a data file to use in the creation of
an invoice. The file is a database export and the database will only pull
user-defined fields if they have data entered in them. Since we can use the
UDF fields some weeks and not others the data size is variable both in number
of row and in the columns that are created. I need to be able to select
columns based on their column header (Customer Number, Customer Name, Part
Number, etc).

Is there VBA language that will allow me to select a variable range based on
the column header?

You can get the column number of a given column header like this...

ColNumber = Rows(1).Find("Part Number", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).Column

where I assumed Row 1 contained the headers and, for the above example, that
we were looking for the Part Number header. You can use the found column
number in a Cells property call for a given row number to specify the
contents of that particular cell.

--
Rick (MVP - Excel)


"Have_Data_Will_Travel" wrote
in message ...
I need to pull certain columns out of a data file to use in the creation of
an invoice. The file is a database export and the database will only pull
user-defined fields if they have data entered in them. Since we can use
the
UDF fields some weeks and not others the data size is variable both in
number
of row and in the columns that are created. I need to be able to select
columns based on their column header (Customer Number, Customer Name, Part
Number, etc).

Is there VBA language that will allow me to select a variable range based
on
the column header?

I'm not sure I understand. Where do I actually "get" the column number with
this code? Do I need to make it part of a message box or something?

What I want to do is to have a template with the headers I'm looking for and
search the raw data for the data under those columns. I could probably do it
with hlookups but it would take thousands of them.

"Rick Rothstein" wrote:

You can get the column number of a given column header like this...

ColNumber = Rows(1).Find("Part Number", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).Column

where I assumed Row 1 contained the headers and, for the above example, that
we were looking for the Part Number header. You can use the found column
number in a Cells property call for a given row number to specify the
contents of that particular cell.

--
Rick (MVP - Excel)


"Have_Data_Will_Travel" wrote
in message ...
I need to pull certain columns out of a data file to use in the creation of
an invoice. The file is a database export and the database will only pull
user-defined fields if they have data entered in them. Since we can use
the
UDF fields some weeks and not others the data size is variable both in
number
of row and in the columns that are created. I need to be able to select
columns based on their column header (Customer Number, Customer Name, Part
Number, etc).

Is there VBA language that will allow me to select a variable range based
on
the column header?

.

From the Column property of the range that the Find function returns. This part...

Rows(1).Find("Part Number", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

returns the cell in Row 1 containing the text "Part Number" as a range... the .Column I placed after it in my original code pulls out the column number and (again, in my original code) I then assigned this column number to a variable which I named ColNumber for my example. You can assign it to any variable you want, or use it for the column argument in a Cells property call, or use it as the argument to a MsgBox statement, etc., etc.

--
Rick (MVP - Excel)


I'm not sure I understand. Where do I actually "get" the column number with
this code? Do I need to make it part of a message box or something?

What I want to do is to have a template with the headers I'm looking for and
search the raw data for the data under those columns. I could probably do it
with hlookups but it would take thousands of them.

"Rick Rothstein" wrote:

You can get the column number of a given column header like this...

ColNumber = Rows(1).Find("Part Number", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).Column

where I assumed Row 1 contained the headers and, for the above example, that
we were looking for the Part Number header. You can use the found column
number in a Cells property call for a given row number to specify the
contents of that particular cell.

Ahhh...I see. So I'm defining the variable so that I can use it in a code to
define the range that I want to copy and paste into my template.

"Rick Rothstein" wrote:

From the Column property of the range that the Find function returns. This part...

Rows(1).Find("Part Number", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

returns the cell in Row 1 containing the text "Part Number" as a range... the .Column I placed after it in my original code pulls out the column number and (again, in my original code) I then assigned this column number to a variable which I named ColNumber for my example. You can assign it to any variable you want, or use it for the column argument in a Cells property call, or use it as the argument to a MsgBox statement, etc., etc.

--
Rick (MVP - Excel)


I'm not sure I understand. Where do I actually "get" the column number with
this code? Do I need to make it part of a message box or something?

What I want to do is to have a template with the headers I'm looking for and
search the raw data for the data under those columns. I could probably do it
with hlookups but it would take thousands of them.

"Rick Rothstein" wrote:

You can get the column number of a given column header like this...

ColNumber = Rows(1).Find("Part Number", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).Column

where I assumed Row 1 contained the headers and, for the above example, that
we were looking for the Part Number header. You can use the found column
number in a Cells property call for a given row number to specify the
contents of that particular cell.

.