Lookup VBA

  • Thread starter Thread starter tomjoe
  • Start date Start date
T

tomjoe

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?
 
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
 
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
 
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).EntireRow
Set L = Range("L4:L" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow
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
 
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.
 
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.
 
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.
 
Back
Top