(Previously posted, on April 13 3:45PM, but I am not well
trained in VB and really NEED a precise code for a Macro)

PLease help!!!

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

row
below

C D E F <-column
1 To To From County Name Number
2 St. Co of
3 <Blank cells in row Returns
4 <Blank row
5 30 017 Custer 10
6 30 111 Yellowstone 6
7 30 031 Gallatin 2
8 Same State 8
9 Diff Region 7
10 30 017 County Non-Migrants 13
11 30 019 Daniels 17
12 30 162 Missula 8
13 30 033 King 4
14 30 031 Gallatin 5
15 Same State 2
16 Diff Region 9
17 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
From From To To County Name Number
St. Co St. Co of
<Blank cells in row Returns
<Blank row
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.)

1. In your first example, what data is already in columns A and B? Or does
it not matter? Are you wanting to overwrite whatever is there?
2. In your second example, is the data in columns A and B simply the data
from the first county listed and copied down the rest of the rows in that
same group (to the row that contains "County Non-Migrants")?
3. How do the numbers add up?
4. I don't see your previous post on 4/13. Did you post it to a different
newsgroup?

--
Regards,
Bill


"Alex" wrote in message
...
(Previously posted, on April 13 3:45PM, but I am not well
trained in VB and really NEED a precise code for a Macro)

PLease help!!!

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

row
below

C D E F <-column
1 To To From County Name Number
2 St. Co of
3 <Blank cells in row Returns
4 <Blank row
5 30 017 Custer 10
6 30 111 Yellowstone 6
7 30 031 Gallatin 2
8 Same State 8
9 Diff Region 7
10 30 017 County Non-Migrants 13
11 30 019 Daniels 17
12 30 162 Missula 8
13 30 033 King 4
14 30 031 Gallatin 5
15 Same State 2
16 Diff Region 9
17 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
From From To To County Name Number
St. Co St. Co of
<Blank cells in row Returns
<Blank row
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.)

1. There is no data in columns A and B. It does not
matter because the data from C and D wil be pasted over it.

2. Yes, the data comes from the first county row, usually
after "County Non-Migrants"

3. The numbers for "number of returns" are completely
hypothetical. I just need soem advice on designing a
macro for the copied information to stop at "county non-
migrants".

4. The post is there, but listed as anonymous
"Copied Pair Info to Different Number..."



-----Original Message-----
1. In your first example, what data is already in columns
A and B? Or does
it not matter? Are you wanting to overwrite whatever is
there?
2. In your second example, is the data in columns A and B
simply the data
from the first county listed and copied down the rest of
the rows in that
same group (to the row that contains "County Non-
Migrants")?
3. How do the numbers add up?
4. I don't see your previous post on 4/13. Did you post
it to a different
newsgroup?

--
Regards,
Bill


"Alex"
wrote in message
...
(Previously posted, on April 13 3:45PM, but I am not
well
trained in VB and really NEED a precise code for a
Macro)

PLease help!!!

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

row
below

C D E F <-column
1 To To From County Name Number
2 St. Co of
3 <Blank cells in row Returns
4 <Blank row
5 30 017 Custer 10
6 30 111 Yellowstone 6
7 30 031 Gallatin 2
8 Same State 8
9 Diff Region 7
10 30 017 County Non-Migrants 13
11 30 019 Daniels 17
12 30 162 Missula 8
13 30 033 King 4
14 30 031 Gallatin 5
15 Same State 2
16 Diff Region 9
17 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
From From To To County Name Number
St. Co St. Co of
<Blank cells in row Returns
<Blank row
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.)



.

As it turns out, there are 2 ways to solve this problem:
1. Use a spreadsheet formula.
2. Use a VBA macro.

================================================== =======
1. The spreadsheet formula method checks the row above each County Name to
see
if it is either blank or the value "County Non-Migrants". If it is, then
copy the value of column C:D on the row to column A:B of the same row,
otherwise copy the value from the row above. Follow these steps:

