Still having Issues with checking certain Rows to be Empty ...

[mloucel]

Good Afternoon Gurus..
I posted this yesterday and was kindly helped by RCBricker (many thanks)


But my problem is somewhat solved thanks to RCBricker, so far I have spent already more than 5 hours trying different things from here and there and I can't find a solution, so I have to ask again for help.


I need to check ONLY certain columns within a row to be empty and display a message to the End user so he can correct the problem


- The EndUser (EU) can be anywhere from row 2 to row 1500.
- When the user enter his/her name in Rn (next available row number in R):
- I need to check colums F thru P, IF ANY of them has nothing then display a message
- some columns as F have a date format (mm/dd/yyyy)
- Phone in K
- and general text the rest
- A thru E are reserved and will never be checked or any data on them.


this is the code I have so far, it works somewhat but not the way I want, partially corrected by RCBricker

Private Sub Worksheet_Change(ByVal Target As Range)
Set TestInRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500, I2:I1500, I2:I1500, J2:J1500, K2:K1500, L2:L1500, M2:M1500, N2:N1500, O2:O1500, P2:P1500"))
Dim rng As Range
Dim lngF As Long, lngCOL As Long
    
If Len(Cells(Target.Row, "R")) <> 0 Then  ' IF the user is in R is R empty or does it have Data ?
    Set rng = Range(Cells(2, 6), Cells(1500, 16))   ' Range F2:P1500
    [COLOR=#ff0000][B]lngCOL = rng.Find("").Column[/B][/COLOR] ' This line is finding Empty cells even if they have data.. 
    If lngCOL > 0 Then          ' IF R has Data then, is there any cell in my TestInRange that is empty ?
   
            MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
            & vbCrLf & "YOU MUST Fill these cells:" _
            & vbCrLf & "" _
            & vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
            & vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
            & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
            & vbCrLf & " " _
            & vbCrLf & "Please go back and re-enter the values...", _
            vbCritical, "Validation NOT PASSED..."
   
        ' Forcing the User to go to DATE CELL (Fn) within the ROW we are at this moment
         ActiveCell.Offset(-1, -12).Select  ' Also Generating an error if we are at row 2 but I
    End If
End If
End Sub

PROBLEMS:
- I fill ALL the colums from F to P and enter a name in R and the message pops up
- My code now checks everytime I re-enter something between F and P, like if something is empty when I trully have data all across the cells.
- no matter what even if ALL have data (F thru P) the message pops up every time I enter new data on those colums, so if the EU is entering new data over let's say in F200 after hitting enter I got the message, enter more data in G200 the message comes again, go to fill H and so on.. ANOYING..


I cannot spend more time in this, I am trully at lost and I've done all I can with my little knowledge I have..


Please Gurus can you lend a hand..

 

[MARK858]

If your cells are truly empty and not the result of formula empty strings then try the below (at least until one of the Guru's posts something tidier )

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, TestInRange As Range
 
 If Not Application.Intersect(Target, Range("R:R")) Is Nothing Or Target.Cells.Count > 1 Then
    
    Set rng = Application.Union(Range("F2:F1500"), Range("G2:G1500"), Range("H2:H1500"), Range("I2:I1500"), Range("I2:I1500"), _
    Range("J2:J1500"), Range("K2:K1500"), Range("L2:L1500"), Range("M2:M1500"), Range("N2:N1500"), Range("O2:O1500"), Range("P2:P1500"))
        Set TestInRange = Intersect(Rows(Target.Row), rng)
        

        If Application.WorksheetFunction.CountA(TestInRange) < 11 Then
            ' IF the user is in R is R empty or does it have Data ?
            '    Set rng = Range(Cells(2, 6), Cells(1500, 16))   ' Range F2:P1500
            '    lngCOL = rng.Find("").Column ' This line is finding Empty cells even if they have data..
            '    If lngCOL > 0 Then          ' IF R has Data then, is there any cell in my TestInRange that is empty ?
            '
            MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
                   & vbCrLf & "YOU MUST Fill these cells:" _
                   & vbCrLf & "" _
                   & vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
                   & vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
                   & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
                   & vbCrLf & " " _
                   & vbCrLf & "Please go back and re-enter the values...", _
                   vbCritical, "Validation NOT PASSED..."

            ' Forcing the User to go to DATE CELL (Fn) within the ROW we are at this moment
            If Target.Row > 2 Then Target.Offset(-1, -12).Select    ' Also Generating an error if we are at row 2 but I
        End If
    End If
End Sub

 

[mloucel]

Dear MARK858

Thanks for the not so small help.. that was HUGE.. I tested your correction in my test WS and I had to make 1 correction

If Target.Row > 2 Then Target.Offset(-1, -12).Select
instead I corrected the code to:
If Target.Row > 2 Then ActiveCell.Offset(-1, -12).Select

Was moving me off the active cell range..

Now for the rest of the code I am so freaking new that some like this:

Set rng = Application.Union(Range("F2:F1500"), Range("G2:G1500"), Range("H2:H1500"), etc..
or this: If Application.WorksheetFunction.CountA(TestInRange) < 11 Then
or this: Application.Intersect(Target, Range("R:R"))

the latest one is mind bug for me.. I've never seen target used as R:R didn't even know you can use that... Awesome..

I am at AWE... First time in my life I've seen such.. you really got me big time I have no knowledge whatsoever I will have to google it and find out what it is and how it works..

Does it work.. HECK YEAH.. I've tested it so far with all cells empty and filling 1 by one and it works the way it is supposed to work..
Filled all the cells and writing the name and it works..
Delete 1 cell and re-test and it works..

Your code is encouraging me to try to read faster and work more in coding, but as you can imagine the pressure of the Boss saying "is it done" not exactly the best ambient for learning...
Many guys here have helped me thru the journey and I have learned a lot in a few weeks, I'm not so rookie anymore but this coding is way different to what I used to do in the time were
cobol and Foxpro were the kings.. (yep i'm that old..), the rules of coding are the same but the power of the code has changed so much that I feel overwhelm with all the new stuff..

Many thanks for taking the time to Excel and give a hand to people like me (newbies from the old days), I am very grateful.

Maurice.