Copy Paired Info to Different number of Rows
 

I am working on old county migration flow datasets.
The datasets are for in-migration into a specific county.
For example, a typical file includes many in-migration
counties.
C D E F

St. FIPS Name # of returns
30 017 Custer 10
30 111 Yellowstone 6
30 031 Gallatin 2
Same State 8
Diff Region 7
30 017 County Non-Migrants 13
30 019 Daniels 17
30 162 Missula 8
30 033 King 4
30 031 Gallatin 5
Same State 2
Diff Region 9
30 019 County Non-Migrants 16

I want to make a macro that takes the first numbers by the
cell right below a "County Non-Migrants" and copies them
to the next "County Non-Migrants," taking into account
that the number of rows is not constant.
Example of End Result:

A B C D E F
To To From
St. FIPS St. FIPS Name # of returns
30 017 30 017 Custer 10
30 017 30 111 Yellowstone 6
30 017 30 031 Gallatin 2
30 017 Same State 8
30 017 Diff Region 7
30 017 30 017 County Non-Migrants 13
30 019 30 019 Daniels 17
30 019 30 162 Missula 8
30 019 30 033 King 4
30 019 30 031 Gallatin 5
30 019 Same State 2
30 019 Diff Region 9
30 019 30 019 County Non-Migrants 16


The problem I see is that the rows are not constant. How
would you write a complex if statement to take into
account the "county non-migrants" information?

Thanks for your help. (By the way, I have thousands rows
of cells of this type of information for over 10 years
with cells for each county in three states. A lot of work
for a long time if I have to the procedure manually.)

Copy Paired Info to Different number of Rows
 

Use the currentregion property. This will always bring back all the filled cells in an area
For instance as long as column A has countiguous data in rows 1 - 10 and column B thru D all have data at least in row 1 then the Currentregion will be A1:D10

so you can do this
dim x as long, MyRange as Range, C as Rang
range("A1").selec
x = selection.currentregion.rows.coun
set myrange = range(cells(1,1), cells(x,1)) " this is all filled cells in Column A
For each c im MyRang

"do your code and changes here
" you can use the Offset method to traverse other columns within the same row and make updates.

Nex

----- wrote: ----

I am working on old county migration flow datasets
The datasets are for in-migration into a specific county
For example, a typical file includes many in-migration
counties
C D E F

St. FIPS Name # of return
30 017 Custer 1
30 111 Yellowstone
30 031 Gallatin
Same State
Diff Region
30 017 County Non-Migrants 1
30 019 Daniels 1
30 162 Missula
30 033 King
30 031 Gallatin
Same State
Diff Region
30 019 County Non-Migrants 1

I want to make a macro that takes the first numbers by the
cell right below a "County Non-Migrants" and copies them
to the next "County Non-Migrants," taking into account
that the number of rows is not constant
Example of End Result

A B C D E F
To To Fro
St. FIPS St. FIPS Name # of return
30 017 30 017 Custer 1
30 017 30 111 Yellowstone
30 017 30 031 Gallatin
30 017 Same State
30 017 Diff Region
30 017 30 017 County Non-Migrants 1
30 019 30 019 Daniels 1
30 019 30 162 Missula
30 019 30 033 King
30 019 30 031 Gallatin
30 019 Same State
30 019 Diff Region
30 019 30 019 County Non-Migrants 1


The problem I see is that the rows are not constant. How
would you write a complex if statement to take into
account the "county non-migrants" information

