We help IT Professionals succeed at work.

VBA Excel: checking a range to see if a number lies within the range

lossiemouth
lossiemouth asked
on
Hi experts,

I am using VBA in Excel.

I am trying to look through a range, in an Excel sheet, to see if the numbers within the range are > 0 but <50.
 
What code to I need to achieve this?

Many Thanks

Lossie
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
Personally i'd use worksheet functions for example countif ... but within VBA i.e. as below:

Chris
Dim rng As Range

    Set rng = ActiveSheet.Range("a1:B4")
    MsgBox CBool((Application.WorksheetFunction.CountIf(rng, ">0") = Application.WorksheetFunction.CountIf(rng, "<50")) And (Application.WorksheetFunction.CountIf(rng, ">0") = rng.Cells.Count))

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
BTW, I recognise the town ... spent many years up there in the past.

Chris

Explore More ContentExplore courses, solutions, and other research materials related to this topic.