I have written a program to bring information from a file called 'US1' into a
file called 'CPWA'. US is 35,000 rows and information needs to move down one
row each time an "X" is identified in 'US1' column A. How do I get ImportRow
to equal one row down? ***** line does not work and this is where I need to
make a correction. This line is included for understanding only.

Here is what I have written to date:

Set ImportSht = Workbooks("CPWA.xls").Sheets("Import RRDD")
Set ImportRow = ImportSht.Range("A2")

With Workbooks("USD.txt").Sheets("USD")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If Range("A" & RowCount) = "X" Then
ImportRow.Value = Range("B" & RowCount).Value
ImportRow.Offset(0, 1).Value = Range("D" & (RowCount + 1)).Value
ImportRow.Offset(0, 2).Value = Range("E" & RowCount + 6).Value
ImportRow.Offset(0, 3).Value = Range("D" & RowCount + 8).Value
ImportRow.Offset(0, 4).Value = Range("C" & RowCount + 29).Value
ImportRow.Offset(0, 5).Value = Range("D" & RowCount + 29).Value
ImportRow.Offset(0, 6).Value = Range("E" & RowCount + 29).Value
ImportRow.Offset(0, 7).Value = Range("F" & RowCount + 29).Value
ImportRow.Offset(0, 8).Value = Range("G" & RowCount + 29).Value
********ImportRow = ImportRow + 1 ********
End If
Next RowCount

Import row is a range object so to move it you need to set it to one row
below...

set ImportRow = importrow.offset(1,0)
--
HTH...

Jim Thomlinson


"iashorty" wrote:

I have written a program to bring information from a file called 'US1' into a
file called 'CPWA'. US is 35,000 rows and information needs to move down one
row each time an "X" is identified in 'US1' column A. How do I get ImportRow
to equal one row down? ***** line does not work and this is where I need to
make a correction. This line is included for understanding only.

Here is what I have written to date:

Set ImportSht = Workbooks("CPWA.xls").Sheets("Import RRDD")
Set ImportRow = ImportSht.Range("A2")

With Workbooks("USD.txt").Sheets("USD")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If Range("A" & RowCount) = "X" Then
ImportRow.Value = Range("B" & RowCount).Value
ImportRow.Offset(0, 1).Value = Range("D" & (RowCount + 1)).Value
ImportRow.Offset(0, 2).Value = Range("E" & RowCount + 6).Value
ImportRow.Offset(0, 3).Value = Range("D" & RowCount + 8).Value
ImportRow.Offset(0, 4).Value = Range("C" & RowCount + 29).Value
ImportRow.Offset(0, 5).Value = Range("D" & RowCount + 29).Value
ImportRow.Offset(0, 6).Value = Range("E" & RowCount + 29).Value
ImportRow.Offset(0, 7).Value = Range("F" & RowCount + 29).Value
ImportRow.Offset(0, 8).Value = Range("G" & RowCount + 29).Value
********ImportRow = ImportRow + 1 ********
End If
Next RowCount

Perfect!!, thank you

"Jim Thomlinson" wrote:

Import row is a range object so to move it you need to set it to one row
below...

set ImportRow = importrow.offset(1,0)
--
HTH...

Jim Thomlinson


"iashorty" wrote:

I have written a program to bring information from a file called 'US1' into a
file called 'CPWA'. US is 35,000 rows and information needs to move down one
row each time an "X" is identified in 'US1' column A. How do I get ImportRow
to equal one row down? ***** line does not work and this is where I need to
make a correction. This line is included for understanding only.

Here is what I have written to date:

Set ImportSht = Workbooks("CPWA.xls").Sheets("Import RRDD")
Set ImportRow = ImportSht.Range("A2")

With Workbooks("USD.txt").Sheets("USD")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If Range("A" & RowCount) = "X" Then
ImportRow.Value = Range("B" & RowCount).Value
ImportRow.Offset(0, 1).Value = Range("D" & (RowCount + 1)).Value
ImportRow.Offset(0, 2).Value = Range("E" & RowCount + 6).Value
ImportRow.Offset(0, 3).Value = Range("D" & RowCount + 8).Value
ImportRow.Offset(0, 4).Value = Range("C" & RowCount + 29).Value
ImportRow.Offset(0, 5).Value = Range("D" & RowCount + 29).Value
ImportRow.Offset(0, 6).Value = Range("E" & RowCount + 29).Value
ImportRow.Offset(0, 7).Value = Range("F" & RowCount + 29).Value
ImportRow.Offset(0, 8).Value = Range("G" & RowCount + 29).Value
********ImportRow = ImportRow + 1 ********
End If
Next RowCount