#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Location
    Missouri
    Posts
    14
    Rep Power
    0

    Excel VBA EntireRow.Insert change value


    Excel Version: 2010

    Hello, I'm working on a function in VBA to do the following:

    1. Iterate through a column; (column C) and look for a specific value.
    2. If that value is found, copy the entire row
    3. Insert a new row with the values from the copied row but change the value in Column C while keeping the remaining values the same.


    I got the copy and insert to work fine, however, I can't get it to change the value in Column C only the rows I'm adding.

    It is changing the value in Column C on all rows.
    So, I'm guessing the problem is between where I'm copying the entire row and doing the insert.

    state product config
    IL super Val1
    IL ultra Val2
    IL ultra Val3


    Code:
    Sub Button3_Click()
        InsertSome
    End Sub
    
    
    Public Function InsertSome()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim StartRow As Long
    Dim LastRow As Long
    Dim ConfVal As Long
    
            Col = "C"
            StartRow = 1
            BlankRows = 1
    
                LastRow = Cells(Rows.Count, Col).End(xlUp).Row
    
                Application.ScreenUpdating = False
    
                With ActiveSheet
        For ConfVal = LastRow To StartRow + 1 Step -1
            If .Cells(ConfVal, Col) = "Val3" Then
               .Cells(ConfVal, Col).EntireRow.Copy
               
               '.Cells(ConfVal, Col).Value = "Val4"
               .Cells(ConfVal, Col).EntireRow.Insert Shift:=xlDown
            End If
        Next ConfVal
        End With
    Application.ScreenUpdating = True
    End Function
    I welcome your advise, thanks.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,553
    Rep Power
    4549
    I've never used Excel VBA and I don't have an answer, sorry. Perhaps someone familiar with Excel will jump in.
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Location
    Missouri
    Posts
    14
    Rep Power
    0
    Originally Posted by Doug G
    I've never used Excel VBA and I don't have an answer, sorry. Perhaps someone familiar with Excel will jump in.
    I'm hoping.

IMN logo majestic logo threadwatch logo seochat tools logo