Solved: Help improve reverse loop

[Switchman]

I am looking for a better, read more intelligent way to perform a reverse loop and insert rows and specific data. Currently what I am doing is:

1- Go to cell A10000
2 -Look for a specific ?Trigger Value? (trigger value is always in column ?A?
3 -If found ?Trigger Value?
4 -Insert 4 rows underneath row with trigger value
5 ? Set the values of specific cells in the rows with specific data
6 ? Go up a line and repeat test until end of loop


I go to row 10,000 to ensure I get all of the rows. My data set size is variable based on the output of another program. It can be as little as 90 rows or I have seen it up to 5000 rows. There is no guarantee that it wll not be over 10,000 rows, I just haven't seen it (yet).

Ideally I want to dynamically find and go the first column of the last row and begin my testing. I am also sure there is probably a better way to test and insert my new data sets.

Any help would be appreciated.

Thanks

[vba]
Range("A10000").Select

Dim i As Integer
For i = 10000 To 2 Step -1

' Inserts 4 rows to make room for software licenses
' looks for a specific cell value to trigger the insert at specific location.
If ActiveCell.Value = "SHF320" Then
ActiveCell.Offset(1, 0).Resize(4, 1).EntireRow.Insert

' Inserts the Base Software Desc, PN and
ActiveCell.Offset(1, 0).Value = "Base Software"
ActiveCell.Offset(1, 1).Value = "Base Desc"
ActiveCell.Offset(1, 2).Value = "Base PN"
ActiveCell.Offset(1, 3).Value = "1"
ActiveCell.Offset(1, 4).Value = "(-)"
ActiveCell.Offset(1, 5).Value = "(-)"

' Inserts the Ethernet Software Desc, PN
' The "tmpETH" value is to be able to find this cell easly at a later time
' and replace it with values I have to calculate later
ActiveCell.Offset(2, 0).Value = "ETH RTU"
ActiveCell.Offset(2, 1).Value = "Eth Desc"
ActiveCell.Offset(2, 2).Value = "Eth PN"
ActiveCell.Offset(2, 3).Value = "tmpETH"
ActiveCell.Offset(2, 4).Value = "(-)"
ActiveCell.Offset(2, 5).Value = "(-)"

' Inserts the TDM Software Desc, PN
ActiveCell.Offset(3, 0).Value = "TDM RTU"
ActiveCell.Offset(3, 1).Value = "TDM Desc"
ActiveCell.Offset(3, 2).Value = "TDM PN"
ActiveCell.Offset(3, 3).Value = "tmpTDM"
ActiveCell.Offset(3, 4).Value = "(-)"
ActiveCell.Offset(3, 5).Value = "(-)"

' Inserts the WDM Software Desc, PN
ActiveCell.Offset(4, 0).Value = "WDM RTU"
ActiveCell.Offset(4, 1).Value = "WDM Desc"
ActiveCell.Offset(4, 2).Value = "WDM PN"
ActiveCell.Offset(4, 3).Value = "tmpWDM"
ActiveCell.Offset(4, 4).Value = "(-)"
ActiveCell.Offset(4, 5).Value = "(-)"
End If
ActiveCell.Offset(-1, 0).Select
Next i
[/vba]

[ska67can]

avoid using select.

try this:

Dim i As Integer
For i = 10000 To 2 Step -1
    If Cells(i, 1) = "SHF320" Then
     ' Inserts 4 rows to make room for software licenses
     ' looks for a specific cell value to trigger the insert at specific location.
        Range(Cells(i + 1, 1), Cells(i + 4, 1)).EntireRow.Insert
 
         ' Inserts the Base Software Desc, PN and
        Cells(i, 0) = "Base Software"
        Cells(i, 1) = "Base Desc"
        Cells(i, 2) = "Base PN"
        Cells(i, 3) = "1"
        Cells(i, 4) = "(-)"
        Cells(i, 5) = "(-)"
 
         ' Inserts the Ethernet Software Desc, PN
         ' The "tmpETH" value is to be able to find this cell easly at a later time
         ' and replace it with values I have to calculate later
        Cells(i + 1, 0) = "ETH RTU"
        Cells(i + 1, 1) = "Eth Desc"
        Cells(i + 1, 2) = "Eth PN"
        Cells(i + 1, 3) = "tmpETH"
        Cells(i + 1, 4) = "(-)"
        Cells(i + 1, 5) = "(-)"
 
         ' Inserts the TDM Software Desc, PN
        Cells(i + 2, 0) = "TDM RTU"
        Cells(i + 2, 1) = "TDM Desc"
        Cells(i + 2, 2) = "TDM PN"
        Cells(i + 2, 3) = "tmpTDM"
        Cells(i + 2, 4) = "(-)"
        Cells(i + 2, 5) = "(-)"
 
         ' Inserts the WDM Software Desc, PN
        Cells(i + 3, 0) = "WDM RTU"
        Cells(i + 3, 1) = "WDM Desc"
        Cells(i + 3, 2) = "WDM PN"
        Cells(i + 3, 3) = "tmpWDM"
        Cells(i + 3, 4) = "(-)"
        Cells(i + 3, 5) = "(-)"
    End If
Next i

if you are looking to start at the last entry in column A then replace

For i = 10000 To 2 Step -1

with

For i = Cells(65536, 1).End(XlUp).Row To 2 Step -1

ska

[Bob Phillips]

[vba]

Dim i As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For i = Cells(Rows.Count,"A").End(XlUp).Row To 2 Step -1

' Inserts 4 rows to make room for software licenses
' looks for a specific cell value to trigger the insert at specific location.
If Cells(i, "A").Value = "SHF320" Then

Rows(i + 1).Resize(4).Insert

' Inserts the Base Software Desc, PN and
Cells(i + 1, "A").Resize(, 6) = Array("Base Software", "Base Desc", "Base PN", "1", "(-)", "(-)")

' Inserts the Ethernet Software Desc, PN
' The "tmpETH" value is to be able to find this cell easly at a later time
' and replace it with values I have to calculate later
Cells(i + 2, "A").Resize(, 6) = Array("ETH RTU", "Eth Desc", "Eth PN", "tmpETH", "(-)", "(-)")

' Inserts the TDM Software Desc, PN
Cells(i + 3, "A").Resize(, 6) = Array("TDM RTU", "TDM Desc", "TDM PN", "tmpTDM", "(-)", "(-)")

' Inserts the WDM Software Desc, PN
Cells(i + 4, "A").Resize(, 6) = Array("WDM RTU", "WDM Desc", "WDM PN", "tmpWDM", "(-)", "(-)")
End If

Next i

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
[/vba]

[Switchman]

I want to thank both of you for responding.

ska67can, I can see how your solution works. I am using as similar sub for another area where I am pasting some formulas in. I can see a way to change it based on how you changed my original code that should improve it.

Xld, I looked up the .resize and it appears that .resize sets the range to a value consisting of 6 columns and that it is setting each column to the value of the column in the array, i.e column 2 is set to ?Base Desc?. I would never have thought to try this. 1 as I am not a very experienced VBA coder and 2 as the online help does not even allude that you could do this.

Do you know if the array has to be rebuilt each time it loops? If so, I wonder if you define a variable with the array data outside the loop and refer to the variable as part of the .resize if it changes the performance.

Also, could you tell me the reason you dimensioned ?i? to a ?Long? vs. an ?Integer? type other than the size value it can contain
Integer = -32,768 to 32,767.
Long = -2,147,483,648 to 2,147,483,647

Thanks again and I am going to mark the thread as solved.

[Bob Phillips]

Xld, I looked up the .resize and it appears that .resize sets the range to a value consisting of 6 columns and that it is setting each column to the value of the column in the array, i.e column 2 is set to ?Base Desc?. I would never have thought to try this. 1 as I am not a very experienced VBA coder and 2 as the online help does not even allude that you could do this.

The Resize just redimensions the range by rows and columns. I then load an array into that newly sized range, which is how each cell gets a different value.

Do you know if the array has to be rebuilt each time it loops? If so, I wonder if you define a variable with the array data outside the loop and refer to the variable as part of the .resize if it changes the performance.

Absolutely not and a good point, I just rattled it off to show te technique. Defining and loading an array variable outside of the loop is definitely the way to go.

Also, could you tell me the reason you dimensioned ?i? to a ?Long? vs. an ?Integer? type other than the size value it can contain[/FONT][/SIZE]

Integer = -32,768 to 32,767.
Long = -2,147,483,648 to 2,147,483,647

Thanks again and I am going to mark the thread as solved.

Because the OS functions will internally convert the long to an integer and back again, so it is just wasteful.