I wrote a VBA function that checks several things and returns a code
that triggers conditional formatting. Everything works well except
that the formula often triggers a #value
error. It does this for all
cells containing the formula (a few thousands). The error appears
whenever I open another workbook. It sometimes happen when I don't
open another workbook.
The function is here:
Function jjcheck(STDTRow As Integer, cuCOL As Integer, cuMax As Integer, trmEnd As Integer, trmEMax As Integer, worksheetSRC As String, lstCTCT As Date) As Variant
'use in spreadsheet =jjcheck(B2,Variables!$G$4,Variables!$G$2,Variables!$F$2,"SRM",U2)
'=jjcheck(B2,Variables!$G$4,Variables!$F$4,Variables!$G$2,Variables!$F$2,"SRM",IF(ISBLANK(U2),TODAY(),U2))
Dim V() As String, dayMax As Integer, lookup As Date, theDiff As Integer, lstContact As String
V = Split(ActiveWorkbook.ActiveSheet.Cells(1, 2).Value, "-"): dayMax = V(1): theDiff = 256
lookup = lstCTCT
theDiff = DateDiff("d", lookup, Date): lstContact = ""
If theDiff > dayMax Then lstContact = "Alert"
Dim STDcu As Integer, STtrmEnd As Date, daysTOtrmend As Integer
STDcu = ActiveWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, cuCOL).Value
STtrmEnd = ActiveWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, trmEnd).Value
daysTOtrmend = DateDiff("d", Date, STtrmEnd)
If STDcu < cuMax And daysTOtrmend < trmEMax Then
jjcheck = "CHECK" & lstContact
ElseIf daysTOtrmend < trmEMax / 2 Then
jjcheck = "ETerm" & lstContact
Else
jjcheck = "" & lstContact
End If
End Function
suspecting that the error might be due to the times cell U2
is empty,I
changed what I put for lstCTCT
to IF(ISBLANK(U2),TODAY(),U2)
That did not seem to help.The image shows what happens in the worksheet
Thanks for any insight you can give on that.
Let me know if this makes any difference? The main change is from
ActiveWorkbook
to ThisWorkbook
. (a few others just to tidy up)
Function jjcheck(STDTRow As Integer, cuCOL As Integer, cuMax As Integer, trmEnd As Integer, trmEMax As Integer, worksheetSRC As String, lstCTCT As Date) as Variant
'use in spreadsheet =jjcheck(B2,Variables!$G$4,Variables!$G$2,Variables!$F$2,"SRM",U2)
'=jjcheck(B2,Variables!$G$4,Variables!$F$4,Variables!$G$2,Variables!$F$2,"SRM",IF(ISBLANK(U2),TODAY(),U2))
Dim V() As String, lstContact As String
Dim dayMax As Integer, theDiff As Integer, STDcu As Integer, daysTOtrmend As Integer
Dim lookup As Date, STtrmEnd As Date
STDcu = ThisWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, cuCOL).Value
STtrmEnd = ThisWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, trmEnd).Value
daysTOtrmend = DateDiff("d", Date, STtrmEnd)
V = Split(ThisWorkbook.ActiveSheet.Cells(1, 2).Value, "-"): dayMax = V(1): theDiff = 256
lookup = lstCTCT
theDiff = DateDiff("d", lookup, Date): lstContact = vbNulltring
If theDiff > dayMax Then lstContact = "Alert"
If STDcu < cuMax And daysTOtrmend < trmEMax Then
jjcheck = "CHECK" & lstContact
ElseIf daysTOtrmend < trmEMax / 2 Then
jjcheck = "ETerm" & lstContact
Else
jjcheck = vbNullString & lstContact
End If
End Function