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.)

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.

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.)

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.)
.