VBA to check if range is populated

[cuddihy2k7]

Hi, I have a spreadsheet that takes input from a balance, (weighing scales),

I need some vba that would calculate the mean (average weight) and put it in cell D21, but before it does this, the conditions are that the activecell row must be 21 and the range (D1120) must all be populated.

so far I have got

If ActiveCell.Row = 21 Then
Range("D21").Activate
ActiveCell.Locked = False
ActiveCell.Value = WorksheetFunction.Average(Range("D11", "D20"))

this works if the activecell row is 21, but since this is going out to users of the company it must only do this if the range is populated, my knowledge of vba is quite limited so if someone were able to help me out it would be great.

 

[Sifou]

Hi,

we would not mind giving you more help if you gave feedback on the previous post you submitted (as I recognise your D11:21 issue , definitely related to this one! (https://www.pcreview.co.uk/forums/excel-average-vba-t4037683.html#post14096129)

If you look at the answer I gave in your other post, the fact that i use the count method to find the average, it would have divided the sum of the range only by the number of populated cells.

Sincerely,
Sifou

 

[cuddihy2k7]

Sifou, the code you posted previously works pretty well with one of the spreadsheets that I have created, as it was only meant to take into account the number of populated cells so thanks for your help, however, I have been working on a second spreadsheet and the requirements of this spreadsheet differs from the first.

This spreadsheet needs all cells to be populated before it finds out the average, i.e.

if one cell in the range D1120 is not populated, then it should not work out the average at all.

the code I have used for this is;

If ActiveCell.Row = 21 Then
If Application.WorksheetFunction.Count(Range("D11", "D20")) = 10 Then

Range("D21").Activate
ActiveCell.Locked = False
ActiveCell.Value = WorksheetFunction.Average(Range("D11", "D20"))
dlow = Mid(Range("G22"), 1, 4)
dhigh = Mid(Range("G22"), 7, 11)
If ActiveCell.Value < dlow Or ActiveCell.Value > dhigh Then
ActiveCell.Interior.Color = vbRed
Else
ActiveCell.Interior.Color = vbGreen
End If
ActiveCell.Locked = True


End If

but it doesn't seem to work, it still works out the average even if one cell in the range is not populated.

 

[Sifou]

Hi Your function works fine however you are missing an End If. I tried it and it run only if all the cells are populated.

Have a great day!
Sifou

 

[Sifou]

I am running Excel 2007 by the way....

what happens when you run it? have you tried putting breakpoints on your code to see how it execute it and what the values of each of your variables are ?

Sifou

 

[cuddihy2k7]

it still didn't work with the end if, instead I used

If Application.WorksheetFunction.CountIf(ActiveSheet.Range("D2938"), "> 0") = 10

and it is working fine, thanks for your time and effort though, as this was helpful in the long run