Conditional Row insertion problem

X

xenikos

I'm a newbie to VBA, have been teaching myself the past week, and I'
stuck on the last step of a complex macro I've been writing. What
want to do is insert a row (or more than one row in some cases
depending on the value returned by one column in the worksheet. Thi
function returns either 1, 2, or 3 for each earlier line of data, and
want to insert a row after each cell in column D that contains a 2, an
insert two rows after each cell in column D that contains a 3.

I'm having a tough time figuring out how to go about this. The onl
stab I've made at it is the following, which doesn't even do anything


range("D:D").Select
If Value = 2 Then
Selection.EntireRow.Insert (xlShiftDown)
ElseIf Value = 3 Then
Selection.EntireRow.Insert (xlShiftDown)
Selection.EntireRow.Insert (xlShiftDown)
End If

I would greatly appreciate any assistance with this. thank you
 
M

MSP77079

Of course it doesn't do anything. There are several problems:
1) you are trying to select a column, not a row (and not doing i
correctly in any case)
2) you need .Value instead of Value

You want something like this:

select the cell you want to check, then
thisRow = ActiveCell.Row
if cells(thisRow, "D").Value = 2 then
Cells(thisRow, "D").entireRow.Insert (xlShiftDown)

et ceter
 
X

xenikos

i'm still encountering trouble with this - can anyone tell me what I'
doing wrong? thanks!

For i = 1 To 65536
thisRow = ActiveCell.Row
If ActiveCell.Value = 2 Then
ActiveCell.EntireRow.Insert (xlShiftDown)
End If
Next
 
N

Norman Jones

Hi Xenikos,

Try:

Sub InsertRows()
Dim i As Long
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Sheet1")
LastRow = WS.Cells(Rows.Count, "D").End(xlUp).Row

For i = LastRow To 1 Step -1
With WS.Cells(i, "D")
If .Value = 2 Or .Value = 3 Then
.Offset(1).EntireRow.Resize(.Value - 1).Insert
End If
End With
Next i
End Sub
 
X

xenikos

Well that worked great (once I realized that having a non-numeric valu
in the last cell was what was giving me an error), thank you Norma
jones
 
Top