+ Reply to Thread
Results 1 to 2 of 2

Comparing Sheets - copy if found

Hybrid View

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Wisconsin
    MS-Off Ver
    Excel 2000
    Posts
    14

    Comparing Sheets - copy if found

    I have a macro that compares names on two worksheets(LastMonth and ThisMonth) and highlights the new names on ThisMonth. If it finds a name, I would like it to take the value from column E in LastMonth and put it in E for ThisMonth. I think I have a logic error, in that I don't think it accounts for a row that gets skipped. Thanks for looking.

    Sub Differences()
        Dim cell As Range
        Dim irow As Integer
        Dim fee As String
        irow = 2
        With Sheets("ThisMonth")
            For Each cell In .Range("a2", .Range("a" & Rows.Count).End(xlUp))
                                    
                If IsError(Application.Match(cell.Value, Sheets("LastMonth").Range("a:a"), 0)) Then
                    cell.Offset(, 0).Resize(, 6).Interior.Color = vbYellow
                             
                Else
                    cell.Offset(, 0).Resize(, 6).Interior.ColorIndex = xlNone
                    'fee = cell.Offset(, 4).Value 'This is reading from the wrong sheet
                    'fee = Sheets("LastMonth").cell.Offset(, 4).Value 'Error = Object doesn't support this property
                    fee = Sheets("LastMonth").Range("e" & irow).Value 'Returns wrong answers once a new name occurs
                    cell.Offset(, 4).Value = fee
                    irow = irow + 1
                End If
                
        
            Next cell
        End With
    End Sub

  2. #2
    Registered User
    Join Date
    09-27-2012
    Location
    Wisconsin
    MS-Off Ver
    Excel 2000
    Posts
    14

    Re: Comparing Sheets - copy if found

    Got it on my own. Might not be perfect, but it works.
    Sub Differences()
        Dim cell As Range
        Dim irow As Integer
        Dim fee As String
        
        irow = 2
        With Sheets("ThisMonth")
            For Each cell In .Range("a2", .Range("a" & Rows.Count).End(xlUp))
                                    
                If IsError(Application.Match(cell.Value, Sheets("LastMonth").Range("a:a"), 0)) Then
                    cell.Offset(, 0).Resize(, 6).Interior.Color = vbYellow
                             
                Else
                    cell.Offset(, 0).Resize(, 6).Interior.ColorIndex = xlNone
                   
                    If Sheets("LastMonth").Range("a" & irow).Value = cell.Offset(, 0) Then
                    fee = Sheets("LastMonth").Range("e" & irow)
                    Else
                        Do Until Sheets("LastMonth").Range("a" & irow).Value = cell.Offset(, 0)
                        irow = irow + 1
                        Loop
                    End If
                    fee = Sheets("LastMonth").Range("e" & irow)
                                        
                            
                                      
                    cell.Offset(, 4).Value = fee
                    irow = irow + 1
                End If
                
        
            Next cell
        End With
    End Sub

+ 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] Comparing Two Columns and Copy Adjacent Column IF Match Found
    By 10AVATAR in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-29-2012, 04:33 PM
  2. Comparing Data based on number found
    By tbar05 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2012, 09:57 PM
  3. Most Efficient Way in Comparing Two Workbooks and Consolidating Found Differences
    By quixter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2012, 12:46 PM
  4. Search Data from Multiple Sheets and copy found data to new workbook
    By xenith1988 in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 06-26-2012, 12:58 PM
  5. Comparing two sheets and perform copy paste operation
    By kmlprtsngh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2010, 04:56 AM

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