check entire column value

  • Thread starter Thread starter preethi b
  • Start date Start date
P

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
 
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
 
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
 
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" έγγÏαψε:
 
Thanks for the reply

I would like to print none only once for entire loop

after search completes

Thanks
Preethi
 
Back
Top