1

I have an excel workbook with two sheets. Each has exactly the same format in terms of columns. The columns are ColA - ID ColB - Desc/dept ColC - Name

Sheet2 has all the information in all columns. Sheet1 only has information in ColC.

I would like to write VBA code to compare the values in Column C for both sheets and if it matches (as they might not be in the same order or even the same number of records) then the code copies the ID and Desc from columns A and B of Sheet2(forId) into Columns A and B of sheet1(1099(2)). I wrote the following but it just runs and excel stops responding after I click on it. When I select to restart excel, nothing changed.

Thanks for your help.

Sub insertId()
Dim Rng As Range
Dim compRngI As Range, compRngO As Range
Dim wsI As Worksheet, wsO As Worksheet
Dim x As Variant, y As Variant

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

'setting sheet where id to be checked
    Set wsI = ThisWorkbook.Sheets("forId")

'set sheet where id to be pasted
    Set wsO = ThisWorkbook.Sheets("1099(2)")

'Set ranges to be compared between sheets
    With wsI
         lastRow = .Range("C" & .Rows.Count).End(xlUp).Row
         Set compRngI = .Range("C1:C" & lastRow)
    End With

    With wsO
        lastRow = .Range("C" & .Rows.Count).End(xlUp).Row
        Set compRngO = .Range("C1:C" & lastRow)
    End With

'compare cells in both columns C of each spreadsheet
    For Each x In compRngI
        For Each y In compRngO
            If x = y Then
                y.Offset(0, -2).Value = x.Offset(0, -2).Value
                y.Offset(0, -1).Value = x.Offset(0, -1).Value
            End If
        Next y
    Next x


ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With

End Sub
RT Mohess
  • 11
  • 1
  • 6
  • 1
    `Set compRngI = .Range("C:C")` Don't set it to the entire column? Find the last row and limit range to that. – findwindow Jan 08 '16 at 16:01
  • As @findwindow stated, use one of the methods shown [HERE](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) to find the last used cell. You are iterating the loop 1,000,000^2 times by looping through the entire column. You could also use the `find` method or `autofilter` to cut down on the loops. – Scott Craner Jan 08 '16 at 16:09
  • Thanks @scottCraner. I edit the portion of code as below but now no errors and nothing happens 'Set ranges to be compared between sheets `With wsI lastRow = .Range("C" & .Rows.Count).End(xlUp).Row Set compRngI = .Range("C" & lastRow) End With With wsO lastRow = .Range("C" & .Rows.Count).End(xlUp).Row Set compRngO = .Range("C" & lastRow) End With` – RT Mohess Jan 08 '16 at 16:24
  • Change your Original post by using the edit option and repost your code. It is easier to read. – Scott Craner Jan 08 '16 at 16:29
  • thanks @findwindow. made change but now no errors nor any results. it doesn't do anything when i click run. – RT Mohess Jan 08 '16 at 16:29
  • this line `Set compRngI = .Range("C" & lastRow) ` should be `Set compRngI = .Range("C1:C" & lastRow) ` same with the second one. – Scott Craner Jan 08 '16 at 16:30
  • @ScottCraner made the changes in original post. Please see above. – RT Mohess Jan 08 '16 at 16:32
  • Scott's better than I. Just go with what he says. – findwindow Jan 08 '16 at 16:33
  • did you see my last comment? – Scott Craner Jan 08 '16 at 16:34
  • @findwindow not better, just wired different. There are time I miss the simplest things that I am amazed you find. – Scott Craner Jan 08 '16 at 16:35
  • @ScottCraner better as in more practiced/knowledgeable =P – findwindow Jan 08 '16 at 16:36
  • @ScottCraner I made the second change as suggested. (Will update original post to reflect same) Once again, there are no errors. The title bar of excel briefly says "running" and nothing changes. – RT Mohess Jan 08 '16 at 16:37
  • 1
    @ScottCraner i was looking in the wrong sheet. I accidentally set the input values that were to be copied(x values) to the blank cells (y values). i switched the x and y around in the last block and it worked. (will update original post to reflect change) Thanks so much! – RT Mohess Jan 08 '16 at 16:47
  • Couldn't you just do this using MATCH and INDEX formulas? – Niall Jan 08 '16 at 16:57
  • @CrazyHorse I don't know how to use those. – RT Mohess Jan 08 '16 at 17:03

1 Answers1

0

You can achieve the same result by using a MATCH and INDEX formula, and it'll run much faster than trying to do this with VBA.

The MATCH part returns a value's position in a range, and INDEX allows you to retrieve a value in a given position of a range. It's functionally similar to a VLOOKUP but allows you to return values to the left of the lookup column.

In your case, you want the following 2 formulas in columns A and B of your 1099(2) sheet:

Column A (ID)

=INDEX(Sheet1!A:A,MATCH(C2,Sheet1!C:C,0))

Column B (Desc/dept)

=INDEX(Sheet1!B:B,MATCH(C2,Sheet1!C:C,0))

The 0 in the MATCH formula means it has to find an exact match, rather than the nearest value that is less than the value being looked up (in which case you'd put 1 here) or greater than (which would be -1).

Also, a rule of thumb with Excel is that you should always try to achieve things using the built in formulas before writing your own VBA code, as the built in formulas will always be a lot faster than VBA.

Niall
  • 1,551
  • 4
  • 23
  • 40
  • Formulas are faster if we are talking a small sample group, not if we are talking thousands of rows, then vba using arrays will be faster. The more formulas the slower the calculation time. – Scott Craner Jan 08 '16 at 22:40
  • Thank you for your help @crazyHorse. I was able to realize that excel has more formulae than I am aware of and I am currently able to use index. – RT Mohess Feb 15 '16 at 15:04