find name and define range for Column

qball · November 13th, 2004, 07:12 PM

I want to define a range base on a header column, however, the column might not be in the same location (e.g. column A this time and column D next time).

I want to define the column range based on the header name regardless of location.

Any help would be greatly appreciated.

Here is my temporary work around to find and format the column, but this will not help me once I try to add the mathematical portion to my sheet.

I have two date columns that I format in this manner to make sure that they are accurately formatted.

currently the Range is defined as the entire column containing the active cell.

Code:

    Cells.Find(What:="PIDLDT", After:=ActiveCell, LookIn:=xlFormulas _
      , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range(ActiveCell, ActiveCell.EntireColumn).Select
    Selection.TextToColumns Destination:=Range(ActiveCell, ActiveCell.EntireColumn), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
    Selection.NumberFormat = "mm/dd/yy;@"

BrianB · November 15th, 2004, 04:04 AM

The general method is :-

Code:

'Partial code
Set foundcell = Cells.Find(What:="PIDLDT")
foundcell.EntireColumn.Select

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

qball · November 15th, 2004, 06:51 PM

Thanks, but that still does not give set that column to a range.

Is there something that will maybe give the column reference and
allow the range to be specified at that time?

Maybe I am trying to make this harder than it really is.

Qball
Silicon Hills
- New to VB

Birger · November 17th, 2004, 03:49 PM

qball, what exactly do you want to achieve?

Do you want to find top/bottom of a range?
Do you want to format the content in the range?
Do you want to define a range?

Birger · November 17th, 2004, 03:55 PM

Define a range like this:

Dim rng as range
'select some range as done above
Set rng = selection

Now you can select the rng like this:

rng.Select