Inserting Formula in Last Row with VBA

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
498
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
=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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Code:
With ActiveSheet
 .Cells(Rows.Count, 2).End(xlup)(3).Formula = "=SUM(IF(FREQUENCY(MATCH($B$3:lastrow,$B$3:lastrow,0),MATCH($B$3:lastrow,$B$3:lastrow,0))>0,1))"
End With

It might hiccup on the 'lastrow' as used in the formula. Wasn't sure if that is a named range or a VBA variable.
 
Last edited:
Upvote 0
I'm not a VBA expert, but look for solutions that simply work. This worked for me:

Code:
Sub Test()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("B1") = lastrow
ActiveSheet.Range("B1").Name = "lastrow"
ActiveSheet.Range("B" & lastrow + 2).Formula = "=SUM(IF(FREQUENCY(MATCH(INDIRECT(""$B$3:$B$""&lastrow),indirect(""$B$3:$B$""&lastrow),0),MATCH(INDIRECT(""$B$3:$B$""&lastrow),INDIRECT(""$B$3:$B$""&lastrow),0))>0,1))"
ActiveSheet.Range("B" & lastrow + 2) = ActiveSheet.Range("B" & lastrow + 2).Value
End Sub[code]

You could also delete the B1 cell in the macro once you calculate the answer.
This won't run properly a second time unless you delete the value created from the first run.
 
Upvote 0
Code:
Sub Test()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("B1") = lastrow
ActiveSheet.Range("B1").Name = "lastrow"
ActiveSheet.Range("B" & lastrow + 2).Formula =  "=SUM(IF(FREQUENCY(MATCH(INDIRECT(""$B$3:$B$""&lastrow),indirect(""$B$3:$B$""&lastrow),0),MATCH(INDIRECT(""$B$3:$B$""&lastrow),INDIRECT(""$B$3:$B$""&lastrow),0))>0,1))"
ActiveSheet.Range("B" & lastrow + 2) = ActiveSheet.Range("B" & lastrow + 2).Value
End Sub

Sorry
 
Upvote 0
Thanks JLGWhiz, the last row is a declared variable. Does that change the code?
 
Upvote 0
Kweaver, thank you, but that didn't quite work. I think the formula is a bit different then mine. Mine doesn't have the indirect function. Yours is giving a name error.

I'm trying to use this formula:
Rich (BB code):
=SUM(IF(FREQUENCY(MATCH($B$3:lastrow,$B$3:lastrow,0),MATCH($B$3:lastrow,$B$3:lastrow,0))>0,1))


Where the last row is a stored VBA variable.

Rich (BB code):
Sub Test()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("B1") = lastrow
ActiveSheet.Range("B1").Name = "lastrow"
ActiveSheet.Range("B" & lastrow + 2).Formula =  "=SUM(IF(FREQUENCY(MATCH(INDIRECT(""$B$3:$B$""&lastrow),indirect(""$B$3:$B$""&lastrow),0),MATCH(INDIRECT(""$B$3:$B$""&lastrow),INDIRECT(""$B$3:$B$""&lastrow),0))>0,1))"
ActiveSheet.Range("B" & lastrow + 2) = ActiveSheet.Range("B" & lastrow + 2).Value
End Sub

Sorry
 
Upvote 0
I realize that I used INDIRECT so that the formula would calculate using the name range. Otherwise, I believe it's the same formula.
You should get a name error because I assigned the name to B1 and in B1 put the row number of the last row.

I put the formula into B26 in this sample case, then replaced the formula with the actual value (I believe that's 15).

If you comment out the last line of the macro, the actual formula will go in B26 with "lastrow" being the name range in B1.

Code:
Sub Test()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("B1") = lastrow
ActiveSheet.Range("B1").Name = "lastrow"
ActiveSheet.Range("B" & lastrow + 2).Formula = "=SUM(IF(FREQUENCY(MATCH(INDIRECT(""$B$3:$B$""&lastrow),indirect(""$B$3:$B$""&lastrow),0),MATCH(INDIRECT(""$B$3:$B$""&lastrow),INDIRECT(""$B$3:$B$""&lastrow),0))>0,1))"
' ActiveSheet.Range("B" & lastrow + 2) = ActiveSheet.Range("B" & lastrow + 2).Value
End Sub
 
Upvote 0
Should have been: You should NOT get a name error because I assigned the name to B1 and in B1 put the row number of the last row.

But I can't seem to edit my post.
 
Upvote 0
I need to see the part of your code that instantiates the lastrow variable.
Code:
B$3:lastrow
This does not look right and I get errors 'expected end of statement' when I try to incorporate it into code to put it in a cell. The formula cannot be incorporated into code as you have it currently written and I can't make it work because I don't know what 'lastrow' really represents. Also, the Match part of the formula does not appear to be in compliance with the function guidelines.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top