Switchman
03-21-2008, 07:14 PM
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
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
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
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