Matching Ranges
 

I want to Create a Macro that will search for a Column Header on a source
spreadsheet, match that with an Indentically named Columnheader on
spreadsheet I use as a template, then take the information in the cells below
the header in the source spreadsheet and move it to the template.
the sticky parts are as followes.
1. have to do this for about 11 Columns but some of the headers on the
template will not appear on the Source. 5 are absolutely necessary and the
other 6 are optional.
2. I need to search for the header names because they may not be in the
right order, and worse they may not be in the first row. still worse 2 of the
header names have a tendency of being duplicated in different columns with
different information, but i still them.
3. there can be anywhere from two to 100 rows of information.
4. I have some formulas I would like to Automatically populate the correct
number of cells.
I know this sounds like alot but I can't imagine that no one has ever tried
to do this or something like this before. I have the basic parts down but I'm
struggling with the things I've indicated.
Thanks

Matching Ranges
 

This code will get you going. The find function you can change the rows to
be more than one row

from
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
to
Set c = .Rows("1:3").Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)

I'm not sure if the formulas will be right. We may have to change the code
to a pastespecial formulas (I think this will work). I'm not sure what you
wqant done with the duplicate headers. Change the workbooknames and sheets
as necessary. the code can be modified to open the books as well.

Sub fillTemplet()

With Workbooks("Source.xls").Sheets("Sheet1")
ColCount = 1
'repeat for every column header
Do While .Cells(1, ColCount) < ""
Header = .Cells(1, ColCount)
LastRow = .Cells(Rows.Count, ColCount).End(xlUp).Row
Set CopyRange = .Range(.Cells(2, ColCount), _
.Cells(LastRow, ColCount))

With Workbooks("Templet.xls").Sheets("Sheet1")
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find header : " & Header)
Else
CopyRange.Copy Destination:=c.Offset(1, 0)
End If
End With
ColCount = ColCount + 1
Loop
End With
End Sub

"KWhamill" wrote:

I want to Create a Macro that will search for a Column Header on a source
spreadsheet, match that with an Indentically named Columnheader on
spreadsheet I use as a template, then take the information in the cells below
the header in the source spreadsheet and move it to the template.
the sticky parts are as followes.
1. have to do this for about 11 Columns but some of the headers on the
template will not appear on the Source. 5 are absolutely necessary and the
other 6 are optional.
2. I need to search for the header names because they may not be in the
right order, and worse they may not be in the first row. still worse 2 of the
header names have a tendency of being duplicated in different columns with
different information, but i still them.
3. there can be anywhere from two to 100 rows of information.
4. I have some formulas I would like to Automatically populate the correct
number of cells.
I know this sounds like alot but I can't imagine that no one has ever tried
to do this or something like this before. I have the basic parts down but I'm
struggling with the things I've indicated.
Thanks

Matching Ranges
 

Joel, Thank you
what I have thus far for the puposes of selection works pretty well. I do
things like a VBA NUB in other words I still record Macros and then clean
them up very slowly. right now it's still kind of clunky and embarrasing but
I wanted to show you this.

Application.Goto Reference:=Worksheets("Entries").Rows("1:3")
Selection.Find(what:="MCA", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy

Now this works great unless there is a space (in whole empty rows) between
the header and the first item in the list. now of course I need the data but
i need to delete the space.
the other important part here are the Formulas I needed to create this
particular piece is a number which needs to have the vector stripped from
the magnitude and reported in two different cells. actually three. it does
not appear possible to do this in a group, in other words i can't just select
the entire column containing the numbers apply the first formula and paste,
then apply the second and paste &c. It looks like i'm going to need to go
back at the end and identify each number associate it with its line and then
apply the formulas. anyway any ideas on this subject would be very
appreciated.
R,
Karl

"Joel" wrote:

This code will get you going. The find function you can change the rows to
be more than one row

from
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
to
Set c = .Rows("1:3").Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)

I'm not sure if the formulas will be right. We may have to change the code
to a pastespecial formulas (I think this will work). I'm not sure what you
wqant done with the duplicate headers. Change the workbooknames and sheets
as necessary. the code can be modified to open the books as well.

Sub fillTemplet()

With Workbooks("Source.xls").Sheets("Sheet1")
ColCount = 1
'repeat for every column header
Do While .Cells(1, ColCount) < ""
Header = .Cells(1, ColCount)
LastRow = .Cells(Rows.Count, ColCount).End(xlUp).Row
Set CopyRange = .Range(.Cells(2, ColCount), _
.Cells(LastRow, ColCount))

