+ Reply to Thread
Results 1 to 17 of 17

Compare columns and insert values in correct place

Hybrid View

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

    Re: Compare columns and insert values in correct place

    I'm afraid there's a little problem, sorry to bother again

    I was trying the code in a test file, with only 2 columns on each sheet, and it all worked well. However, I tried to implement it on the file I really need, which has a lot of columns on Sheet3, and the code only moved 2 columns down. So I did some alterations to the code (the columns have changed) and this is what I have now:

    Sub ertert()
    Dim x, i&
    
    
    With Sheets("Sheet3")
        x = .Range("B1:C" & .Cells(Rows.Count, 2).End(xlUp).Row).Value
    End With
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        For i = 1 To UBound(x): .Item(x(i, 2)) = x(i, 1): Next i
        With Sheets("CADIM")
            x = .Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
        End With
    
        For i = 1 To UBound(x): .Item(x(i, 2)) = x(i, 1): Next i
        Sheets("Sheet3").Range("B1:C1").Resize(.Count).Value = WorksheetFunction.Transpose(Array(.items, .keys))
    End With
    
    With Sheets("Sheet3")
        With .Range("A1:AZ" & .Cells(Rows.Count, 2).End(xlUp).Row)
               .Sort Key1:=.Cells(2, 3), Order1:=xlDescending
        End With
        
    End With
    End Sub
    Sheet3 was already done by someone and sheet CADIM was made by me, so now I'm using ONLY columns A and B. The ideal way to do what I need would be to insert an entire row on Sheet3 and then write in columns B and C the corresponding values of columns A and B from CADIM (all the other columns of that row would be left blank).
    37em.jpg
    What is happening now is it does insert the new values on columns B and C as I want, but it also inserts random (?) values on the rest of the columns. It appears that it copies those values from some other row, but I can't tell why because they are too many.
    Last edited by HugoRibeiro; 07-19-2013 at 11:00 AM.

+ 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] 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. 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