vba loop


Hello Experts,

Listed below is my code - It is working fine, but it is not running this line of code:
If Not Intersect(target, Range("C21:G21, C23:G23, C25:G25, C27:G27")) Is Nothing......
when a new value is entered that is < 50 in the (ByVal taget As Range)

It is not checking if the target is "0" and then hide row accordingly. Could it be skipping it since the target is "".

How can I modify my code to catch this and run correctly????
 
Private Sub Worksheet_Change(ByVal target As Range)

    If Not Intersect(target, Range("C20:G20, C22:G22, C24:G24, C26:G26")) Is Nothing Then
   
    On Error Resume Next
    Application.EnableEvents = False
    target.Offset(1, 0).ClearContents
         If target > 50 Then
               target.Offset(1, 0).Rows.Hidden = False
               target.Offset(1, 0).Cells.Locked = True
               target.Offset(1, 0) = target - 50
               target = 50
         End If
    End If
   
    If Not Intersect(target, Range("C21:G21, C23:G23, C25:G25, C27:G27")) Is Nothing Then
   
         If target = 0 Then
               target.Rows.Hidden = True
               target.Cells.Locked = True
         End If
    End If
       
    Application.EnableEvents = True
   
    Call SelectCell    'this code tells it to go to the next cell that is empty & not locked)

End Sub
mike637Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Change:

         If target = 0 Then

to:

         If target = 0 Or If Target = "" Then
0
jppintoCommented:
If (target = 0) Or (Target = "") Then
0
mike637Author Commented:
Hi mattewspatrick

I changed it to that - but the code line is staying "red" and I get a compile error - Syntax error

mike637
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

CluskittCommented:
Be sure you're using 2 " and not 4 '
0
mike637Author Commented:


I tried this line - which does not create an error

If (target = 0) Or (Target = "") Then

But it is still not hiding the row.

Very confused
0
mike637Author Commented:


Yes I am using 2 ' and not 4'

But it is firing still.

mike637
0
Patrick MatthewsCommented:
If the value is one or more spaces, then you would need:

         If target = 0 Or If Trim(Target) = "" Then

Also, assuming this is from a Worksheet_Change event sub, if more than one cell changed, then Target will represent all of those cells.  You may have to iterate through the cells, but you have not posted enough of your code or of the ultimate prupose to make suggestions on how to allow for that.
0
mike637Author Commented:
Sorry - meant to say

Yes I am using 2 ' and not 4'

But it is NOT firing still.

mike637
0
mike637Author Commented:
Hello matthewspatrick

It is a Private Sub Worksheet_Change(ByVal target As Range)

I have posted all my code.

How do I get it to iterate through the cells in the range?
0
Patrick MatthewsCommented:
Yes, you did post the whole sub.  Sorry for that oversight :)




Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cel As Range

    If Not Intersect(target, Range("C20:G20, C22:G22, C24:G24, C26:G26")) Is Nothing Then
    Set rng = Intersect(target, Range("C20:G20, C22:G22, C24:G24, C26:G26"))
    For Each cel In rng.Cells
        On Error Resume Next
        Application.EnableEvents = False
        cel.Offset(1, 0).ClearContents
             If cel > 50 Then
                   cel.Offset(1, 0).Locked = False
                   cel.Offset(1, 0).EntireRow.Hidden = False
                   cel.Offset(1, 0) = cel - 50
                   cel = 50
                   cel.Offset(1, 0).Locked = True
             End If
        Next
        On Error GoTo 0
    End If

    If Not Intersect(target, Range("C21:G21, C23:G23, C25:G25, C27:G27")) Is Nothing Then
    Set rng = Intersect(target, Range("C21:G21, C23:G23, C25:G25, C27:G27"))
    For Each cel In rng.Cells
         If cel = 0 Or Trim(cel) = ""Then
               cel.Locked = False
               cel.EntireRow.Hidden = True
               cel.Locked = True
         End If
    Next
    End If
        
    Application.EnableEvents = True
    
    Call SelectCell    'this code tells it to go to the next cell that is empty & not locked)

End Sub 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
Is recursion desired in this sub? For example, if the user makes a change such that C20 is 55 then the macro subtracts 50 from the value in C21. If C21 now equals 0, then do you want row 21 to be hidden? If so, then comment out line 9 in Patrick's latest sub (it is turning off Application.EnableEvents and preventing recursion).

Also, if C21 should have a value less than 0 after the subtraction, do you want to hide row 21? If so, change statement 25 to:
         If cel <= 0 Or Trim(cel) = "" Then


Brad
0
rberkeConsultantCommented:
It is hard to tell exactly what he wants selectcell to do.

The attached excel book seems to do what he wants.
to test, it type 0 in c20, then c21, then c22 etc.

My theory is that he coded SelectCell incorrectly.  The correct code is
Sub selectcell()
Do Until Len(Selection) = 0
    Selection.Offset(1, 0).Select
Loop
End Sub

But, if he accidentally coded it like I originally did, he would get a symptom similar to his description.

Sub selectcellLikeIOriginallyCodedIt()
Do
Selection.Offset(1, 0).Select
Loop Until Len(Selection) = 0
End Sub

The trick is that the event is not called until AFTER excel advances the selection to the next cell.

deletenow.xls
0
Patrick MatthewsCommented:
Nice seeing you around, Richard :)
0
rberkeConsultantCommented:
I'm sure it would be nice seen richard.  But this is bob.

I just had a few minutes to kill for the first time in about a half year.
0
Patrick MatthewsCommented:
Apologies :)  Nice seeing you back even so!
0
mike637Author Commented:
Thank you to all - for your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.