Run-time error '13' VBA Code

[miffy45]

Hello everyone,

I keep facing this pop-up window eventough the VBA code works well.
Anyone can help what's wrong with my code? i'm kinda new to these codings.

Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String
Dim CloumnOrder As String
Dim Order As String

CloumnNameIssue = "F"
CloumnNameDate = "C"
CloumnNameRemStatus = "K"
CloumnOrder = "A"

RowNrNumeric = 3
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Order = Range(CloumnOrder + RowNrString).Value

Do While Issue <> ""
    If (RemStatus = "ON" And DateDiff("d", DueDate, Date) >= -1) Then
        TextDay = Day(DueDate)
        TextMonth = Month(DueDate)
        TextYear = Year(DueDate)
        MsgBox "Please update item no." + Order & vbCrLf & Issue & vbCrLf & "Due Date is : " + TextDay + "-" + TextMonth + "-" + TextYear
    End If
   
   
   
    RowNrNumeric = RowNrNumeric + 1
    RowNrString = RowNrNumeric
    Issue = Range(CloumnNameIssue + RowNrString).Value
    DueDate = Range(CloumnNameDate + RowNrString).Value
    RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
    Order = Range(CloumnOrder + RowNrString).Value
Loop

End Sub

 

[Peter_SSs]

What line of the code do you get this error on (you should always report that when reporting a vba error)?

 

[miffy45]

Noted, thank you for the information.

Ln 41.

DueDate = Range(CloumnNameDate + RowNrString).Value

I just noticed that the code won't read the next column even it's already meet the condition to trigger the pop-up.

 

[Peter_SSs]

Does column C have a formula that perhaps returns a date for one or more rows but then returns "" or something else other than a date?

If col C does have formulas, what is the formula in cell C3?

 

[miffy45]

Oh, Col B actually have the return date the configuration as below:

A	B	C
No.	Input Date	Due Date
1	IF(A3<>"",IF(B3<>"",B3,NOW()),"")	=IFERROR(B3+3,"")

 

[Peter_SSs]

1. I cannot tell what rows those formulas are on.
2. Are the formulas copied down the columns?

 

[miffy45]

1. The formulas in my excel are like below:

	A	B	C	G	K
1	No.	Input Date	Due Date	Status	Reminder
2	1	=IF(A2<>"",IF(B2<>"",B2,NOW()),"")	=IFERROR(B2+3,"")	Close/Open	=IF(OR(AND(C2<=TODAY(),G2<>"Close"),AND(C2-TODAY()=1,G2<>"Close")),"ON","OFF")
3	2	=IF(A3<>"",IF(B3<>"",B3,NOW()),"")	=IFERROR(B3+3,"")		=IF(OR(AND(C3<=TODAY(),G3<>"Close"),AND(C3-TODAY()=1,G3<>"Close")),"ON","OFF")
4	3	=IF(A4<>"",IF(B4<>"",B4,NOW()),"")	=IFERROR(B4+3,"")		=IF(OR(AND(C4<=TODAY(),G4<>"Close"),AND(C3-TODAY()=1,G4<>"Close")),"ON","OFF")

2. Yes, copied.

 

[Peter_SSs]

Try adding these two extra lines into your code where shown.

  RowNrNumeric = RowNrNumeric + 1
  RowNrString = RowNrNumeric
  Issue = Range(CloumnNameIssue + RowNrString).Value
  If Issue <> "" Then
    DueDate = Range(CloumnNameDate + RowNrString).Value
    RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
    Order = Range(CloumnOrder + RowNrString).Value
  End If
Loop

 

[miffy45]

Sorry for the late reply, it really works!
Thank you for your time, patience and solution.

But if you don't mind, could you explain to me?
How you get it needs Issue <> "" while the debug said the problem on DueDate?

 

[Peter_SSs]

it really works!
Thank you for your time, patience and solution.

You're welcome. Glad it worked for you.

How you get it needs Issue <> "" while the debug said the problem on DueDate?

The problem is that on the first row where Issue = "", the formula in the Date column (col C) also returns ""
You have declared DueDate as Date so this line
DueDate = Range(CloumnNameDate + RowNrString).Value
becomes DueDate = "" and Excel cannot convert "" to a Date, hence the error.
By checking the string for Issue first, we avoid that attempted conversion.