Jav_uk8

New Member
Joined
Jan 12, 2017
Messages
22
Hi All,

I need help on the following VBA code.

I need to amend the formula below into a VBA code. I need to amend the 'O2:O662' section to show O2:O" & lastrow (used in previous VBA) or something that will work.

=CONCATENATE("Number of Cases"," ",COUNTIF(O2:O662,">=£0.01")," ","out of"," ",COUNTA(O2:O662))

Hope this makes sense?

Cheers :)
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Say you want to place this formula in cell A1. Then assuming you have already calculated lastrow, you would just use:
Code:
Range("A1").Formula = "=CONCATENATE(""Number of Cases"","" "",COUNTIF(O2:O" & lastrow & ","">=£0.01""),"" "",""out of"","" "",COUNTA(O2:O" & lastrow & "))"
If you have not already calculated lastrow, you would do so like this:
Code:
Dim lastrow as Long
lastrow = Cells(Rows.Count,"O").End(xlUp).Row
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,270
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top