+ Reply to Thread
Results 1 to 6 of 6

Compare cell to cell in previous row

Hybrid View

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Compare cell to cell in previous row

    Hi there I am hoping to get some help translating what I have below into functional code. The goal is to start at B2 and go down the column checking every cell to the next one and if they are different insert cells above between A:E, if they are the same continue moving down. Any imput would be greatly appreciated. Thanks!

    Dim Check_Row As Long
    Dim Last_Row As Long
    
    Check_Row = B2  //Will always be B2
     
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    While Check_Row<Last_Row
    
        if Check_Row != (Check_Row+1)
            Range("A" & Check_Row & ":" & "E" & Check_Row).Select
            Selection.Insert Shift:=xlDown
            Check_Row+1
        else
            Check_Row+1

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Compare cell to cell in previous row

    Maybe?

    Sub am_hawk()
    Range("B2").Select
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value <> ActiveCell.Offset(1).Value Then
            Range(ActiveCell.Offset(1, -1), ActiveCell.Offset(1, 3)).Insert
            ActiveCell.Offset(1).Select
        End If
        ActiveCell.Offset(1).Select
    Loop
    End Sub

  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Compare cell to cell in previous row

    Worked perfectly, I am just curious how would I insert an entire Row? Currently I'm using the offset values to determine the location of the inserted cells like you have shown. I just tried
    Range(ActiveCell.Row, ActiveCell.Row).Insert
    but I received runtime error 1004 Method 'Range' of object'_Global failed. Any advice on how to insert a row?

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Compare cell to cell in previous row

    Maybe:

    Sub am_hawk()
    Range("B2").Select
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value <> ActiveCell.Offset(1).Value Then
            ActiveCell.Offset(1).EntireRow.Insert
            ActiveCell.Offset(1).Select
        End If
        ActiveCell.Offset(1).Select
    Loop
    End Sub

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Compare cell to cell in previous row

    Thanks for the help! Much appreciated.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Compare cell to cell in previous row

    You're welcome. Glad to help out and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cell color will be changed based on its value compare with previous cell value
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-02-2013, 02:09 PM
  2. [SOLVED] Make Cell Reference to Either a cell in Previous Tab or the Previous Tabs Name
    By paddyk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2013, 01:13 PM
  3. [SOLVED] Cell color change if value is = or > or < compare with previous cell
    By cityguy10005 in forum Excel General
    Replies: 8
    Last Post: 07-29-2012, 07:52 PM
  4. Compare text in a cell with the text in the previous cell and return a value
    By kinley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-09-2010, 04:14 AM
  5. how to compare the active cell value to the previous cell's value
    By crimsonkng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2005, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1