Checking if data exists and adding it if not

Question

I have two rows containing dates which I am trying to compare and see if they are the same. If not, I want to add the extra data (i.e. row 1 can change and so I want those changes added to row 2). Ive tried looking around and also writing my own loop but I`m getting an error.

UPDATE following the comment, i am still getting an error; "Unable to get the CountIf propety of the worksheetfunction class"

I am wondering if there are any alternatives to check if the data is present somewhere in the second row add add it if not. I am new to vba and programming in general and any help would be appreciated.

Dim Dates As Range
Set Dates = Range("C23:O23")
Dim hisdate As Range
Set hisdate = Range("C35:O35")

For Each cell In Dates 'this is gonna first add new dates
    If WorksheetFunction.CountIf(hisdate, cell) > 0 Then 'do nothing
    Else
        Set hisdate = Union(hisdate, cell)
    End If
Next

The error probably results from using `hisdates`, when you haven't declared any such variable. Change to `hisdate`. — Vegard, Jul 03 '18 at 08:04
Thanks for that, tha'ts a real silly mistake. Still getting errors though — user1234, Jul 03 '18 at 08:16
I guess, this is [XY Problem](https://en.wikipedia.org/wiki/XY_problem). You need to compare dates in two rows and see if they are the same, but all you do in code is just take one date from one row and search for it in another one. — JohnyL, Jul 03 '18 at 08:21
This macro do nothing. It just sets new value to hisdate variable, that's all. — MarcinSzaleniec, Jul 03 '18 at 08:23
@JohnyL why is that? if it finds it, I want to do nothing, if not(else) then I want to add that bit to hisdate — user1234, Jul 03 '18 at 08:26
Once again: ***and see if they are the same***. This means that same set of dates must be in both rows. — JohnyL, Jul 03 '18 at 08:28
Your error comes from a range that contains several subranges (called `areas`). The function works correctly until you execute the first `union` statement. See https://stackoverflow.com/questions/42703316/unable-to-get-the-countif-property-of-the-worksheetfunction-class-error — FunThomas, Jul 03 '18 at 08:30
@FunThomas, but if I use + instead of union I still get an error — user1234, Jul 03 '18 at 08:42
Either you change your logic and for example use a dictionary, or you have to implement a search logic similar to this answer: https://stackoverflow.com/a/42703839/7599798 — FunThomas, Jul 03 '18 at 08:45

score 4 · Answer 1 · answered Jul 03 '18 at 08:56

4

As mentioned in the comments, WorksheetFunction.CountIf doesn't work with multi-area ranges. You could write your own countIf-function that loops over all areas (works even if the range is not a multi-area range)

Dim cell As Range
For Each cell In Dates 'this is gonna first add new dates
    If MyCountIf(hisdate, cell) <= 0 Then
        Set hisdate = Union(hisdate, cell)
    End If
Next
Debug.Print hisdate.Address

Function MyCountIf(fullRange As Range, val As Variant)
    MyCountIf = 0
    Dim r As Range
    For Each r In fullRange.Areas
        MyCountIf = MyCountIf + WorksheetFunction.CountIf(r, val)
    Next
End Function

answered Jul 03 '18 at 08:56

FunThomas

23,043
3
18
34

Thanks! This takes away the errors but the macro doesnt seem to be doing anything – user1234 Jul 04 '18 at 11:29
Then I would suggest to Debug the code (use F8) to see where it fails. I did a quick test with some example data and it worked (it added some cells to the range) – FunThomas Jul 04 '18 at 14:16

Checking if data exists and adding it if not

1 Answers1