• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look for ANY blank cells in a column, IF found show message box

C

C-C

New Member
IF there is data in Column "A" THEN look through Column "M" for any blanks.
As soon as it locates a blank, show message "BLANKS FOUND" and end sub.

If blank is not found, show message "NO BLANKS FOUND" and end sub.

**When the data in column A runs out, then stop running the code (and provide whichever message is applicable of the 2 aforementioned) (don't want it picking up the blanks at the bottom of a worksheet that are not part of a row of content)...
 
Hi CC

Welcome to Chandoo :)

This should sort you out for the blanks in Col M.

Code: 
Sub MBlank()
    Dim cnt As Long
    cnt = WorksheetFunction.CountBlank(Range("M1", Range("M" & Rows.Count).End(xlUp)))
    MsgBox "There are " & cnt & " blanks."
End Sub

Take care

Smallman
 
Last edited:
Thanks Smallman, the counting feature added to the code is throwing me off - (don't need it to count)
Simply want it to recognize whether or not there's a blank.
Ultimately, I was needing to try to incorporate that piece of IF/THEN code into doing something like this -- but can't get this one to work...

  • If there ARE any blanks found in Col "O" then I want it to automatically run Module 36
  • If there ARE NO BLANKS found in Col "O" then I want it to automatically run Moduel 36B
Anyone know what's wrong with this -- greatly appreciated...
Code: 
Sub RunMod()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).row
    Dim rng As Range
    For Each rng In Range("O5:O" & bottomA)
        If rng = "" Then
            Call Module36
        Else
            Call Module36B
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:
counting feature added to the code is throwing me off
Click to expand...
Don't let it throw you off. Use it to your advantage. Think a bit further and most of all don't loop through ranges.
Code: 
Sub MBlank()
    Dim cnt As Long
    cnt = WorksheetFunction.CountBlank(Range("M1", Range("M" & Rows.Count).End(xlUp)))
    If cnt = 0 Then
    'processA
    Else
    'Process B
    End Sub
End Sub

Take care

Smallman
 
Last edited:
  • Like
Reactions: C-C
Getting error: Compile Error: "Block If without End If" and the first "End Sub" is highlighted... Any ideas?
 
Smallman's code should be

Code: 
Sub MBlank()
Dim cnt As Long
cnt = WorksheetFunction.CountBlank(Range("M1", Range("M" & Rows.Count).End(xlUp)))
If cnt = 0 Then
    'processA  
Else
    'Process B  
End if
End Sub
 
  • Like
Reactions: C-C
Ooops sorry I was free coding into Chandoo and typed End Sub twice. Sorry for the confusion.

Smallman
 
OK guys, assuming you will be amused....:eek::D I'll post the full Module for you to see what I'm trying to accomplish.
The module RUNS - but does not appear to be working properly with the new piece of code added from this post.
Looks like it is not properly evaluating and taking the appropriate path...
I tested it on a file where Col O had blanks and a diff file where it did NOT have blanks to see if it re-acted appropriately to auto-use "Process A" vs "Process B". It appears to be IGNORING the "ELSE" and always runs Process A regardless of if Col O has blanks or not...
Perhaps you'll see something that is preventing it from making an accurate decision???

BACKGROUND: I've created a custom ribbon/toolbar for an analyst to use during their day to day forecasting. Each icon/button along their toolbar runs an automated series of steps for the analyst to increase the speed and accuracy of manual effort.
THIS large chunk of code (be nice) is tied to a single button.
Instead of making the analyst physically look through Column "O" for blanks and make a manual decision which button to use, I wanted the code to be smart enough to evaluate, then take the appropriate path of code.

Feel free to critique/offer alternatives.

>>> Since there is a 10,000 character limitation, I'll have to split the 2nd 1/2 of the code off and put it in a 2nd follow-on post... <<<<

Code: 
Sub MOD_46_MBlank()


'THIS MOD NEEDS TO DO 2 THINGS
'---------------------------------
'1 - CHECK COLUMN O (NOUN) FOR ANY BLANKS,
' ....IF FOUND,.......... THEN RUN THE NORMAL C-SYSTEM CODE taken from "MOD36" (10 steps)
' ....IF NO BLANKS FOUND, THEN RUN THE NEW CODE taken from "MOD36B" containing less steps providing user the ability to bi-pass 5 of the steps necessary in the other process.


'-----------------------------------------------------------'  

Dim cnt As Long
    cnt = WorksheetFunction.CountBlank(Range("O1", Range("O" & Rows.Count).End(xlUp)))
    If cnt = 0 Then

    'processA
    'IF NO BLANKS ARE FOUND IN COLUMN O THEN RUN CODE FOR Sub MOD_36B()
    'Multiple steps are automated to cut down time expended by the analyst during forecasting efforts



