User-defined type not defined - vba/excel error

[orangestripes]

I am getting this error message popping up in a workbook I have, but not when I am running the macro (there's only one in the workbook at the moment). The macro runs fine with no errors, but any time I enter any data into a cell in any worksheet I get this pop up error message.

I've looked in tools/references and everything looks ok there, I have stripped out the raw data and put it in a brand new workbook and then as soon as I re-introduce the macro code, the error returns. Here's the code in question, I have gone through it multiple times and can't see any issues and as I say it runs fine and does what I want it to do.

Sub ImportEOD()
Application.ScreenUpdating = False

Dim wb As Workbook
Set wb = Application.ActiveWorkbook
Dim rd As Worksheet
Set rd = wb.Sheets("RawData")
Dim rdlr As Long
If IsEmpty(rd.Range("A2")) Then
rdlr = 3
Else: rdlr = rd.Range("A1").End(xlDown).Row
End If

Dim nwb As Workbook
Set nwb = Application.Workbooks.Open(Filename:="S:\Hard Wired Measures.....DO NOT DELETE\Data Extracts\Power BI\EOD new data.xlsx")
Dim ws As Worksheet
Set ws = nwb.Sheets(1)
Dim lr As Long
lr = ws.Range("A1").End(xlDown).Row

ws.Range("A1:BK" & lr).Sort Key1:=ws.Range("O1"), Order1:=xlAscending, Header:=xlYes
Dim err As Integer
err = Application.WorksheetFunction.CountIf(ws.Range("O2:O" & lr), "<1")

If err > 0 Then
    Dim rngend As Integer
    rngend = err + 1
    Dim rng As Range, cell As Range
    Set rng = ws.Range("O2:O" & rngend)
    For Each cell In rng
        If cell.Value < 1 Then
        cell.Offset(0, -4).Delete Shift:=xlToLeft
        End If
    Next cell
End If

ws.Range("AJ2:AJ" & lr).Replace What:=" Callout Only", Replacement:="", MatchCase:=False
ws.Range("AJ2:AJ" & lr).Replace What:="Tanker Rate - ", Replacement:=""
ws.Range("AJ2:AJ" & lr).Replace What:="QUOTED ", Replacement:=""
ws.Range("AJ2:AJ" & lr).Replace What:="~*", Replacement:=""
ws.Range("AJ2:AJ" & lr).Replace "`", "'"
ws.Range("A1:BJ" & lr).Replace What:=" Quoted Works Only", Replacement:=""

Dim dt As Date
Dim dt2 As Date
Dim dt3 As Date
dt = Format(Application.WorksheetFunction.Min(ws.Range("P2:P" & lr)), "dd/mm/yyyy")
dt2 = Format(DateSerial(Year(dt), Month(dt) + 1, 1), "dd/mm/yyyy")
dt3 = Format(DateSerial(Year(dt), Month(dt) + 2, 1), "dd/mm/yyyy")

wb.Activate
rd.AutoFilterMode = False

rd.Range("A1:BJ" & rdlr).AutoFilter Field:=16, Criteria1:=Array(Format(dt, "mmm-yy"), Format(dt2, "mmm-yy"), Format(dt3, "mmm-yy")), Operator:=xlFilterValues
rd.Range("A1:BJ" & rdlr).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Clear
rd.AutoFilterMode = False
rd.Range("A1:BJ" & rdlr).Sort Key1:=rd.Range("A1"), Order1:=xlAscending, Header:=xlYes
rdlr = rd.Range("A1").End(xlDown).Row + 1

nwb.Activate
ws.Range("A2:BJ" & lr).Copy Destination:=rd.Range("A" & rdlr)
Application.DisplayAlerts = False
nwb.Close
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

 

[jkpieterse]

Two questions:

1. What error do you get exactly?
2. If you click Debug, which line of code gets highlighted in yellow?

 

[orangestripes]

I get an error pop up that says user defined type not defined. It doesn't come up when running the code though and there is nothing to debug.

 

[RoryA]

If you start Excel in safe mode (hold down the Ctrl key while starting the application) do you have the same problem?

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA in Excel - User-defined type not defined error

I am getting this error message popping up in a workbook I have, but not when I am running the macro (there's only one in the workbook at the moment). The macro runs fine with no errors, but any t...

stackoverflow.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.