Help with some code please

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

This is what I want this particular piece of code to do:

Check column B, if the value in column B is 1 then check column's K and L to
see if either of them have an entry in them. If there is an entry in
either, or both, and column F does not contain 'No' then display a msgbox
with the value of column C in it.

This is what I have so far, I am am stuck on the And Or bits:

Sub Check()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set rng = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
End With
For Each cell In rng
If cell.Value = 1 And .......Or ......And ......Then
MsgBox "Please check " & cell.Offset(0,1).Value
End If
Next cell
Application.ScreenUpdating = True
End Sub

Thanks in advance.

Gareth
 
Sub Check()
Dim rng As Range
Dim cell As Range

With Worksheets("Sheet1")
Set rng = .Range("B2:B" & .Range("B65536").End(xlUp).Row)
End With

For Each cell In rng
With cell
If .Value = 1 And .Offset(0, 4) <> "No" _
And (.Offset(0, 9).Value <> "" Or .Offset(0, 10).Value <> "")
Then
MsgBox "Please check " & .Offset(0, 1).Value
End If
End With
Next cell
End Sub

(I declared the variables, since I use Option Explicit.)

Note that this does not handle the situation where the value in B, F, K
or L are errors. Some additional code would be required to check for
this (or On Error Resume Next, I guess).
 
Back
Top