CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Using VBA to insert a row above specific values in a table

I have a table organized similarly to as follows:

   Open Date    MaturityDate    ZeroRate
    5-May-15    5-May-15    0.006999933
    5-May-15    6-May-15    0.0069999329
    5-May-15    5-Jun-15    1
    5-May-15    6-Jul-15    0.008993128
    5-May-15    5-Aug-15    0.006744264
    5-May-15    5-Nov-15    0.006912941
    5-May-15    5-May-16    0.007104633
    5-May-15    5-May-17    0.006910929
    5-May-15    6-Nov-17    0.007017292
    5-May-15    7-May-18    0.00712202
    5-May-15    5-Nov-18    1
    5-May-15    6-May-19    0.008551509
    5-May-15    5-Nov-19    0.009734602
    5-May-15    5-May-20    0.010916848
    5-May-15    5-Nov-20    0.011779622
    5-May-15    5-May-21    0.012632521
    5-May-15    5-Nov-21    1
    5-May-15    5-May-22    0.014366506
    5-May-15    7-Nov-22    0.014935518
    5-May-15    5-May-23    0.0154865

I'm trying to code a VBA script to insert a row into the sheet above every instance of the value of "1" in the third column. Here's what I've written so far:

Sub RowInserter()
Dim d As Integer
d = Range("C:C").End(xlDown).Row
Dim c As Range
  For i = d To 2 Step -1
    If Cells(i, 1).Value = "1" Then
      Rows(Cells(i, 1).Row).Insert shift:=xlDown
    End If
  Next
End Sub

but it doesn't seem to have any effect. What am I doing wrong?

Upvotes: 1

Views: 2173

Answers (1)

ManishChristian
ManishChristian

Reputation: 3784

You have coded right, but only one mistake. You should type 3 instead of 1 in line: 

If Cells(i, 1).Value = "1" Then

Change that 3, like: 

If Cells(i, 3).Value = "1" Then

Upvotes: 3

Related Questions