How to goto next iternation in for?

[SathyaM]

Hi Experts,

I am trying to write a code which should work if any one of the cell in Range(AL10 to AL37) is yes and if blank or no it should go to next iternation. Something i am missing here, its displays messge for all iternation(even for balnk values).


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim r As Range
Set r = Worksheets("Resource Addition").Range("AL10:AL37")
For Each cell In r
If Len(cell) <> 0 And cell.Value = "Yes" Then
Call abc
End If
Next
End Sub
Sub abc()
IsEmpty (Cells(ActiveCell.Row, ActiveCell.Column + 1).Value)
MsgBox "Time"
End Sub

only if range value its true it should call the funcation but I am getting the message for blank values of cell.

Please help to resolve.

Thanks for the help.

Regards
Sathya

 

[Wessie]

Hi Sathya

Try
If Cell.Value = "Yes" Then
Instead of the Len as well.

Post back if it doesn't help.

 

[SathyaM]

No. let me tell complete requst. I want to set AM10 and U10 as mandantory field to fill if AL10 is Yes. It should work for row 10 to 37.

 

[Wessie]

I still don't see a reason for the Len then. You want to determine that it's not empty and that it says "Yes". So the len in your formula serves no purpose as it cannot be the one without the other.

Show me the full picture of what you want to achieve as your end goal is not complex to achieve at all.

Also try Cell.Formula instead of value.

 

[dmt32]

Not fully clear what you are trying to do but your procedure ABC uses Activecell and as your calling procedure does not increment each cell on your worksheet (selects each cell in range), the procedure will always use the same cell value.</SPAN>

For Procedure ABC to work correctly, you need to pass the range to it using an argument of same data type.</SPAN>

To give you some idea & as an example only, I have amended your code to show how you can do this – procedure Test passes both the worksheet object & required range to ABC procedure – you will need to adapt to meet your specific project need.</SPAN>

Hope Helpful</SPAN>

Dave</SPAN>

</SPAN>
Sub test()</SPAN>
    Dim r As Range</SPAN>
    Dim cell As Range</SPAN>
    Dim ws As Worksheet</SPAN>
    Set ws = Worksheets("Resource Addition")</SPAN>
    Set r = ws.Range("AL10:AL37")</SPAN>
    For Each cell In r</SPAN>
        If cell.Value = "Yes" Then abc ws, cell</SPAN>
    Next</SPAN>
End Sub</SPAN>
 
Sub abc(ByVal sh As Object, ByVal rng As Range)</SPAN>
    IsEmpty (sh.Cells(rng.Row, rng.Column + 1).Value)</SPAN>
    MsgBox sh.Name & Chr(10) & rng.Row</SPAN>
End Sub</SPAN>

</SPAN>

 

[Norie]

What is the purpose of this line of code?

 IsEmpty (Cells(ActiveCell.Row, ActiveCell.Column + 1).Value)

As far as I can see it does nothing.

 

[dmt32]

What is the purpose of this line of code?

 IsEmpty (Cells(ActiveCell.Row, ActiveCell.Column + 1).Value)

As far as I can see it does nothing.

I guessed its just an example procedure which OP is in process of developing - what that line is meant to do was not I thought, the issue - but could be wrong.

Dave

 

[SathyaM]

I guessed its just an example procedure which OP is in process of developing - what that line is meant to do was not I thought, the issue - but could be wrong.

Dave

I am not sure how to give the pic of my form. I am trying to explaining my need. If my code and way of approach is wrong, please help me with fresh code.


My form have the values to fill in A10 to AM37.

user can choose any row to fill the value between 10 to 37. When user attempting to save the form it should check the column AL(row 10 to 37). That column is drop down of Yes or No. If user says "Yes" for AL then user should fill the value in U and AM column. If those fields are blank user should get message to fill in the those values and save should cancellled. the problem is user has the right to choose the any row to fill in between 10 to 37. So code should check all the row of AL 10 to 37.

Thanks for the all replied.

 

[Wessie]

Here you go:

Sub test()
    Dim a As Integer
    Dim ws As Worksheet
    Dim Filled As Boolean
    Dim Counter As Integer
    Dim Prov As Boolean
    
    Filled = True
    Prov = False
    Set ws = Worksheets("Resource Addition")
    
    For a = 10 To 37
        If ws.Cells(a, 38).Formula = "Yes" Then
            Prov = True
            If Cells(a, 21) = "" Or Cells(a, 39) = "" Then
                MsgBox ws.Name & Chr(10) & "AL" & a & "'s mandatory fields need to be completed to continue."
                Filled = False
                Counter = Counter + 1
            End If
        End If
    Next
    
    If Counter = 0 And Prov = False Then
        MsgBox "No answers in AL10 to AL37 provided"
    End If
    
    If Counter <> 0 And Prov Then
        MsgBox Counter & " mandatory rows' fields have not been completed"
    End If
    
    If Filled And Prov Then
        MsgBox "All mandatory fields have been filled"
        'Continue with the rest of your coding as if all mandatory fields were populated.
    End If
End Sub

As basic as I could.
This should do exactly what you want it to.

Let me know.
Kind regards
R

 

[Norie]

Perhaps.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cl As Range
Dim arr()
Dim I As Long

    With Worksheets("Resource Addition")
        For Each cl In .Range("AL10:AL37")
            If cl.Value = "Yes" Then
                If .Cells(cl.Row, "U") = "" Or .Cells(cl.Row, "AM") Then
                    ReDim Preserve arr(I)
                    arr(I) = "Row " & cl.Row
                    I = I + 1
                End If
            End If
        Next cl
    End With
    
    If I > 0 Then
        Cancel = True
        MsgBox "Incomplete entries on:" & vbCrLf & vbCrLf & Space(10) & Join(arr, vbCrLf & Space(10)), vbExclamation
    End If
    
End Sub