GoTo Function in VBA checking values

[blimey88]

Can anyone help with the following, I am trying to miss the email module when checking through my code,

I want the to skip this part of the code:

Call EmailOutOfDate (Email, ExpDate, Documents, Days Remaining) and check a value in the cell which determines whether or not an email should be sent. I need to completely skip this but return to it if the email is required to be sent.

I hope this makes sense if it doesn't but you think you can help please let me know.

 

[Fluff]

Maybe something like

If Range("X3").Value = "yes" Then
   Call EmailOutOfDate (Email, ExpDate, Documents, Days Remaining)
End If

 

[blimey88]

Many thanks for this, will this allow me to skip a certain part of the code but return to it if the answer is 'No'? So If Range("X3").Value = "no" it will then revery back to the next part of the code and continue to run?

 

[Fluff]

If the cell value is no it will just skip the Call routine & continue with the rest of the code, unless it's inside a loop.

 

[blimey88]

OK, the userform is running through the worksheet and looking for any dates that are within a certain range for example 60 days, 30 days, 15 days and 0 days. When an email is sent at 60 days i need the empty cell next to that date marked as 60, after which i need the next email to be sent at 30 days and marked accordingly. What I need is to miss the 60 day marker so another email isn't generated? Does this make sense? I apologise if not. I don't want the same email sent over and over!

 

[Fluff]

Yes that makes perfect sense, but you have only supplied one line of your code so it's nigh on impossible to help.
Can you please supply the rest of the code?

 

[blimey88]

I understand, please see below:

Sub OutOfDateSubs()
 
Dim LastRow As Integer
Dim FirstAddress As String
Dim FirstColumn As Integer
Dim FindLastAddress As String
Dim FindLastColumn As Integer
Dim LastColumn As Integer
Dim NumberColumns As Integer
Dim ActiveRow As Integer
Dim Documents As String
Dim ExpDate As Date
Dim Email As String
Dim EmailColumn As Long
Dim CurrentCell As String
Dim CurrentDate As Date
Dim DaysRemaining As Long
 
 
ThisWorkbook.Sheets("Subcontractors").Select
 
LastRow = Range("A" & Rows.Count).End(xlUp).Row '(So we know where to stop)
'MsgBox LastRow
 
FirstAddress = Cells.Find(What:="Accreditations").Address 'Find The First Address Of The "Accreditations" Cell
ActiveSheet.Range(FirstAddress).Select
FirstColumn = ActiveCell.Column 'Find The First Address Of The "Accreditations" Cell
ActiveSheet.Range(FirstAddress).Select 'Select First Services Column to get the Column Number
'MsgBox " First Column Of Accreditations " & FirstColumn
 
FindLastAddress = Cells.Find(What:="Insurance").Address 'Find The First Address Of The "Insurance" Cell
ActiveSheet.Range(FindLastAddress).Select
FindLastColumn = ActiveCell.Column
ActiveSheet.Range(FindLastAddress).Select
ActiveCell.Offset(, 1).Select 'Offset to end of merged row
ActiveCell.Offset(2, -1).Select 'Offset Down 2 Rows and left 1 Column
LastColumn = ActiveCell.Column
'MsgBox "Last Column Of Insurance " & LastColumn
 
NumberColumns = LastColumn - FirstColumn + 1
 
ActiveSheet.Range("F4").Select
ActiveRow = ActiveSheet.Range("F4").Row
'MsgBox ActiveRow
 
For i = 1 To NumberColumns
 
    If ActiveCell = "n/a" Or ActiveCell = "In Progress" Or ActiveCell = "On Request" Then
        'Do Nothing
    Else
        CurrentCell = ActiveCell.Address
        CurrentDate = ActiveCell.Value
        DaysRemaining = DateDiff("D", Now(), CurrentDate)
        'MsgBox DaysRemaining
 
        If DaysRemaining <= Worksheets("Email_Time_Frames").Range("B3") Then
            'MsgBox "In 60 Days " & ActiveCell.Address
            
   
            Email = Cells(ActiveCell.Row, 3).Value
            'MsgBox Email
            ExpDate = ActiveCell.Value
            'MsgBox ExpDate
            Documents = Cells(3, ActiveCell.Column).Value
            'MsgBox Documents
 
 
 
 
***** Offset 1 column, Check last sent ********
***** If with GoTo *****
 
 
 
 
 
 
            Call EmailOutOfDate(Email, ExpDate, Documents, DaysRemaining)
            
            ActiveCell.Offset(0, 1).Select:
           
            If ActiveCell = "0" Then
                'Do Noting
            Else
                Select Case DaysRemaining 'Mark Next Time Frame Email Needs To Be Sent
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("E3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("F3").Value 'Reset
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("D3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("E3").Value 'Cant Schedule
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("C3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("D3").Value '3rd Email
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("B3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("C3").Value '2nd Email
                End Select
            End If
            Cells.Range(CurrentCell).Select
        End If
       
    End If
   
    ActiveCell.Offset(, 2).Select
   
    If ActiveCell.Column = LastColumn + 1 Then 'Move to Next Row
        Cells((ActiveRow + 1), FirstColumn).Select
        ActiveRow = ActiveRow + 1
        'MsgBox ActiveRow
        i = 0 '0=Normal,1=Test 1 row only
    End If
   
    If ActiveRow = LastRow + 1 Then
        Exit Sub
    End If
   
Next i
 
Unload Admin1
 
End Sub

 

[Fluff]

Which cell has the date & which cell contains the 60,30 etc

 

[blimey88]

The 60 and 30 ect will be in G4, from there I will be offsetting by 2 to then fill these with the subsequent 60, 30 ect

[TABLE="width: 1144"]
<colgroup><col span="2"><col span="2"><col span="2"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]22/06/2023[/TD]
[TD]60[/TD]
[TD]09/05/2021[/TD]
[TD]60[/TD]
[TD]17/05/2022[/TD]
[TD]60[/TD]
[TD]21/03/2019[/TD]
[TD]60[/TD]
[TD]21/03/2019[/TD]
[TD]60[/TD]
[TD]21/03/2020[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

In that case what is this part of the code doing

                Select Case DaysRemaining 'Mark Next Time Frame Email Needs To Be Sent
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("E3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("F3").Value 'Reset
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("D3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("E3").Value 'Cant Schedule
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("C3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("D3").Value '3rd Email
                    Case Is <= ThisWorkbook.Sheets("Email_Time_Frames").Range("B3").Value
                        ActiveCell.Value = ThisWorkbook.Sheets("Email_Time_Frames").Range("C3").Value '2nd Email
                End Select

I thought that was checking if an email had been sent.