Excel VBA works in 2003 but not in 2010:

The code works perfect in Excel 2003. In 2010, i get the error:

Compile error:
Can’t find project or library

After clicking the error msg box,

i    is selected (not yellow)  [from "For i = 1 To LastRowDailySource"]

while Sub UpdateDailyAndWeeklyHist() is selected yellow.

thanks for your help.


Sub UpdateDailyAndWeeklyHist()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

'dim the row variables
Dim LastRowDailySource As Long
Dim LastRowWeeklySource As Long
Dim LastPrxdDailySourceRow As Long
Dim LastPrxdWeeklySourceRow As Long
Dim DailyRowsToInsert As Integer
Dim WeeklyRowsToInsert As Long
Dim rngInsertLoc As Range

'dim the date variables
Dim LastProcessedDaily As Date
Dim LastProcessedWeekly As Date

'dim the workbooks
Dim TargetWb As Workbook
Dim DailyWb As Workbook
Dim WeeklyWb As Workbook

'dim the worksheets
Dim TargetWsDaily As Worksheet
Dim TargetWsWeekly As Worksheet
Dim DailyWs As Worksheet
Dim WeeklyWs As Worksheet

'set and open workbooks
Set TargetWb = ActiveWorkbook
Set DailyWb = Workbooks.Open("G:\data\___pcp\___xls\Tulip\tulip_monitoring_report.xls", UpdateLinks:=1)
Set WeeklyWb = Workbooks.Open("G:\data\___pcp\___xls\Tulip\tulip_r&a_report.xls", UpdateLinks:=1)

'set worksheets
'TargetWs = Workbooks("Your workbook.xls").Sheets("daily hist")
Set TargetWsDaily = TargetWb.Sheets("DailyHist")
Set TargetWsWeekly = TargetWb.Sheets("HistMrgn")
Set DailyWs = DailyWb.Sheets("hist")
Set WeeklyWs = WeeklyWb.Sheets("hist mrgn")

'activate TargetWb
TargetWb.Activate

'get last processed dates
With TargetWsDaily
    .Select
    .Range("A1").Select
End With
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
LastProcessedDaily = ActiveCell.Value

With TargetWsWeekly
    .Select
    .Range("A1").Select
End With
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
LastProcessedWeekly = ActiveCell.Value


'activate daily source sheet
DailyWb.Activate
'get the row number of the last non-empty cell in the daily source sheet
LastRowDailySource = DailyWs.Range("A" & Rows.Count).End(xlUp).Row
'search for the last processed daily date in source sheet and get row number
For i = 1 To LastRowDailySource
    If DailyWs.Range("A" & i).Value = LastProcessedDaily Then PrxdDailySourceRow = i
Next
'calculate number of rows to insert for daily
DailyRowsToInsert = LastRowDailySource - PrxdDailySourceRow - 1 '-1 nur bei daily

'activate weekly source sheet
WeeklyWb.Activate
'get the row number of the last non-empty cell in the weekly source sheet
LastRowWeeklySource = WeeklyWs.Range("A" & Rows.Count).End(xlUp).Row
'search for the last processed weekly date in source sheet and get row number
For i = 1 To LastRowWeeklySource
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then PrxdWeeklySourceRow = i
Next
'calculate number of rows to insert for weekly
WeeklyRowsToInsert = LastRowWeeklySource - PrxdWeeklySourceRow

'close source workbooks
DailyWb.Close False
WeeklyWb.Close False


'insert daily data in target book
If DailyRowsToInsert > 0 Then
   TargetWsDaily.Activate
   TargetWsDaily.Range("A" & Rows.Count).End(xlUp).Select
   For i = 1 To DailyRowsToInsert
   ActiveCell.Offset(-1, 0).EntireRow.Insert
   Next
End If
'fill down daily formulas
ActiveCell.Offset(-5, 0).Select
currentrow = Selection.Row()
lastcolumn = Cells(currentrow, Columns.Count).End(xlToLeft).Column
lastrow = Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.Resize(, lastcolumn).Copy Range(Cells(currentrow + 1, 1), Cells(lastrow, lastcolumn))

'insert weekly data in target book
If WeeklyRowsToInsert > 0 Then
   TargetWsWeekly.Activate
   TargetWsWeekly.Range("A" & Rows.Count).End(xlUp).Select
   For i = 1 To WeeklyRowsToInsert
   ActiveCell.Offset(-1, 0).EntireRow.Insert
   Next
End If
'fill down weekly formulas
ActiveCell.Offset(-5, 0).Select
currentrow = Selection.Row()
lastcolumn = Cells(currentrow, Columns.Count).End(xlToLeft).Column
lastrow = Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.Resize(, lastcolumn).Copy Range(Cells(currentrow + 1, 1), Cells(lastrow, lastcolumn))

TargetWb.Sheets("Output").Activate

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = True

End Sub

Open in new window

stmoritzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
When Excel VBA reports problems with basic VBA functions such as Trim or Space and possibly displays an error stating that it "cannot find project or library", the problem is most likely a missing library. This can occur when move a workbook from one workstation to another or after installing software updates or uninstalling software. To resolve the problem, switch to the VBE (press CTRL+F11), open the References dialog (choose the menu command Tools->References), and look for any library references preceded with the text "MISSING:". For each occurrence of such a library, try unchecking the library and then searching the list for a similarly named library. If the library is no longer required then just uncheck it.

One particular library that causes problems is the Ref Edit Library which appears when the RefEdit control is placed on a user form. The library reference is not removed when the control is deleted. Also, the library reference is not needed even if the control remains on the form. The best course of action is to remove this library reference whether or not the control is used.

Kevin
0
All Courses

From novice to tech pro — start learning today.