Thanks for your help. (By the way, I have thousands rows
of cells of this type of information for over 10 years
with cells for each county in three states. A lot of work
for a long time if I have to the procedure manually.

Copy Paired Info to Different number of Rows
 

Hi,

You can try this:

Public Sub CopyInCounty()
Dim sST As String
Dim sFIPS As String

Columns("A:B").Select
Selection.NumberFormat = "@"

Range("A1").Value = "To"
Range("B1").Value = "To"
Range("A2").Value = "S."
Range("B2").Value = "FIPS"
Columns("B:B").Select
Selection.NumberFormat = "@"
Range("C3").Select
Do While Not IsEmpty(ActiveCell)
sST = ActiveCell.Text
sFIPS = ActiveCell.Offset(0, 1).Text
ActiveCell.Offset(0, -2).Value = sST
ActiveCell.Offset(0, -1).Value = sFIPS
Do While StrComp(ActiveCell.Offset(0, 2).Text, "County
Non-Migrants", vbTextCompare) < 0
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -2).Value = sST
ActiveCell.Offset(0, -1).Value = sFIPS
Loop
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Good luck,

Wouter

wrote in message ...
I am working on old county migration flow datasets.
The datasets are for in-migration into a specific county.
For example, a typical file includes many in-migration
counties.
C D E F

St. FIPS Name # of returns
30 017 Custer 10
30 111 Yellowstone 6
30 031 Gallatin 2
Same State 8
Diff Region 7
30 017 County Non-Migrants 13
30 019 Daniels 17
30 162 Missula 8
30 033 King 4
30 031 Gallatin 5
Same State 2
Diff Region 9
30 019 County Non-Migrants 16

I want to make a macro that takes the first numbers by the
cell right below a "County Non-Migrants" and copies them
to the next "County Non-Migrants," taking into account
that the number of rows is not constant.
Example of End Result:

A B C D E F
To To From
St. FIPS St. FIPS Name # of returns
30 017 30 017 Custer 10
30 017 30 111 Yellowstone 6
30 017 30 031 Gallatin 2
30 017 Same State 8
30 017 Diff Region 7
30 017 30 017 County Non-Migrants 13
30 019 30 019 Daniels 17
30 019 30 162 Missula 8
30 019 30 033 King 4
30 019 30 031 Gallatin 5
30 019 Same State 2
30 019 Diff Region 9
30 019 30 019 County Non-Migrants 16


The problem I see is that the rows are not constant. How
would you write a complex if statement to take into
account the "county non-migrants" information?

Thanks for your help. (By the way, I have thousands rows
of cells of this type of information for over 10 years
with cells for each county in three states. A lot of work
for a long time if I have to the procedure manually.)

Respond to my new entry
 

The last lines of the macro do not work!!!
It also does not like the loop.

ActiveCell.Offset(1,)). Select Loop

I am not very experienced with VB programming. Could you
respond to my new entry on April 14th 10:07am.

Thank you.
Alex Bujak

-----Original Message-----
Hi,

You can try this:

Public Sub CopyInCounty()
Dim sST As String
Dim sFIPS As String

Columns("A:B").Select
Selection.NumberFormat = "@"

Range("A1").Value = "To"
Range("B1").Value = "To"
Range("A2").Value = "S."
Range("B2").Value = "FIPS"
Columns("B:B").Select
Selection.NumberFormat = "@"
Range("C3").Select
Do While Not IsEmpty(ActiveCell)
sST = ActiveCell.Text
sFIPS = ActiveCell.Offset(0, 1).Text
ActiveCell.Offset(0, -2).Value = sST
ActiveCell.Offset(0, -1).Value = sFIPS
Do While StrComp(ActiveCell.Offset(0,
2).Text, "County
Non-Migrants", vbTextCompare) < 0
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -2).Value = sST
ActiveCell.Offset(0, -1).Value = sFIPS
Loop
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Good luck,

Wouter

wrote in message
...
I am working on old county migration flow datasets.
The datasets are for in-migration into a specific
county.
For example, a typical file includes many in-migration
counties.
C D E F

St. FIPS Name # of returns
30 017 Custer 10
30 111 Yellowstone 6
30 031 Gallatin 2
Same State 8
Diff Region 7
30 017 County Non-Migrants 13
30 019 Daniels 17
30 162 Missula 8
30 033 King 4
30 031 Gallatin 5
Same State 2
Diff Region 9
30 019 County Non-Migrants 16

I want to make a macro that takes the first numbers by
the
cell right below a "County Non-Migrants" and copies
them
to the next "County Non-Migrants," taking into account
that the number of rows is not constant.
Example of End Result:

A B C D E F
To To From
St. FIPS St. FIPS Name # of returns
30 017 30 017 Custer 10
30 017 30 111 Yellowstone 6
30 017 30 031 Gallatin 2
30 017 Same State 8
30 017 Diff Region 7
30 017 30 017 County Non-Migrants 13
30 019 30 019 Daniels 17
30 019 30 162 Missula 8
30 019 30 033 King 4
30 019 30 031 Gallatin 5
30 019 Same State 2
30 019 Diff Region 9
30 019 30 019 County Non-Migrants 16


The problem I see is that the rows are not constant.
How
would you write a complex if statement to take into
account the "county non-migrants" information?

Thanks for your help. (By the way, I have thousands
rows
of cells of this type of information for over 10 years
with cells for each county in three states. A lot of
work
for a long time if I have to the procedure manually.)
.