Solved: How do I create a range based on a column name?

[rhk001]

Hi there,

I am very very new to VBA but am loving what it can do, albeit very basic stuff I am doing at the moment.

I do a lot of importing into excel from another programme(Primavera P6).
The fields from the imports vary depending on the fileds open on the layout, so do their column positions. The first row of data holds the column headings, There are two date coulmns that I am interested in they are always called "start" and "Finish", I need to do a some tidying up to get them turned into proper date fields as some come in as text strings, and others dates. ( I have sub routing to do this bit)

What I am looking at doing in VB is looking to the right to find the last column, then work backwards to find Start & finish and select them as a range before I run my routine to tidy them up. Can anyone help my with some code to find "Start", and select all the data below as a range, then the same for "finish"

Thanks in advance for your help

Rob

[Bob Phillips]

Off the top, untested

[vba]

mpStartCol = Application.Match("Start", Rows(1), 0)
mpEndCol = Application.Match("Finish", Rows(1), 0)

Set rng = Columns(mpStartCol).Resize(, mpEndCol - mpStartCol + 1)
[/vba]

[mdmackillop]

I'm reading the question differenttly so
[VBA]
Sub SetFormat()
Dim c As Range
Dim MyRange As Range
Set c = Rows(1).Find("Start")
Set MyRange = Range(c.Offset(1), Cells(Rows.Count, c.Column).End(xlUp))
MyRange.NumberFormat = "dd/mm/yyyy"
End Sub

[/VBA]

[rhk001]

Brilliant, thanks for the quick reply guys, I will have a little play with both options.

[rhk001]

okay guys, here is my final answer & it works thanks for your help, I am sure it is not the nicest looking code you have ever seen.
[VBA]Sub FormatP6dates()

Dim c As Range
Dim d As Range
Dim MySRange As Range
Dim MyFRange As Range
Set c = Rows(1).Find("Start")
Set d = Rows(1).Find("Finish")

Set MySRange = Range(c.Offset(1), Cells(Rows.Count, c.Column).End(xlUp))
MySRange.Select

Set MyFRange = Range(d.Offset(1), Cells(Rows.Count, d.Column).End(xlUp))
MyFRange.Select

Selection.Replace What:=" A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'This is Part 2 of the code, its does the text to columns conversion to sort out the dates.

Selection.TextToColumns Destination:=MySRange, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True

Selection.TextToColumns Destination:=MyFRange, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True

End Sub[/VBA]