check entire column value

[preethi b]

Please provide me solution

for each value in column "G" if value = "y"
then check respective value in column "b" value = Null
then
print message its none

Thanks in Advance



*** Sent via Developersdex http://www.developersdex.com ***

 

[JLGWhiz]

Sub naught()
Dim lr As Long, srcRng As Range, c As Range
lr = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
Set srcRng = ActiveSheet.Range("G2:G" & lr)
For Each c In srcRng
If Range("B" & c.Row) = "" Then
MsgBox "It's None " & Range("B" & c.Row).Address
End If
Next
End Sub

 

[JLGWhiz]

Missed part of the criteria. Use this one.

Sub naught()
Dim lr As Long, srcRng As Range, c As Range
lr = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
Set srcRng = ActiveSheet.Range("G2:G" & lr)
For Each c In srcRng
If LCase(c.Value) = "y" Then
If Range("B" & c.Row) = "" Then
MsgBox "It's None " & Range("B" & c.Row).Address
End If
End If
Next
End Sub

 

[John_John]

An other way with less loops:

Sub CheckOutNulls()
Dim rngNulls As Range
Dim rngCell As Range

On Error GoTo ErrorHandler
Set rngNulls = Range("B:B") _
.SpecialCells(xlCellTypeBlanks)

For Each rngCell In rngNulls.Cells
If rngCell.Offset(, 5) = "y" Then
MsgBox "It's none: " & rngCell.Address
'more code here
End If
Next rngCell
ExitProc:
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbExclamation
Resume ExitProc
End Sub

Ο χÏήστης "JLGWhiz" έγγÏαψε:

 

[preethi b]

Thanks for the reply

I would like to print none only once for entire loop

after search completes

Thanks
Preethi



*** Sent via Developersdex http://www.developersdex.com ***

 

[preethi b]

hi,

Any reply?



*** Sent via Developersdex http://www.developersdex.com ***