1. Enter the following formula into cells $A$5:$B$17 (where each County Name
is listed in column $E):
=IF(OR(ISBLANK($E4),$E4="County Non-Migrants"),C5,A4)
2. Then Copy and Paste|Special Values to put the value back into these cells
and remove the formulas.

================================================== =======
2. The VBA macro method:

Public Sub CopyCDtoAB()
Dim ws As Worksheet
Dim rngStart As Range 'Starting County Name.
Dim rngEnd As Range 'End of data section at "County Non-Migrants".

Set ws = ActiveSheet
Set rngStart = ws.Cells(1, 5).End(xlDown)

Do
Set rngEnd = ws.Cells.Find(What:="County Non-Migrants", _
After:=rngStart, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If (rngEnd Is Nothing) Then Exit Do
If (rngEnd.Row < rngStart.Row) Then Exit Do

'End of this section of data has been found.
'Copy values to columns $A and $B.
With ws
.Range(.Cells(rngStart.Row, 1), .Cells(rngEnd.Row, 1)).Value = _
.Cells(rngStart.Row, 3).Value
.Range(.Cells(rngStart.Row, 2), .Cells(rngEnd.Row, 2)).Value = _
.Cells(rngStart.Row, 4).Value
End With
Set rngStart = rngEnd.Offset(1, 0)
Loop
End Sub

--
Regards,
Bill

Minor correction to the VBA code solution: When setting a cell to a new
value, use the Formula property instead of the Value property to insure
correct operation, for backward compatibility with older versions of Excel.
In older versions of Excel, setting the Value property did NOT change the
formula. The next time the workbook was recalculated, the value set by the
VBA code was overwritten. So the code inside the With statement should
technically be written as follows:

With ws
.Range(.Cells(rngStart.Row, 1), .Cells(rngEnd.Row, 1)).Formula = _
.Cells(rngStart.Row, 3).Value
.Range(.Cells(rngStart.Row, 2), .Cells(rngEnd.Row, 2)).Formula = _
.Cells(rngStart.Row, 4).Value
End With
--
Regards,
Bill


"Bill Renaud" wrote in message
...
As it turns out, there are 2 ways to solve this problem:
1. Use a spreadsheet formula.
2. Use a VBA macro.

================================================== =======
1. The spreadsheet formula method checks the row above each County Name to
see
if it is either blank or the value "County Non-Migrants". If it is, then
copy the value of column C:D on the row to column A:B of the same row,
otherwise copy the value from the row above. Follow these steps:

1. Enter the following formula into cells $A$5:$B$17 (where each County
Name
is listed in column $E):
=IF(OR(ISBLANK($E4),$E4="County Non-Migrants"),C5,A4)
2. Then Copy and Paste|Special Values to put the value back into these
cells
and remove the formulas.

================================================== =======
2. The VBA macro method:

Public Sub CopyCDtoAB()
Dim ws As Worksheet
Dim rngStart As Range 'Starting County Name.
Dim rngEnd As Range 'End of data section at "County Non-Migrants".

Set ws = ActiveSheet
Set rngStart = ws.Cells(1, 5).End(xlDown)

Do
Set rngEnd = ws.Cells.Find(What:="County Non-Migrants", _
After:=rngStart, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If (rngEnd Is Nothing) Then Exit Do
If (rngEnd.Row < rngStart.Row) Then Exit Do

'End of this section of data has been found.
'Copy values to columns $A and $B.
With ws
.Range(.Cells(rngStart.Row, 1), .Cells(rngEnd.Row, 1)).Value = _
.Cells(rngStart.Row, 3).Value
.Range(.Cells(rngStart.Row, 2), .Cells(rngEnd.Row, 2)).Value = _
.Cells(rngStart.Row, 4).Value
End With
Set rngStart = rngEnd.Offset(1, 0)
Loop
End Sub

--
Regards,
Bill