Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |