PDA

View Full Version : [SOLVED] How to check whether Cells contains specific number?



Cinema
12-27-2016, 06:08 AM
Hi I want to check if Range("G8:G" & lr) contains the Value 1. First I am checking if Range("G8:G" & lr) are blank then I want to check if the cells contain the value "1". I get an error for < Range("G8:G" & lr).Value = "1" >
Here is my Code so far.




Dim tool As Workbook
Dim sh2 As Worksheet
Dim lr As Long
Dim rng As Range

Set sh2 = Sheets("Data")
Set tool = ThisWorkbook

lr = sh2.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = tool.sh2.Range("G8:G" & lr)

If WorksheetFunction.CountA(rng) = 0 Or Range("G8:G" & lr).Value = "1" Then
.....

mana
12-27-2016, 07:12 AM
Set sh2 = ThisWorkbook.Sheets("Data")

lr = sh2.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = sh2.Range("G8:G" & lr)

If WorksheetFunction.CountIf(rng, 1) > 0 Then

mancubus
12-27-2016, 07:13 AM
If WorksheetFunction.CountA(rng) = 0 Or WorksheetFunction.CountIf(rng, 1) > 0
or

If Application.CountA(rng) = 0 Or Application.CountIf(rng, 1) > 0

Cinema
12-27-2016, 07:48 AM
Hi mana and mancubus,

this works perfect.
Thank you very much.