How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask bbao Your Own Question
bbao
bbao, IT Consultant
Category: Microsoft Office
Satisfied Customers: 393
Experience:  CISM / CISSP / MCSD - Azure Solutions Architect / MCSE - Cloud Platform and Infrastructure / MS MVP
96068672
Type Your Microsoft Office Question Here...
bbao is online now
A new question is answered every 9 seconds

In VBA Excel, how do I change this to count the columns that

Customer Question

In VBA Excel, how do I change this to count the columns that have numbers only, instead of all of the columns... n = returns.Columns.Count?
JA: What's the make and model of your computer? And what version of Excel are you using?
Customer: HP Elitebook, Excel 2010
JA: Have you installed any updates recently?
Customer: yes
JA: Anything else you want the Microsoft Office expert to know before I connect you?
Customer: no
Submitted: 8 days ago.
Category: Microsoft Office
Expert:  bbao replied 8 days ago.

You actually don't need VBA for counting non-null numbers, just Excel function can do that.

Customer: replied 8 days ago.
Great! How do I do that? I've tried within my VBA function and outside of it, both fail.
Expert:  bbao replied 8 days ago.

There are a number of ways to do that depending on your particular scenario. For example:

1. To count A1:A100 for any non-blank cells: =COUNTA(A1:A100)

2. To count A1:A100 for any numbers > 0: =COUNTIF(A1:A100,">0")

3. To count A1:A100 for any text: =COUNTIF(A1:A100,"*")

4. To count A1:A100 for cells with at least one character: =SUMPRODUCT(--(LEN(A1:A100)>0))

Expert:  bbao replied 7 days ago.

Any feedback please?

Customer: replied 7 days ago.
How should I update my function that is performing the current count "n = returns.Columns.Count" to use this "=COUNTIF(A1:A100,">0")"?
Expert:  bbao replied 7 days ago.
Just put the COUNTIF function in a cell then assign the cell's value to a VBA variable or an object's property.BTW, why do you have to use VBA or macro code?
Customer: replied 7 days ago.
I don't know how to do that
Expert:  bbao replied 7 days ago.

Okay, does this help?

https://powerspreadsheets.com/excel-worksheet-function-vba/

Customer: replied 7 days ago.
Here is my function. How would be best to update it or to use it as you state?Function SortinoRatio(returns As Range, MAR As Variant) As Variant
Dim n As Integer
Dim i As Integer
Dim avgReturn As Double
Dim mm2d As Double
Dim downDev As Double
Dim x
x = aj11
n = returns.Columns.Count
avgReturn = WorksheetFunction.Average(returns)
moment2d = 0
For i = 1 To n
If returns(i) - MAR < 0 Then
mm2d = mm2d + ((returns(i) - MAR) ^ 2)
End If
Next
downDev = Sqr(mm2d / n)
If downDev > 0 Then
SortinoRatio = (avgReturn - MAR) / downDev
Else
SortinoRatio = "no losses"
End If
End Function
Customer: replied 7 days ago.
The "x = aj11" isn't being used for anything. That line is just a test.
Expert:  bbao replied 7 days ago.

Just replace

n = returns.Columns.Count

with

n = WorksheetFunction.CountA(returns)

or

n = WorksheetFunction.COUNTIF(returns,">0")

Related Microsoft Office Questions