Problem with function 1

[FJAY]

Good Morning - I have some problem with this function, it's running into an infinite loop. All I want is to insert rows based an a number. For example if:

frmSAGE.txtConsolePorts.Text = 5. Insert 5 rows
rngRefName = "EndNumCSLconsolports"

Public Sub layout_input_sheet()

Dim rngRefName As Range, intNumOfRows As Integer

Sheets("INPUT_SHEET").Select
Sheets("INPUT_SHEET").Range("C22:C1000").Select

For Each rngRefName In Selection.Cells
set rngRefName = selection
select case "EndNumCSLconsolports"
rngRefName = "EndNumCSLconsolports"
intNumOfRows = frmSAGE.txtConsolePorts.Text
Call insert_rows(rngRefName, intNumOfRows)
case Else
End select
Next

End Sub

Public Function insert_rows(ByVal rngRefNameVar As String, intRows As Integer)

Dim endOfSpecifiedTable As Range, startInsert As Integer
Dim intNumOfRowsToInsert As Integer

'find the specified reference name
Set endOfSpecifiedTable = Sheets("INPUT_SHEET").UsedRange.Find(rngRefNameVar, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
'select the Reference definning the end of the table
endOfSpecifiedTable.Select
'number of rows to be inserted
intNumOfRowsToInsert = intRows
startInsert = 1

'insert rows based on the number of PP15kSites
For startInsert = 1 To intNumOfRowsToInsert
If intNumOfRowsToInsert > 1 Then
Selection.EntireRow.Insert
Else
End If
Next

End Function

 

[VBAjedi]

Why not insert all the rows at once? Something like:

Public Function insert_rows(ByVal rngRefNameVar As String, intRows As Integer)
    Dim endOfSpecifiedTable As Range    
'find the specified reference name
    Set endOfSpecifiedTable = Sheets("INPUT_SHEET").UsedRange.Find(rngRefNameVar, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
    'select the Reference definning the end of the table
    endOfSpecifiedTable.Select
    Range(endOfSpecifiedTable, endOfSpecifiedTable.Offset(intRows - 1, 0)).EntireRow.Insert
End Function

Let me know if that solves the issue for you!

VBAjedi

 

[FJAY]

Thanks...the function runs by itself as a procedure but when I make it a function and call it in the layout_input_sheet it runs into an infinite loop. What an trying to do is select a range. For example A1:a10 and look for some values and insert rows above the values. Thanks.

 

[VBAjedi]

Ah. Usually when you're processing a bunch of rows with code that adds or deletes rows, it's better to work from the bottom to the top. The loop looks something like:

For x = 1000 to 22 Step -1
   Set rngRefName = Sheets("INPUT_SHEET").Range("C" & x)
   intNumOfRows = frmSAGE.txtConsolePorts.Text
   Call insert_rows(rngRefName, intNumOfRows)
Next x

That way when you insert rows (or delete them, same principle) it doesn't mess up your pointer (x).

I still don't have an exact picture of what you're trying to accomplish, but hopefully that gets you pointed in the right direction!

VBAjedi