Lookup VBA
 

I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is any
occupied cell in column K (K4 and down) with no data in the cell to the right
(column I). Then a message should pop up saying: "Please update the sheet".

Any suggestions?

Lookup VBA
 

Sub FindMissing()
Dim k As Long, i As Long
With Sheets("Sheet2")
k = Cells(Rows.Count, "K").End(xlUp).Row
For i = 4 To k
If Cells(i, "K").Value < "" And Cells(i, "I").Value = "" Then
MsgBox "Please update the sheet"
End If
Next
End With
End Sub

--
Gary''s Student - gsnu201001


"tomjoe" wrote:

I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is any
occupied cell in column K (K4 and down) with no data in the cell to the right
(column I). Then a message should pop up saying: "Please update the sheet".

Any suggestions?

Lookup VBA
 

First off, you say you want to check the cell to the **right** of Column K (for being blank), but then you identify that column as Column I... Column I is two columns to the **left** of Column K, not one cell to the right. I'm guessing you meant to type Column L and mistakenly typed I instead. The following macro assumes you meant Column L.

Okay, here is a macro that should do what you want without employing any loops...

Sub CheckColKandL()
Dim K As Range, L As Range
On Error Resume Next
Set K = Range("K4:K" & Rows.Count).SpecialCells(xlCellTypeConstants).Enti reRow
Set L = Range("L4:L" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireR ow
If Not K Is Nothing And Not L Is Nothing Then
If Not Application.Intersect(K, L) Is Nothing Then MsgBox "Please update the sheet"
End If
End Sub

--
Rick (MVP - Excel)


"tomjoe" wrote in message ...
I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is any
occupied cell in column K (K4 and down) with no data in the cell to the right
(column I). Then a message should pop up saying: "Please update the sheet".

Any suggestions?

Lookup VBA
 

I believe you should put an Exit For statement after your MsgBox statement,
otherwise you will pop up the MessageBox for each set of cells meeting the
If..Then test.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Sub FindMissing()
Dim k As Long, i As Long
With Sheets("Sheet2")
k = Cells(Rows.Count, "K").End(xlUp).Row
For i = 4 To k
If Cells(i, "K").Value < "" And Cells(i, "I").Value = "" Then
MsgBox "Please update the sheet"
End If
Next
End With
End Sub

--
Gary''s Student - gsnu201001


"tomjoe" wrote:

I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is
any
occupied cell in column K (K4 and down) with no data in the cell to the
right
(column I). Then a message should pop up saying: "Please update the
sheet".

Any suggestions?

Lookup VBA
 

I believe you should put an Exit For statement after your MsgBox statement,
otherwise you will pop up the MessageBox for each set of cells meeting the
If..Then test.

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
Sub isitthere()
Dim lr As Long, rng As Range
lr = ActiveSheet.Cells(Rows.Count, "K").End(slUp).Row
rng = ActiveSheet.Range("K4:K" & lr)
For Each c In rng
If c.Value "" And c.Offset(0, 1) = "" Then
MsgBox "Please Update the Sheet"
End If
Next
End Sub


"tomjoe" wrote in message
...
I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is
any
occupied cell in column K (K4 and down) with no data in the cell to the
right
(column I). Then a message should pop up saying: "Please update the
sheet".

Any suggestions?

Lookup VBA
 

Yep, you are correct, Rick. Actually, there is a lot more code that could
be added to make the process fully effective. I suppose the OP will figure
that out as they progress.


"Rick Rothstein" wrote in message
...
I believe you should put an Exit For statement after your MsgBox statement,
otherwise you will pop up the MessageBox for each set of cells meeting the
If..Then test.

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
Sub isitthere()
Dim lr As Long, rng As Range
lr = ActiveSheet.Cells(Rows.Count, "K").End(slUp).Row
rng = ActiveSheet.Range("K4:K" & lr)
For Each c In rng
If c.Value "" And c.Offset(0, 1) = "" Then
MsgBox "Please Update the Sheet"
End If
Next
End Sub


"tomjoe" wrote in message
...
I have a workbook with 2 sheets.
I am looking for a short procedure that detects if there, in sheet 2, is
any
occupied cell in column K (K4 and down) with no data in the cell to the
right
(column I). Then a message should pop up saying: "Please update the
sheet".

Any suggestions?