VBA Help

[Jav_uk8]

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

 

[Joe4]

Say you want to place this formula in cell A1. Then assuming you have already calculated lastrow, you would just use:

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:

Dim lastrow as Long
lastrow = Cells(Rows.Count,"O").End(xlUp).Row

 

[Jav_uk8]

Hi Joe4,

Works perfectly!

Thank you

 

[Joe4]

You are welcome.