Offset Rows Loop by Value in AH

manthony

New Member
Joined
Dec 5, 2016
Messages
40
Hi, I am struggling with my code below and I am hoping someone can help.

I have a formula in column AP that returns a number or a blank. I am trying to write some code so that if my formula in column AP returns a number then offset/insert (X) number of rows below based on the value in column AH. For example if cell AP2 = 1 and the value in AH2 is 3 then 3 rows will be inserted below AP2. The values in AH vary. Below is what I have so far.


Sub Macro4()
'
' Macro4 Macro


Last = Cells(Rows.Count, "AP").End(xlUp).Row
For i = Last To 1 Step -1
If IsNumeric(Cells(i, "AP").Value) Then
Cells(i, "AP").Offset(RowOffset:=(Cells(i, "AH").Value)).EntireRow.Insert
End If
Next i

End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
559
Office Version
365
Platform
Windows
Hi. Try:
Code:
Sub Macro4V2()
 Dim Last As Long, i As Long
 Last = Cells(Rows.Count, "AP").End(xlUp).Row
  For i = Last To 1 Step -1
   If IsNumeric(Cells(i, "AP").Value) Then
    Rows(i + 1 & ":" & i + Cells(i, "AH")).EntireRow.Insert
   End If
  Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,821
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top