Solved how to find values in range of cells and display message

October 15, 2014 at 09:23:38
Specs: Windows 7
Using a macro, in cell range D2:D151, find values between 0 and 8 and if true display a message in cell F151 "No Jackpot this week". Can you help ?

See More: how to find values in range of cells and display message

Report •


#1
October 15, 2014 at 11:25:35
It's not clear to me what you are asking for.

You say you want to search a range and find values (plural) between 0 and 8. Do you mean you are looking for at least 2 values any where in that range that meet that criteria or do all values in that range have to meet that criteria, or do mean something totally different?

I'm also not sure why you want a macro, but that answer might depend on what you are trying to accomplish.

Just as a shot in the dark, this formula will return your message as long as at least 2 cells in that range are greater than 0 but less than 8:

=IF(COUNTIFS(D2:D151,">0",D2:D151,"<8")>1,"No Jackpot This Week","")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 15, 2014 at 11:38:19
Can be any number of values in the range. I have other macros which I also run for other single values and need another to include this range

Report •

#3
October 15, 2014 at 12:15:32
✔ Best Answer
What is wrong with using a formula? The formula I offered will continually update immediately based on the data in the range and not be dependent on a macro being run to update the cell.

As far as: "I have other macros which I also run for other single values and need another to include this range"...I'm once again confused.

Why not just add the test for these values to your existing macro instead of adding another one for this single task?

In any case, this code should do what you are asking for. It is just one of many, many ways to accomplish the goal of putting the message in F151 based on the values in the range.

Note: The code assumes Sheet(1), modify as required.

Sub Between0and8()
  If Application.WorksheetFunction.CountIfs _
    (Sheets(1).Range("D2:D151"), ">0", Sheets(1).Range("D2:D151"), "<8") _
  Then
     Sheets(1).Range("F151") = "No Jackpot This Week"
  Else: Sheets(1).Range("F151") = ""
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions


Ask Question