Excel VBA - Formula writing error

Question

I am trying to bring a formula inside my vba code and I am getting an error inside it. Please have a look into the code and kindly share your thoughts.

This is my excel function that was written in VBA Code :

GetUniqueCount(Range,Value)

And here is the VBA Code trying to make use of it :

Sheets("sheet2").Activate
        With ThisWorkbook.Sheets("sheet2").UsedRange
            lastrow = .Rows(.Rows.Count).Row
        End With

    For i = 14 To lastrow
        check = Range("h" & i).Value
        If check <> "" Then
         Range("I" & i).Value = WorksheetFunction.GetUniqueCount(sheet1!.Range("A1:B100"), check)
        Else
        Range("I" & i).Value = ""
    Next

The range for the function comes from a different sheet. How do I write it in VBA?

This is the function for it :

Function GetUniqueCount(Rng1 As Range, Lookup As String) As Long
Dim x, dict
Dim i As Long, cnt As Long
Set dict = CreateObject("Scripting.Dictionary")
x = Rng1.Value
For i = 1 To UBound(x, 1)
    If x(i, 1) = Lookup Then
        dict.Item(x(i, 1) & x(i, 2)) = ""
    End If
Next i
GetUniqueCount = dict.Count
End Function

yes. I defined that function inside my VBA code in a separate module. How do I call that function and How do I assign the range for it from a separate sheet? — Siddharth Thanga Mariappan
is it "Sheet1" as sheet's name ? or sheet1 as CodeName ? — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2017-04-11T10:09:05

You have other possible errors in your code, unqualified Range, etc.

Since your Function GetUniqueCount is not Excel's built in WorksheetFunction, but your own UDF, you don't need to call it with WorksheetFunction.GetUniqueCount but just GetUniqueCount.

Try the code below:

Option Explicit

Sub Test()

Dim LastRow As Long, i As Long
Dim check As String

    With ThisWorkbook.Worksheets("sheet2")
        LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row

        Dim Rng As Range
        Set Rng = Worksheets("sheet1").Range("A1:B100")

        For i = 14 To LastRow
            check = .Range("H" & i).Value
            If check <> "" Then
                .Range("I" & i).Value = GetUniqueCount(Rng, check)
            Else
                .Range("I" & i).Value = ""
            End If
        Next i
    End With

End Sub

Excel VBA - Formula writing error

2 Answers