Inserting Formula in Last Row with VBA

[nirvehex]

Hi,

I have a formula which I'm trying to put in the last row of my spreadsheet.

Here is basically what I'm trying to do.

In column B, starting in B3:last row. I want to have this formula populate two rows below the last row:

=SUM(IF(FREQUENCY(MATCH($B$3:lastrow,$B$3:lastrow,0),MATCH($B$3:lastrow,$B$3:lastrow,0))>0,1))

So if the data runs b3:b24, it should give me a unique count of b3:b24 in cell b26.

Any thoughts how to do this with VBA?

Thanks!

 

[JLGWhiz]

You can't use "lastrow" in the formula that would be inserted in the cell via VBA because "lastrow" is a number and not a cell reference. That's why I used INDIRECT.

That is what I want to determine for certain. The formula as shown in the OP would be erroneous from the start, so we need to know exactly what it is supposed to be matching. I don't believe you can use a range of values as the value to find in a Match function. The entire fomula needs to be re-worked.

 

[nirvehex]

Here is my code so far. It defines NumberofRows from a separate tab and stores that count.

My headers are in row 2 on Details tab. Then if you hit control down you get to the last row of data. Then two rows below that last row I want to write the unique count of what's in column B3:NumberofRows. Everything is working except the formula is messed up. It keeps giving me a #NAME ? error. I must not be writing it correctly.

Sub SetupTest()




'1. Select Pivot, Select B4, control+shift down, count filled rows, store count




Dim NumberofRows As Long
Application.ScreenUpdating = False


Sheets("Pivot").Select
Range("G4").Select
Range(Selection, Selection.End(xlDown)).Select




NumberofRows = Selection.Count


Sheets("Details").Select


With ActiveSheet
.Range("B" & NumberofRows + 3).Formula = "=SUM(IF(FREQUENCY(MATCH(""$B$3:$B$""&NumberofRows,""$B$3:$B$""&NumberofRows),MATCH(""$B$3:$B$""&NumberofRows,""$B$3:$B$""&NumberofRows,0))>0,1))"
.Range("B" & NumberofRows + 3) = ActiveSheet.Range("B" & NumberofRows + 3).Value
End With


End Sub

 

[nirvehex]

.

 

[nirvehex]

Nevermind, figured it out:

Sub SetupTest2()




'1. Select Pivot, Select B4, control+shift down, count filled rows, store count


Dim MyUnique As Object, c As Range
Dim UniqueCount As Integer


Set MyUnique = CreateObject("Scripting.Dictionary")


Sheets("Details").Select
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select


For Each c In Selection
        If c.EntireRow.Hidden = False Then MyUnique(CStr(c)) = 1
    Next c


With ActiveSheet
Range("B3").Select
Selection.End(xlDown).Select
Selection.Offset(2, 0).Select


End With


Selection.Value = MyUnique.Count


End Sub