Write data in reverse
 

Given the following macro:
Option Explicit
Sub mastertest1()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("IP Office", "Avaya Additional Equipment",
"Partner and Magix", "Vodavi Master Schedule", "Vodavi Misc Direct Parts",
"Paging and Headsets", "Cable, Patch Panel Material", "Power, Racks, Mgt
Etc", "Data Equip & Time Blocks", "Voice & Cable Installation"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("VICOM Quote Sheet").Rows(17).Insert
..EntireRow.Copy _
Destination:=Worksheets("VICOM Quote Sheet").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub


How do I change it to write the lines in sequence from top to bottom instead
of insert mode which writes the last found as the first line?

Thanks

Write data in reverse
 

jerry -

it's written from the bottom up for a reason......................
you're inserting rows.

when you start your macro, before you insert any rows, your lastrow
variable comes up as, say row 25. as you go merrily along, perhaps
you've inserted 5 rows here & there. but the lastrow variable will
not adjust to reflect that. so in going down your range, you'll miss
the last 5 rows!

by starting @ the bottom & working upward, you avoid this problem.
the startrow is always the same & won't be adjusted with any row
additions. so any adjustments to the range will be below where you
currently are, so you won't miss anything.

not only that, but if iRow = 20, and you insert a row, then are you
going to loop thru the NEW iRow21, or skip that one & go to iRow22,
which used to be iRow21.
gets very confusing.
going up from the bottom is much easier.

having said that, i guess you **could** change it around the other
way, but you'd have to constantly re-set your lastrow variable to make
sure you've got everything, and you run the risk of having data
skipping/redoing errors.
:)
susan



On May 30, 8:13 am, Jerry Foley
wrote:
Given the following macro:
Option Explicit
Sub mastertest1()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("IP Office", "Avaya Additional Equipment",
"Partner and Magix", "Vodavi Master Schedule", "Vodavi Misc Direct Parts",
"Paging and Headsets", "Cable, Patch Panel Material", "Power, Racks, Mgt
Etc", "Data Equip & Time Blocks", "Voice & Cable Installation"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("VICOM Quote Sheet").Rows(17).Insert
.EntireRow.Copy _
Destination:=Worksheets("VICOM Quote Sheet").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub

How do I change it to write the lines in sequence from top to bottom instead
of insert mode which writes the last found as the first line?

Thanks