Hey Guys,
I have been building this section of code for about a month. I have many revs of this code, however, while it SEEMS this code works in other versions. I cannot get it to stop breaking on the same line.
I know it is a fairly large program. But the real problem is where I highlighted in red. Im not sure if there is a syntax problem somewhere, but what I can say is that it definitely doesnt seem like any variables have empty or undelcared values. So I am quite confused.
Please let me know if you can help, and as always many thanks to the devoted community. Every step of this project that I have had problems with you guys have helped me
I have been building this section of code for about a month. I have many revs of this code, however, while it SEEMS this code works in other versions. I cannot get it to stop breaking on the same line.
Code:
Sub alertGen()
Dim lync As CommunicatorAPI.IMessengerConversationWndAdvanced
Dim toUser As String, message As String, fiscalDayNum As String, dayCheckNoDouble As String
Dim bizRhyth As Worksheet, dialSh As Worksheet
Dim rowA As Range, dayPlan As Range, dayCheck As Range, offsetCol As Range, asOf As Range, dueDay As Range
Dim CAMX As Variant, finPMX As Variant, planningX As Variant, subX As Variant, finOX As Variant 'used as contact groups
Dim offsetOfToday As Integer, offsetOfDue As Integer
'//////////////////////////////////////////////////////////////////////////////////////////////
'Dim CAMX As String, finPMX As String, planningX As String, subX As String, finOX As String 'Used to hold place for contacts NOT NEEDED?????
Dim placeHolder As Integer, contactCounter As Integer
Dim CxCount As Integer, FPMxCount As Integer, PxCount As Integer, SxCount As Integer, FOxCount As Integer 'Used to make sure that each recipient gets a message and that the program doesnt loop the message to a user for a second time
'//////////////////////////////////////////////////////////////////////////////////////////////
Set bizRhyth = Sheets("Business Rhythm") 'declaring a sheet as a variable for easy reference
Set dialSh = Sheets("Master Dial") 'easy reference
Set datesRhy = Sheets("DatesForRhythm") 'easy reference
CxCount = Sheets("Distribution").Range("E5")
FPMxCount = Sheets("Distribution").Range("H5")
PxCount = Sheets("Distribution").Range("K5")
SxCount = Sheets("Distribution").Range("N5")
FOxCount = Sheets("Distribution").Range("Q5")
contactCounter = 6
fiscalDayNum = dialSh.Range("N2") 'brings back fiscal day
Set rowA = bizRhyth.Range("A1:A999") 'range at which to find the reminders for a specfic day
Set offsetCol = datesRhy.Range("A1:A50")
Dim X As Long, Letters() As String
Dim firstRun As Integer
firstRun = 0
Dim camWelcomeMessage As Integer
camWelcomeMessage = 0
Dim FinPMWelcomeMessage As Integer
FinPMWelcomeMessage = 0
Dim planningWelcomeMessage As Integer
planningWelcomeMessage = 0
Dim subsWelcomeMessage As Integer
subsWelcomeMessage = 0
Dim analystOnlyWelcomeMessage As Integer
analystOnlyWelcomeMessage = 0
'//////////////////////////////////////////////////////////////////////////////////////////////
Do
If firstRun = 0 Then
Set dayPlan = rowA.Find(What:=fiscalDayNum, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
firstRun = 1
Set dayCheck = dayPlan
Else
Set dayPlan = rowA.Find(What:=fiscalDayNum, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False, After:=dayPlan)
If Not dayPlan Is Nothing Then
If dayCheck.Address = dayPlan.Address Then
Exit Do
End If
Else
Exit Do
End If
End If
[COLOR=#ff0000][I][U] offsetOfToday = dayPlan.Offset(0, 3)[/U][/I][/COLOR]
offsetOfDue = dayPlan.Offset(0, 4)
Set asOf = offsetCol.Find(What:=offsetOfToday, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1)
If asOf.Offset(0, 2) = "Sunday" Then 'Used so the weekend is skip
Set asOf = asOf.Offset(-2, 0)
ElseIf asOf.Offset(0, 2) = "Saturday" Then
Set asOf = asOf.Offset(-1, 0)
End If
Set dueDay = offsetCol.Find(What:=offsetOfDue, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1)
If dueDay.Offset(0, 2) = "Sunday" Then 'Used so the weekend is skip
Set dueDay = dueDay.Offset(1, 0)
ElseIf dueDay.Offset(0, 2) = "Saturday" Then
Set dueDay = dueDay.Offset(2, 0)
End If
Letters = Split(Replace(dayPlan.Offset(0, 1).Value, " ", ""), ",")
For X = 0 To UBound(Letters)
Select Case Letters(X)
Case "C"
placeHolder = CxCount
Do While placeHolder <> 0
placeHolder = placeHolder - 1
Set CAMX = Sheets("Distribution").Range("D" + contactCounter)
contactCounter = countactCounter + 1
Application.ScreenUpdating = False
If camWelcomeMessage = 0 Then
message = "CAMs, the following is a reminder for the upcoming business rhythm:"
Set lync = Messenger.InstantMessage(CAMX)
lync.SendText (message)
End If
message = dayPlan.Offset(0, 2)
Set lync = Messenger.InstantMessage(CAMX)
lync.SendText (message)
If dayPlan.Offset(0, 3) = 0 Then
message = "As of: Today, " & asOf
ElseIf dayPlan.Offset(0, 3) = -1 Then
message = "As of: Yesterday, " & asOf
Else
message = "As of: " & asOf
End If
lync.SendText (message)
If dayPlan.Offset(0, 4) = 1 Then
message = "Due: Close of business, " & dueDay
ElseIf dayPlan.Offset(0, 4) = 2 Then
message = "Due: Tomorrow, " & dueDay
Else
message = "Due: " & dueDay
End If
lync.SendText (message)
On Error Resume Next
lync.Close
Application.ScreenUpdating = True
Loop
camWelcomeMessage = 1
contactCounter = 6
Case "F"
placeHolder = FPMxCount
Do While placeHolder <> 0
placeHolder = placeHolder - 1
Set finPMX = Sheets("Distribution").Range("G" + contactCounter)
contactCounter = countactCounter + 1
Application.ScreenUpdating = False
If FinPMWelcomeMessage = 0 Then
message = "Finance/PMs, the following is a reminder for the upcoming business rhythm:"
Set lync = Messenger.InstantMessage(finPMX)
lync.SendText (message)
End If
message = dayPlan.Offset(0, 2)
Set lync = Messenger.InstantMessage(finPMX)
lync.SendText (message)
If dayPlan.Offset(0, 3) = 0 Then
message = "As of: Today, " & asOf
ElseIf dayPlan.Offset(0, 3) = -1 Then
message = "As of: Yesterday, " & asOf
Else
message = "As of: " & asOf
End If
lync.SendText (message)
If dayPlan.Offset(0, 4) = 1 Then
message = "Due: Close of business, " & dueDay
ElseIf dayPlan.Offset(0, 4) = 2 Then
message = "Due: Tomorrow, " & dueDay
Else
message = "Due: " & dueDay
End If
lync.SendText (message)
On Error Resume Next
lync.Close
Application.ScreenUpdating = True
Loop
FinPMWelcomeMessage = 1
contactCounter = 6
Case "P"
placeHolder = PxCount
Do While placeHolder <> 0
placeHolder = placeHolder - 1
Set planningX = Sheets("Distribution").Range("J" + contactCounter)
contactCounter = countactCounter + 1
Application.ScreenUpdating = False
If planningWelcomeMessage = 0 Then
message = "Planning, the following is a reminder for the upcoming business rhythm:"
Set lync = Messenger.InstantMessage(planningX)
lync.SendText (message)
End If
message = dayPlan.Offset(0, 2)
Set lync = Messenger.InstantMessage(planningX)
lync.SendText (message)
If dayPlan.Offset(0, 3) = 0 Then
message = "As of: Today, " & asOf
ElseIf dayPlan.Offset(0, 3) = -1 Then
message = "As of: Yesterday, " & asOf
Else
message = "As of: " & asOf
End If
lync.SendText (message)
If dayPlan.Offset(0, 4) = 1 Then
message = "Due: Close of business, " & dueDay
ElseIf dayPlan.Offset(0, 4) = 2 Then
message = "Due: Tomorrow, " & dueDay
Else
message = "Due: " & dueDay
End If
lync.SendText (message)
On Error Resume Next
lync.Close
Application.ScreenUpdating = True
Loop
planningWelcomeMessage = 1
contactCounter = 6
Case "S"
placeHolder = SxCount
Do While placeHolder <> 0
placeHolder = placeHolder - 1
Set subX = Sheets("Distribution").Range("M" + contactCounter)
contactCounter = countactCounter + 1
Application.ScreenUpdating = False
If subsWelcomeMessage = 0 Then
message = "Subcontractors, the following is a reminder for the upcoming business rhythm:"
Set lync = Messenger.InstantMessage(subX)
lync.SendText (message)
End If
message = dayPlan.Offset(0, 2)
Set lync = Messenger.InstantMessage(subX)
lync.SendText (message)
If dayPlan.Offset(0, 3) = 0 Then
message = "As of: Today, " & asOf
ElseIf dayPlan.Offset(0, 3) = -1 Then
message = "As of: Yesterday, " & asOf
Else
message = "As of: " & asOf
End If
lync.SendText (message)
If dayPlan.Offset(0, 4) = 1 Then
message = "Due: Close of business, " & dueDay
ElseIf dayPlan.Offset(0, 4) = 2 Then
message = "Due: Tomorrow, " & dueDay
Else
message = "Due: " & dueDay
End If
lync.SendText (message)
On Error Resume Next
lync.Close
Application.ScreenUpdating = True
Loop
subsWelcomeMessage = 1
contactCounter = 6
Case "O"
placeHolder = FOxCount
Do While placeHolder <> 0
placeHolder = placeHolder - 1
Set finOX = Sheets("Distribution").Range("P" + contactCounter)
contactCounter = countactCounter + 1
Application.ScreenUpdating = False
If analystOnlyWelcomeMessage = 0 Then
message = "Finance, the following is a reminder for the upcoming business rhythm:"
Set lync = Messenger.InstantMessage(finOX)
lync.SendText (message)
End If
message = dayPlan.Offset(0, 2)
Set lync = Messenger.InstantMessage(finOX)
lync.SendText (message)
If dayPlan.Offset(0, 3) = 0 Then
message = "As of: Today, " & asOf
ElseIf dayPlan.Offset(0, 3) = -1 Then
message = "As of: Yesterday, " & asOf
Else
message = "As of: " & asOf
End If
lync.SendText (message)
If dayPlan.Offset(0, 4) = 1 Then
message = "Due: Close of business, " & dueDay
ElseIf dayPlan.Offset(0, 4) = 2 Then
message = "Due: Tomorrow, " & dueDay
Else
message = "Due: " & dueDay
End If
lync.SendText (message)
On Error Resume Next
lync.Close
Application.ScreenUpdating = True
Loop
analystOnlyWelcomeMessage = 1
contactCounter = 6
End Select
Next
Loop
End SubI know it is a fairly large program. But the real problem is where I highlighted in red. Im not sure if there is a syntax problem somewhere, but what I can say is that it definitely doesnt seem like any variables have empty or undelcared values. So I am quite confused.
Please let me know if you can help, and as always many thanks to the devoted community. Every step of this project that I have had problems with you guys have helped me
D1)
