Improvement on code

[fvdk]

Hi I just posted a similair post about code improvement but with this piece of code I face the similair problem.

The form is for hour administartion). The code basicly checks (before sending the form) if in Range AF19 any hours are put (Value > 0) where the teamnumber is not filled (default value of cell stays on "team" and is not changed to "team1" for instance). Then the cell of team (I19) is selected and marked as red.

So far so good =) However I want to do this same action for AF19 untill AF46. Is there a nice way (working with ranges maybe) to put that in few lines of code. Or do I have to do the below piece of code for every AF Field (So 27 times).

Sub SaveSend()
Dim Response As Boolean
If Range("AF19").Value > 0 And Range("I19").Value = "team" Then
    Range("I19").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Response = MsgBox("You have not entred the fieldnumber")
Cancel = True
Else
 
Rest of the code

 

[wigi]

Maybe:

Sub SaveSend()
    Dim r As Range
    Dim blnErrors As Boolean
    blnErrors = False
    For Each r In Range("AF19:AF46")
        If r.Value > 0 And Range("I" & r.Row).Value = "team" Then
            Range("I" & r.Row).Interior.ColorIndex = 3
            blnErrors = True
        End If
    Next
    If blnErrors Then
        Response = MsgBox("You have not entred the fieldnumber")
        Cancel = True
    End If
Else

 

[fvdk]

Thanks I think this works. However I have some problems with running the rest of my code. Because after Else it is support to save the workbook and the add it to a new email.

I get this error from the debugger (Else without If).

If I remove the 'Else' the file will be saved (which I don't want, they should not be allowed to save the file untill everything is filled in properly).

I guess there should be an 'If' added somewhere without 'End if' and there Else would come to place. Can't figure out how it work though. Would appreciate it if you can help.

Else

ThisWorkbook.SaveAs Filename:=GetDesktopPath & (rest of filename)

Maybe:

Sub SaveSend()
    Dim r As Range
    Dim blnErrors As Boolean
    blnErrors = False
    For Each r In Range("AF19:AF46")
        If r.Value > 0 And Range("I" & r.Row).Value = "team" Then
            Range("I" & r.Row).Interior.ColorIndex = 3
            blnErrors = True
        End If
    Next
    If blnErrors Then
        Response = MsgBox("You have not entred the fieldnumber")
        Cancel = True
    End If
Else

 

[wigi]

I just started from your code, so you will have to see where my work fits into your previous code.

 

[fvdk]

Ok thanks, I figured it out with a bit of tweaking.

Thanks for your answer, really helps to get the code clean and improve maintaince on this code

I just started from your code, so you will have to see where my work fits into your previous code.