Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I am working on writing a simple macro that computes the average, but

I am having trouble figuring out how to count the number of values so

that I know what to divide by...

This is what I have so far:

Function Average(rngNumbers As Range)

Average = 0

Sum = 0

NumValues = ActiveSheet.UsedRange.Rows.Count

For Each addrNumber In rngNumbers

Sum = Sum + addrNumber.Value

Next addrNumber

Average = Sum / NumValues

End Function

Is there any other way to count variables?

Any insight would be appreciated.

: I am working on writing a simple macro that computes the

: average, but I am having trouble figuring out how to count the

: number of values so that I know what to divide by...

AVERAGE is already a built-in function in Excel. I assume you

are doing this for an exercise in writing macros.

: This is what I have so far:

:

: Function Average(rngNumbers As Range)

:

: average = 0

: sum = 0

: NumValues = ActiveSheet.UsedRange.Rows.Count

Do more testing.

Create a new workbook. Add three small ranges separated by

empty cells. Extend one range to be longer than the other two. Go

to the VB Editor (Alt+F11). Open the Immediate Window (Ctrl+G).

Shrink the window size down enough to see the worksheet. Add the

following to the window and press Enter.

ActiveSheet.UsedRange.Select

Do you see how all those empty cells were selected? Once you

see a visual representation of the cells you are selecting, you

may write better code. When I am having problems with a function,

I often start looking at each line of code to test my assumptions.

Showing which cells are selected is very helpful.

While we could use this method to count the cells in a range,

but the range we are interested in is already in rngNumbers. It

would be better to count the cells in that range.

Let's write a test function to count the cells in a range.

I realize this seems like overkill, but down the line you can

write tests for much more complex solutions. Mistakes in some of

those complex solutions may cost people money. Lot's of money.

Add this function to the new spreadsheet we created above.

Add the following to a few cells.

Function TestCount(CountRange As Range, KnownCount as Integer)

Dim iCount As Integer

iCount = CountRange.Rows.Count

TestCount = "Count test failed. Count = " & iCount

If iCount = KnownCount Then TestCount = "Count test Succeeded."

End Function

=TestCount(E4:G6, 9)

=TestCount(E1:E6, 6)

=TestCount(E3:G3, 3)

Of course this doesn't help us out since the count is still

wrong. A little more testing on other ranges reveals that

TestCount always returns the number of rows in the range. Careful

examination shows that we are counting the wrong thing.

CountRange.Rows.Count

Since we are counting cells, let's try this.

CountRange.Cells.Count

Change the function and recalculate the worksheet (F9). All the

tests should succeed.

Function TestCount(CountRange As Range, KnownCount as Integer)

Dim iCount As Integer

iCount = CountRange.Cells.Count

TestCount = "Count test failed. Count = " & iCount

If iCount = KnownCount Then TestCount = "Count test Succeeded."

End Function

Further testing revealed that CountRange.Count works as well.

Next, you might write a function to test the average of a range of

cells

Can you use the following?

TestRange = Range("D6:D8")

AverageTest = Application.WorksheetFunction.Average(TestRange)

I'm not sure where you are having trouble or why you want a different way of

counting values, but some comments on your code ...

Your For Each loop is looking at every cell in the range, yet your NumValues

is taken from a count of just the rows. This will not do what you want if

the range is wider than one column. You should be able to use the Count

attribute against the range itself to get the actual number of cells in the

range.

But you say "values". Do you want not to count empty cells? Look at

CountA.

You don't need a loop to sum a range. There is a Sum function. There is

also an Average function.

Didn't find what you were looking for?
Find more on Average Macro Help
Or get search suggestion and latest updates.

Related Topics:

- Average Macro Help
- Removing macro's shortcut key
- Removing a vba module or macro with another macro
- Macro's In Excel
- How to Make a Unsigned macro to Signed Macro
- Macro Macros and Increasing VBA Password Security Questions
- Need help with macro
- Help editing a Macro
- Help with macro
- Macro help newbie: VB conditional if statement to copy paste cell
- Excel Macro Help Needed
- help with making macro with user input
- need macro help document
- wondering if I could get some macro/vba help
- Help with Macro
- excel Macro Help
- help with freezing panes so labels scroll with the page macro
- inputbox macro help
- macro help
- Help on VBA macro
- Report creation through VBA macro help required!
- comp macro value pls help
- Need Help for Macro to Perform Same Function Only on Certain Sheets
- Need help w/creating macro to delete rows
- Excel Macro help - conditional copy and paste from one worksheet to another