0

Im looking to compare all entries in column B of WB1 (can vary in amount up to 300,000), versus a master listing in WB2,tab "Guide", column A (circa 500 entries).

If there are new entries in column B of WB1, i have a msgbox appear listing the new types to be added to the master listing in WB2.

I would also like a msgbox to appear saying "all types valid" if there are no new types found

Any help greatly appreciated.

Sub Compare()

Dim sh1 As Worksheet
Dim sh2 As Worksheet

Dim lr1 As Long
Dim lr2 As Long

Dim rng1 As Range
Dim rng2 As Range
Dim c As Range

Dim msg As String
msg = "New types: "

Set sh1 = Sheets(1)

Workbooks.Open Filename:="filepath\Types.xls"
Set sh2 = Worksheets("Guide")

lr1 = Application.WorksheetFunction.CountA(sh1.Columns(1))
lr2 = Application.WorksheetFunction.CountA(sh2.Columns(1))

Set rng1 = sh1.Range("B2:B" & lr1)
Set rng2 = sh2.Range("A2:A" & lr2)
   
For Each c In rng1
    If Len(c.Value) > 0 And Application.CountIf(rng2, c.Value) = 0 Then
    msg = msg & vbNewLine & c.Value
    End If
    
Next

Workbooks("Types.xls").Close SaveChanges:=False

MsgBox msg

End Sub
coyner
  • 3
  • 4

1 Answers1

0

You declare k As Integer but Excel has more rows than Integer can handle. Declare all row/column counting variables as Long instead.

Actually there is no benefit in using Integer instead of Long in VBA therefore I recommend always to use Long. Only some very rare cases really need Integer: Why Use Integer Instead of Long?

Also your CountIf statement is wrong and missing the criteria:

If Application.WorksheetFunction.CountIf(S1.Range(S1.Cells(2, 2), S2.Cells(j, 1)), Then

Should look like

If Application.WorksheetFunction.CountIf(S1.Range(S1.Cells(2, 2), S2.Cells(j, 1)), CRITERIA) Then

Where CRITERIA needs to be replaced by your count criteria.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hi Peh, thanks for your feedback, I've updated my code above, just have one issue outstanding now – coyner Jun 10 '22 at 10:05
  • @coyner Just put a `If msg = "New types: " Then msg = "all types valid"` right before your `MsgBox msg`. If the `msg` is still the inital message and nothing was added in the loop then change it to all types are valid. – Pᴇʜ Jun 10 '22 at 11:47