Results 1 to 17 of 17

Compare columns and insert values in correct place

Threaded View

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    14

    Compare columns and insert values in correct place

    Hi! So, I have 2 very big columns with numbers that i want to compare. If a number in column C of the worksheet CADIM is also in column B of worksheet sheet3 it's ok, so I go to the next number. When a number is not in sheet3, I want to insert it in the correct position. Both columns are in decreasing order (although they have different sizes).
    Example:
    columnC(CADIM):
    10
    9
    6
    2

    columnB(sheet3):
    11
    10
    6
    2
    1

    In this case the only number of CADIM that is not in sheet3 is the 9, so I need to insert a row between row2 and row3 and write 9 in it.
    This is what I got so far, I'm only starting to learn VBA and I can't make this work... The current error is the first line in yellow, which I don't know what it means, and when I click the button with the macro nothing happens...

    Any help would be appreciated!!

    Private Sub CommandButton1_Click()    
        Dim res1 As String
        Dim res2 As String
    
        Set wk2 = ActiveWorkbook
        Set WK1 = Workbooks("CADIM PA's.XLS")
    
        Y = WK1.Sheets("CADIM").Range("C" & WK1.Sheets("CADIM").Rows.Count).End(xlUp).Row
        Z = wk2.Sheets("Sheet3").Range("B" & wk2.Sheets("sheet3").Rows.Count).End(xlUp).Row
        
            For i = 1 To Y
                For j = 1 To Z
                    res1 = WK1.Sheets("CADIM").Range("C" & i).Value
                    res2 = wk2.Sheets("sheet3").Range("B" & j).Value
                    If res1 = res2 Then Exit For
                    
                    If res1 > res2 Then
                    wk2.Sheets("sheet3").Range("B" & j).EntireRow.Insert
                    wk2.Sheets("sheet3").Range("B" & j).Value = res1
    
                    
                    End If
                Next
            Next
    End Sub
    Last edited by HugoRibeiro; 07-18-2013 at 07:56 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare 4 column values then insert a 5th columns value to a blank column/cell value
    By JasonKMcCoy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2012, 06:01 PM
  2. Replies: 1
    Last Post: 09-05-2012, 04:11 PM
  3. Replies: 0
    Last Post: 08-03-2012, 04:31 PM
  4. Compare columns in worksheets and move to correct sheet.
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-29-2011, 12:25 PM
  5. [SOLVED] How do I create a macro that will compare columns and place data
    By CompuCat in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-20-2006, 02:25 PM

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