With Workbooks("Templet.xls").Sheets("Sheet1")
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find header : " & Header)
Else
CopyRange.Copy Destination:=c.Offset(1, 0)
End If
End With
ColCount = ColCount + 1
Loop
End With
End Sub

"KWhamill" wrote:

I want to Create a Macro that will search for a Column Header on a source
spreadsheet, match that with an Indentically named Columnheader on
spreadsheet I use as a template, then take the information in the cells below
the header in the source spreadsheet and move it to the template.
the sticky parts are as followes.
1. have to do this for about 11 Columns but some of the headers on the
template will not appear on the Source. 5 are absolutely necessary and the
other 6 are optional.
2. I need to search for the header names because they may not be in the
right order, and worse they may not be in the first row. still worse 2 of the
header names have a tendency of being duplicated in different columns with
different information, but i still them.
3. there can be anywhere from two to 100 rows of information.
4. I have some formulas I would like to Automatically populate the correct
number of cells.
I know this sounds like alot but I can't imagine that no one has ever tried
to do this or something like this before. I have the basic parts down but I'm
struggling with the things I've indicated.
Thanks

Matching Ranges
 

A couple of comments

1) Using the find with after will cause the search to wrap to the beginning
of the range. If you use column a and tell it to start after row 10 after
row 65536 it will continue the search a row 1.

2) xldown stops at a blank row even if the data continues after the blank
row. That is why peopleuse xlup

LastRow = Range("A" & Rows.count).end(xlup).Row

Rows.Count is a constant in excel which is the last row of the worksheet.
usually 65536 in excel 2003 but is larger in excel 2007.

"KWhamill" wrote:

Joel, Thank you
what I have thus far for the puposes of selection works pretty well. I do
things like a VBA NUB in other words I still record Macros and then clean
them up very slowly. right now it's still kind of clunky and embarrasing but
I wanted to show you this.

Application.Goto Reference:=Worksheets("Entries").Rows("1:3")
Selection.Find(what:="MCA", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy

Now this works great unless there is a space (in whole empty rows) between
the header and the first item in the list. now of course I need the data but
i need to delete the space.
the other important part here are the Formulas I needed to create this
particular piece is a number which needs to have the vector stripped from
the magnitude and reported in two different cells. actually three. it does
not appear possible to do this in a group, in other words i can't just select
the entire column containing the numbers apply the first formula and paste,
then apply the second and paste &c. It looks like i'm going to need to go
back at the end and identify each number associate it with its line and then
apply the formulas. anyway any ideas on this subject would be very
appreciated.
R,
Karl

"Joel" wrote:

This code will get you going. The find function you can change the rows to
be more than one row

from
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
to
Set c = .Rows("1:3").Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)

I'm not sure if the formulas will be right. We may have to change the code
to a pastespecial formulas (I think this will work). I'm not sure what you
wqant done with the duplicate headers. Change the workbooknames and sheets
as necessary. the code can be modified to open the books as well.

Sub fillTemplet()

With Workbooks("Source.xls").Sheets("Sheet1")
ColCount = 1
'repeat for every column header
Do While .Cells(1, ColCount) < ""
Header = .Cells(1, ColCount)
LastRow = .Cells(Rows.Count, ColCount).End(xlUp).Row
Set CopyRange = .Range(.Cells(2, ColCount), _
.Cells(LastRow, ColCount))

With Workbooks("Templet.xls").Sheets("Sheet1")
Set c = .Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find header : " & Header)
Else
CopyRange.Copy Destination:=c.Offset(1, 0)
End If
End With
ColCount = ColCount + 1
Loop
End With
End Sub

"KWhamill" wrote:

I want to Create a Macro that will search for a Column Header on a source
spreadsheet, match that with an Indentically named Columnheader on
spreadsheet I use as a template, then take the information in the cells below
the header in the source spreadsheet and move it to the template.
the sticky parts are as followes.
1. have to do this for about 11 Columns but some of the headers on the
template will not appear on the Source. 5 are absolutely necessary and the
other 6 are optional.
2. I need to search for the header names because they may not be in the
right order, and worse they may not be in the first row. still worse 2 of the
header names have a tendency of being duplicated in different columns with
different information, but i still them.
3. there can be anywhere from two to 100 rows of information.
4. I have some formulas I would like to Automatically populate the correct
number of cells.
I know this sounds like alot but I can't imagine that no one has ever tried
to do this or something like this before. I have the basic parts down but I'm
struggling with the things I've indicated.
Thanks