# 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

#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","")

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
 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```message edited by DerbyDad03