Can't transfer SUMPRODUCT function to VBA

[Julesdude]

Hello all,

I'm having difficulty transfering a worksheet function into VBA, assigning the result to a variable.

The function is

=SUMPRODUCT(1/COUNTIF(A3:A8,A3:A8))

How would I translate that into VBA. I keep getting type mismatches each time I assign it to the variable. Of course, I am using the application.worksheetfunction. etc prefix before doing this but to no avail.

Please can someone help. Thanks.

 

[Julesdude]

Thanks everyone with your help on this. There was me wrongly assuming that I could retain a simple one line worksheet formula into VBA.

Peter I've adapted your code slightly to tailor to my userform and it all seems to work perfectly:

Private Sub UserForm_Initialize()
Sheets("Sheet1").Select
lastrow = Sheets("Sheet1").UsedRange.Rows.count
 
Dim i As Long, j As Long, k As Long, x()
ReDim x(1)
x(0) = Range("C3").Value
For i = 6 To lastrow
    If IsError(Application.Match(Range("C" & i).Value, x, 0)) Then
        j = j + 1
        ReDim Preserve x(j)
        x(j) = Range("C" & i).Value
    End If
Next i
For k = LBound(x) To UBound(x)
    With UserForm.Combobox1
     .AddItem x(k)
    End With
Next k
End Sub

It might be possible to do this all in the same loop. I'll have a look.

Thanks again!

 

[Julesdude]

Hmmm, I was just testing this out a bit further and I got running into a problem.

I'm using the code above to assign the array with unique strings in a range. That works to a degree but it appears to be getting confused when it hits an empty/blank cell and is filling the array with each occurrence of blank cells.

Is there a way I can eliminate the blanks so that when the combobox is listed with the array, no blanks appear in it?

 

[VoG]

Perhaps

If Range("C" & i).Value <> "" And IsError(Application.Match(Range("C" & i).Value, x, 0)) Then

 

[Julesdude]

Thanks Peter, that works a treat. Is it better to use the IsEmpty argument instead of <> "" just on the off chance that a cell is blank but might contain a [space] ?

 

[VoG]

This should take care of spaces

If Trim(Range("C" & i).Value) <> "" And IsError(Application.Match(Range("C" & i).Value, x, 0)) Then

 

[Julesdude]

Ah ha - the Trim! Didn't even cross my mind to use that! There's always a number of ways. Thanks again to you and everyone else with your help on this.