Having Multiple VBA "Insert Row" Programs Correctly Reference Trigger Cells

[Michael515]

Hi Y'all,

I have a worksheet that inserts rows based on the value in a certain cell. The problem is, is that on this same worksheet, I would like to create to other cells that insert rows below it based on the value inside it. The problem is, that after the rows are inserted into the worksheet the first time, the other "trigger" cells become displaced in the code. I feel like I have to use a combination of dim variables and offsets but don't know exactly how. Here's the code I've been working on below:

Sub InsertFundRows()
    Dim i As Integer, n As Integer, m As Long
    n = Sheets("Questionnaire").Range("B20").Value
    m = Sheets("Questionnaire").Range("B30").Row
    For i = 1 To n
        Rows(m + 1 * i).Insert
    Next i
End Sub

Sub InsertFundRows2()
    Dim i As Integer, n As Integer, m As Long
    Dim x As Variable
    Dim y As Variable
    x = Sheets("Questionnaire").Range("D108").Offset(Range("B20").Value, 0)
    n = x.Value
    y = Sheets("Questionnaire").Range("B111").Offset(Range("B20").Value, 0)
    m = y.Row
    For i = 1 To n
        Rows(m + 1 * i).Insert
    Next i
End Sub

I'm new to writing VBA so any tips for the future would be much appreciated. If I haven't described my problem clearly enough, please feel free to ask me more questions so I can clarify. Thank you for all the help!

 

[JLGWhiz]

It is difficult to interpret exactly what you are trying to do, but maybe this will help you to get your code fixed. In both Insert and Delete actions the column references to the right and the row references below can be affected. So to avoid that, for rows, start at the bottom and work upwards. you can get the last row with data by:

Dim lstRw As Long
lstRow = Sheets("Questions").Cells(Rows.Count, 2).End(xlUp).Row

Which returns the row number for the last cell with data in column B.
Then you can construct a For loop like:

For i = lstRw To 2 Step -1
 'If Cells(i, 2).Value = 0 Then do something
 'Code for condititions to insert or delete rows here
Next

The thing to remember when using this method is the i increments negatively, so if you use the i variable for a row reference above the current row location, use the minus (-) sign in front of the number of offset rows.

 

[friel300]

you could also try using a named range as your cell that keeps moving (B30?).

hilight the cell then go Formulas>Define Name then type a name and hit ok.
not instead of :

m = Sheets("Questionnaire").Range("B20").Row

use :

m = Sheets("Questionnaire").Range("test").Row

 

[Michael515]

you could also try using a named range as your cell that keeps moving (B30?).

hilight the cell then go Formulas>Define Name then type a name and hit ok.
not instead of :

m = Sheets("Questionnaire").Range("B20").Row

use :

m = Sheets("Questionnaire").Range("test").Row

I redefined the cell name which seems to keep the insertrow function intact, but I have the insertrow function automatically triggered by a cell change as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$20" Then
        Call InsertFundRows
    End If

    If Target.Address = "PriceLimits" Then
        Call InsertFundRows2
    End If
End Sub

The first cell change works, but when I change the second cell it doesn't seem to call. Should I create to separate subs? Or is their something wrong with my above code?

 

[JLGWhiz]

I redefined the cell name which seems to keep the insertrow function intact, but I have the insertrow function automatically triggered by a cell change as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$20" Then
        Call InsertFundRows
    End If

    If Target.Address = [COLOR=#ff8c00]"PriceLimits"[/COLOR] Then
        Call InsertFundRows2
    End If
End Sub

The first cell change works, but when I change the second cell it doesn't seem to call. Should I create to separate subs? Or is their something wrong with my above code?

If PriceLimits is a named range then you need the keyword Range("PriceLimits").Address configured like this to compare addresses. Otherwise, a range address cannot equal a text string. If you are testing for the text string as a value, then change Target.Address to Target.Value.