• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

excel macro error

hi all

i have an example work book attached, there is a macro on both sheet 1 an 2
the macro in sheet 1 will compare column A of sheet 1 and 2 and return what is unique to sheet1 in sheet3
and vice versa for sheet2

the macro (i did not write) works fine for sheet 1, but fails on sheet2 with type mismatch - and i think this is because sheet 2 has 1 less row

can anyone tell me how i could get around this?
----sheet1 macro---------
Sub CopyUniques_old()
   Dim lngLastRow As Long
   Dim varData
   With Sheets("Sheet1")
      lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      varData = .Evaluate("INDEX(IF(ISNA(MATCH(A2:A" & lngLastRow & ",Sheet2!A:A,0)),A2:A" & lngLastRow & ",""Matched""),0,0)")
      varData = Filter(Application.Transpose(varData), "Matched", False)
      varData = Application.Transpose(varData)
   End With
   With Sheets("Sheet3")
      .Range("A:A").ClearContents
      .Range("A1").Value = "Stock Codes not on new Forecast "
      .Range("A2").Resize(UBound(varData, 1), 1).Value2 = varData
   End With


End Sub


-----sheet 2 macro----------

Sub CopyUniques_new()
   Dim lngLastRow As Long
   Dim varData
   With Sheets("Sheet2")
      lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      varData = .Evaluate("INDEX(IF(ISNA(MATCH(A2:A" & lngLastRow & ",Sheet1!A:A,0)),A2:A" & lngLastRow & ",""Matched""),0,0)")
      varData = Filter(Application.Transpose(varData), "Matched", False)
      varData = Application.Transpose(varData)
   End With
   With Sheets("Sheet4")
      .Range("A:A").ClearContents
      .Range("A1").Value = "Stock Codes not on new Forecast "
      .Range("A2").Resize(UBound(varData, 1), 1).Value2 = varData
   End With


End Sub

Open in new window

differences-test.xlsx
0
malraff
Asked:
malraff
  • 2
1 Solution
 
Rory ArchibaldCommented:
It's because all of the data matches. Try this version:
Sub CopyUniques_new()
   Dim lngLastRow As Long
   Dim varData
   With Sheets("Sheet2")
      lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      varData = .Evaluate("INDEX(IF(ISNA(MATCH(A2:A" & lngLastRow & ",Sheet1!A:A,0)),A2:A" & lngLastRow & ",""Matched""),0,0)")
      varData = Filter(Application.Transpose(varData), "Matched", False)
      If UBound(varData) < LBound(varData) Then
        MsgBox "No unmatched data"
        Exit Sub
    End If
      varData = Application.Transpose(varData)
   End With
   With Sheets("Sheet4")
      .Range("A:A").ClearContents
      .Range("A1").Value = "Stock Codes not on new Forecast "
      .Range("A2").Resize(UBound(varData, 1), 1).Value2 = varData
   End With


End Sub

Open in new window

0
 
malraffAuthor Commented:
great stuff rorya - i think i owe u a pint or 2 ;)
0
 
Rory ArchibaldCommented:
Stick a couple of cans of Guinness in the post. ;)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now