'-----------------------------------------------------------'  
'THIS SECTION HIDES ALL THE COLUMNS THAT WE DON'T NEED TO SEE DURING C-SYSTEM CALCULATION EFFORTS

    Columns("B:B").Select
    Range("B4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("C:C").Select
    Range("C4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("D:D").Select
    Range("D4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("E:E").Select
    Range("E4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("F:F").Select
    Range("F4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("H:H").Select
    Range("H4").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("J:J").Select
    Range("J2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("K:K").Select
    Range("K2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("L:L").Select
    Range("L2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("M:M").Select
    Range("M2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("N:N").Select
    Range("N2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("O:O").Select
    Range("O2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("P:P").Select
    Range("P2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("Q:Q").Select
    Range("Q2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("R:R").Select
    Range("R2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("S:S").Select
    Range("S2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("U:U").Select
    Range("U2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("V:V").Select
    Range("V2").Activate
    Selection.EntireColumn.Hidden = True
  
    Columns("W:W").Select
    Selection.EntireColumn.Hidden = True

'-----------------------------------------------------------'  
'THIS SECTION INSERTS THE HEADERS FOR AE, AF and AG - THE COLUMNS THAT WILL SOON HOLD FORMULAS AND CALCULATIONS
'THIS SECTION ALSO INSERTS THE FORMULAS TO PERFORM THE CALCULATIONS

    Range("AE4").Select
    ActiveCell.FormulaR1C1 = "y1"
    Range("AF4").Select
    ActiveCell.FormulaR1C1 = "y2"
    Range("AG4").Select
    ActiveCell.FormulaR1C1 = "TOT"
  
    Range("AE5").Select
    ActiveCell.FormulaR1C1 = "=RC[-24]"
    Range("AF5").Select
    ActiveCell.FormulaR1C1 = "=RC[-23]"
    Range("AG5").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]+RC[-1])/730"
    Columns("AE:AG").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


'-----------------------------------------------------------'
    'THIS SECTION TAKES THE RESULT GENERATED IN COL AG "TOT" AND PASTES IT INTO COL T.
    'THE NUMBER '13' FOUND WITHIN THE PARENS IS COUNTING NUMBER OF COLUMNS TO THE RIGHT
    'OF "T5" WHICH IS COL 'AG's contents
    'THIS new code performs the copy paste downward task but
    'only to the point where data exists in column A
    
    'THE OLD CODE COPIED THE CONTENTS AND THE FORMATTING FOUND IN T5 DOWNWARD, BUT DON'T WANT IT TO COPY THE COLOR
    'OF THAT CELL OR BORDER FORMATTING DOWNWARD
    'DO THE CODE LIKE THIS TO AVOID COPYING FORMATTING DOWNWARD/FILLING DOWN
    'THE NEW BELOW CODE DOES NOT COPY IT DOWNWARD - ALSO CORRECTED TO BE 14 if using AH, or 13 if using AG as TOT column.

  
    Range("T5").FormulaR1C1 = "=RC[13]"
    Range("AE5:AG5").AutoFill Destination:=Range("AE5:AG" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillDefault
    Range("T5").AutoFill Destination:=Range("T5:T" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillValues


'-----------------------------------------------------------'
'THIS CODE HIDES COLUMNS X:AD
'

'
    Columns("X:AD").Select
    Selection.EntireColumn.Hidden = True

      
'-----------------------------------------------------------'
'THIS LAST PIECE POPS UP A MESSAGE TO THE USER TO GUIDE THEM ON WHAT TO DO NEXT USING AN OUTSIDE SYSTEM
'THIS IS A SIMPLE MESSAGE BOX

MsgBox ("THIS VIEW ALLOWS YOU TO UPDATE" & vbCrLf & "ANY MANUAL OVER-RIDES NEEDED" & vbCrLf & "WITH YOUR  'y1', 'y2'  CALCULATIONS" & vbCrLf & vbCrLf & "Click OK to proceed")

'THESE ARE 2 DIFFERENT EXAMPLES OF HOW TO ACHIEVE MY MESSAGE
'MsgBox ("This is row one," & vbCrLf & "This is row two," & vbCrLf & "This is row 3.")
'MsgBox ("Looks like you can use this too" & vbNewLine & "then this for row two" & vbNewLine & "and so on...")



  
  
  
Else
'==================================================================
'==================================================================
'==================================================================
'see other code window to see the rest of this code (had to split it due to a 10,000 character post limitation <<<<<<<<<<<

    'Process B
    'Sub MOD_36()
'
'IF BLANKS ARE FOUND IN COLUMN O THEN RUN CODE FOR Sub MOD_36B()
    'Multiple steps are automated to cut down time expended by the analyst during forecasting efforts
'-----------------------------------------------------------'
 
..... CONTINUED:
here is the other 1/2 of the code that had to be cut from the bottom due to a 10,000 character post limitation.
Hopefully you guys can see what might be keeping it from running the correct Process between A and B options.

Code: 
Else
'==================================================================
'==================================================================
'==================================================================

    'Process B
    'Sub MOD_36()
'
'IF BLANKS ARE FOUND IN COLUMN O THEN RUN CODE FOR Sub MOD_36B()
    'Multiple steps are automated to cut down time expended by the analyst during forecasting efforts
'-----------------------------------------------------------'

'THIS SECTION HIDES ALL THE COLUMNS THAT WE DON'T NEED TO SEE DURING C-SYSTEM CALCULATION EFFORTS

    Columns("B:B").Select
    Range("B4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("C:C").Select
    Range("C4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("D:D").Select
    Range("D4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("E:E").Select
    Range("E4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("F:F").Select
    Range("F4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("H:H").Select
    Range("H4").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("J:J").Select
    Range("J2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("K:K").Select
    Range("K2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("L:L").Select
    Range("L2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("M:M").Select
    Range("M2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("N:N").Select
    Range("N2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("O:O").Select
    Range("O2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("P:P").Select
    Range("P2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("Q:Q").Select
    Range("Q2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("R:R").Select
    Range("R2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("S:S").Select
    Range("S2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("U:U").Select
    Range("U2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("V:V").Select
    Range("V2").Activate
    Selection.EntireColumn.Hidden = True
 
    Columns("W:W").Select
    Selection.EntireColumn.Hidden = True
 
'-----------------------------------------------------------
'THIS SECTION INSERTS THE HEADERS FOR AE, AF and AG - THE COLUMNS THAT WILL SOON HOLD FORMULAS AND CALCULATIONS
'THIS SECTION ALSO INSERTS THE FORMULAS TO PERFORM THE CALCULATIONS

    Range("AE4").Select
    ActiveCell.FormulaR1C1 = "y1"
    Range("AF4").Select
    ActiveCell.FormulaR1C1 = "y2"
    Range("AG4").Select
    ActiveCell.FormulaR1C1 = "TOT"
 
    Range("AE5").Select
    ActiveCell.FormulaR1C1 = "=RC[-24]"
    Range("AF5").Select
    ActiveCell.FormulaR1C1 = "=RC[-23]"
    Range("AG5").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]+RC[-1])/730"
    Columns("AE:AG").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


'-----------------------------------------------------------
    'THIS SECTION TAKES THE RESULT GENERATED IN COL AG "TOT" AND PASTES IT INTO THE "DDR" COL T.
    'THE NUMBER '13' FOUND WITHIN THE PARENS IS COUNTING NUMBER OF COLUMNS TO THE RIGHT OF "T5" WHICH IS COL 'AG's contents
     
        'THIS code performs the copy paste downward task but
        'only to the point where data exists in column A
        'THE CODE COPIES THE CONTENTS AND THE FORMATTING FOUND IN T5 DOWNWARD, BUT DON'T WANT IT TO COPY THE COLOR
        'OF THAT CELL OR BORDER FORMATTING DOWNWARD
        'DO THE CODE LIKE THIS TO AVOID COPYING FORMATTING DOWNWARD/FILLING DOWN
        'THE NEW BELOW CODE DOES NOT COPY IT DOWNWARD - ALSO CORRECTED TO BE 14.


 
    Range("T5").FormulaR1C1 = "=RC[14]"
    Range("AE5:AG5").AutoFill Destination:=Range("AE5:AG" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillDefault
    Range("T5").AutoFill Destination:=Range("T5:T" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillValues


     
'-----------------------------------------------------------
    'THIS SECTION PUTS IN ALL THE COL HEADERS OF "X THROUGH AD"
   
 
    Range("X4").Select
    ActiveCell.FormulaR1C1 = "NIIN"
 
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "Noun"
 
    Range("Z4").Select
    ActiveCell.FormulaR1C1 = "AAC"
 
    Range("AA4").Select
    ActiveCell.FormulaR1C1 = "Blank"
 
    Range("AB4").Select
    ActiveCell.FormulaR1C1 = "Report NIIN"
 
    Range("AC4").Select
    ActiveCell.FormulaR1C1 = "Report Noun"
 
    Range("AD4").Select
    ActiveCell.FormulaR1C1 = "Report AAC"
 
 
'-----------------------------------------------------------

'Sub mod_39_COPA_add_formulas_x_to_z_MOD_39()
'
' THIS SECTIONS ADDS THE FORMULAS TO X,Y,Z AND pastes and drags downward in x through z
'

'THIS FORMULA WAS ENTERED INTO X5
    Range("X5").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-23],5,9)"
 
'THIS FORMULA WAS ENTERED INTO Y5
    Range("Y5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[3]:C[4],2,FALSE)"
 
'THIS FORMULA WAS ENTERED INTO Z5
    Range("Z5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],C[2]:C[4],3,FALSE)"
 
' DRAG FORMULAS FROM X TO Z DOWN TO BOTTOM
 
    Range("X5:Z5").Select
    Range("X5:Z5").AutoFill Destination:=Range("X5:Z" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillDefault

'-----------------------------------------------------------
'THIS SECTION WAS TAKEN FROM MOD 39, SUB MACRO6, IT SIMPLY COPIES "Y" RESULTS TO "O"

'Sub Macro6()
'
' Macro6 Macro
' select all the cells in Y and paste all to O

'
    Range("Y5").AutoFill Destination:=Range("Y5:Y" & Range("A" & Rows.Count).End(xlUp).row), Type:=xlFillDefault
    Selection.Copy
    Range("O5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
'-------------------------------------------------------------
'THIS SIMPLY SAVES THE WORKBOOK (don't want it turned on just yet - with test files)
    'ActiveWorkbook.Save
 


'------------------------------------------------------------

'THIS SECTION COPIES THE RESULTS SITTING IN COLUMN X AND PLACES THAT LIST OF DATA ONTO THE CLIPBOARD
'ITS NOW READY TO CLICK PASTE -- ONCE THE USER GETS INTO THEIR EMALL APPLICATION
  'Sub CopyToClipboard()

    Range("X5:X5000").Copy




'-------------------------------------------------------------
'THIS LAST PIECE POPS UP A MESSAGE TO THE USER TO GUIDE THEM ON WHAT TO DO NEXT USING AN OUTSIDE SYSTEM
'THIS IS A SIMPLE MESSAGE BOX

MsgBox ("Your Column X NIINs have been copied," & vbCrLf & "Please open EMALL and paste into ORDERS > DLA ORDERS," & vbCrLf & "Select NSN/NIIN UPLOAD CAPABILITY, paste & click Submit," & vbCrLf & "Click Export to Excel and open exported file" & vbCrLf & "then copy/paste from export file to your forecasting file" & vbCrLf & "Col B to AB" & vbCrLf & "Col E to AC and" & vbCrLf & "Col N to AD.")


'-------------------------------------------------------------

    End If
End Sub
 
I just tested the code on a worksheet where COL "O" does NOT have any blanks and confirmed the problem lies in this small piece of code:

Code: 
Sub MOD_46_MBlank()

    Dim cnt As Long
    cnt = WorksheetFunction.CountBlank(Range("O1", Range("O" & Rows.Count).End(xlUp)))
    If cnt = 0 Then

This small test sheet purposely only holds data in rows 5 through 17.
The code should look through column O down to row 17 OR (locate the last row of data (row17) and work upward to stop at row 5)

I think it is seeing all the "blanks" in rows 18 down to bottom of sheet and THINKS that it has blanks -- therefore when VALIDATING, it is taking the wrong path and going with PROCESS B rather than the correct PROCESS A.

How do we adjust this code to make it only look through that specific range where data exists?
You can use COLUMN A as a point of refc if that helps? (in other words if COL A has data then continue down COL "O" looking for blanks until no more data exists in COL A)
 
CC

The file attached is what I will call a perfect test environment. This file is proof that the small piece of code as you put it flies truely. I think with this file safely producing the correct result as it always would have that the problem is elsewhere in your code. You have not taken the time to post a workbook. The code above means little without one so the test environment wins the day.

Smallman
 

Attachments

  • 1Blanks.xlsm
    13.5 KB · Views: 11
No need to get testy -- ;) Here's a scaled down file showing the format.
Instead of using the personal workbook this time, I've copied the module into this workbook and used simple "Messagebox" lines to determine which path it elects to take... Sure enough, the result is positive! There's 1 tab WITH blanks and a tab WITHOUT to run the testing.

Now--to figure out why the heck it would not take the correct path with the REAL code (shown above)... SOMETHING must be conflicting with Smallman and Hui's updated code - Does anyone see what might be causing the fail issue with the real code shown in the 2 large windows above? The code is ignoring Process A and always jumping to Process B. It should RUN Process A code when the sheet has NO BLANKS.
 

Attachments

  • Test_with-both-sheet-variations.xlsm
    24.8 KB · Views: 6
Last edited:
Found the issue!!
As you can see in my attached sample, rows 1 through 4 are part of a header area and should be ignored.
I corrected the (Range("O1", Range.... of the original code to start at the proper place in my sample "O5" and now it works like a charm! Thanks for your help guys!

Code: 
cnt = WorksheetFunction.CountBlank(Range("O5", Range("O" & Rows.Count).End(xlUp)))
 
Glad you got there in the end. There is this vagary called the International time line which gets me every day. Right now it is 5.44 am in my part of my country. Pleased you sorted it.

Smallman
 
You must log in or register to reply here.
Back
Top