Snasui Excel Forum

Code: Select all

Private Sub AddBotton_Click()

If SecOptM = True Then

Dim LinesToInsert As Variant
Dim rTarget As Range
  
  With Sheets("MAIN")
  'Set rTarget = Range("a" & .Rows.Count).End(xlUp).Offset(-6, 0)
   Set r = ActiveCell
    
  Sheets("Main").Select: Range("b9").Select ' SHEET TARGET

    Do Until UCase(ActiveCell.Value) = "ANNUALLY"
        ActiveCell.Offset(1, 0).Select ' find the range named "ID"
    Loop
        ActiveCell.Offset(0, 0).Select
        intR = ActiveCell.Row
    
   'LinesToInsert = InputBox("How many Rows to insert? ") ' Ask for how many rows needs
    LinesToInsert = RowNTextBox
  
  For Each c In r
    If c.Value <> "" Then
        Do Until ActiveCell.Value = ""
            If UCase(ActiveCell.Value) = UCase(c.Value) And _
                    c.Value <> "" Then
                If Val(LinesToInsert) <= 50 Then
                    On Error Resume Next
                    r.EntireRow.Resize(Int(LinesToInsert)).Insert
                End If
            Else
                ActiveCell.Offset(1, 0).Select
            End If
        Loop
    End If
    Cells(intR, 2).Select
Next
    
    End With
End If

End Sub

Code: Select all

For Each c In r
    If c.Value <> "" Then
        Do Until ActiveCell.Value = ""
            If UCase(ActiveCell.Value) = UCase(c.Value) And _
                    c.Value <> "" Then
                If Val(LinesToInsert) <= 50 Then
                    On Error Resume Next
                    r.EntireRow.Resize(Int(LinesToInsert)).Insert
                End If
            Else
                ActiveCell.Offset(0, 0).Select
            End If
        Loop
    End If
    Cells(intR, 2).Select

Code: Select all

Private Sub AddBotton_Click()
    Dim x$, y&
    If (SecOptM = True Or SecOptA = True) _
        And RowNTextBox <> "" Then
        With Sheets("MAIN")
            x = IIf(SecOptM, "Annually", "Total")
            y = Application.Match(x, .Range("b:b"), 0)
            .Range("b" & y).Resize(RowNTextBox).EntireRow.Insert
        End With
    End If
End Sub