• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

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
0
mike637
Asked:
mike637
  • 6
  • 5
  • 2
  • +3
2 Solutions
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
byundtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now