Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Macro has stopped working - Compile Error
Chaps,
I'm running a macro which has always worked previously. It now stops immediately and gives the error: Compile Error: Can't find project or library. The macro compares two lists of staff numbers - a current one and a historic one. It adds any missing staff numbers from the current list onto the old one. Hitting "debug" highlights the term "str" in the floowing line: CellToCheck = "A" + Trim(Str(5 + NumberChecked)) Any help you can provide will be very greatly appreciated. Full code below: Private Sub ConsolidateRenewalsAdviserList_Click() Dim AdviserDisplay As String Dim AdviserDisplayCell As String Dim CellToInsertInto As String Dim TotalAdvisers As Integer Dim StartRow As Integer Dim CurrentRow As Integer StartRow = 8 TotalAdvisers = Worksheets("Master").Range("RNL65536").Value For CurrentRow = StartRow To TotalAdvisers + StartRow - 1 AdviserDisplayCell = "A" + Trim(Str(CurrentRow)) AdviserDisplay = Range(AdviserDisplayCell).Value Dim AdviserFound As String Dim CellToCheck As String Dim NumberChecked As Integer AdviserFound = "Not Found" NumberChecked = 0 While AdviserFound = "Not Found" And NumberChecked < Worksheets("Master").Range("RNLH65536") CellToCheck = "A" + Trim(Str(5 + NumberChecked)) If AdviserDisplay = Worksheets("YTDRenewalsHistory").Range(CellToCheck ) Then AdviserFound = "Found" Else NumberChecked = NumberChecked + 1 End If Wend If AdviserFound = "Not Found" Then CellToInsertInto = "A" + Trim(Str(Worksheets("Master").Range("RNLH65536") + 5)) Worksheets("YTDRenewalsHistory").Range(CellToInser tInto).Value = "'" & AdviserDisplay End If Next CurrentRow End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Macro has stopped working - Compile Error
In the VBE select Tools - References and look for a checked item starting
with "Missing:". You might be able to get away with just unchecking the reference or you might have to update the reference... -- HTH... Jim Thomlinson "mr tom" wrote: Chaps, I'm running a macro which has always worked previously. It now stops immediately and gives the error: Compile Error: Can't find project or library. The macro compares two lists of staff numbers - a current one and a historic one. It adds any missing staff numbers from the current list onto the old one. Hitting "debug" highlights the term "str" in the floowing line: CellToCheck = "A" + Trim(Str(5 + NumberChecked)) Any help you can provide will be very greatly appreciated. Full code below: Private Sub ConsolidateRenewalsAdviserList_Click() Dim AdviserDisplay As String Dim AdviserDisplayCell As String Dim CellToInsertInto As String Dim TotalAdvisers As Integer Dim StartRow As Integer Dim CurrentRow As Integer StartRow = 8 TotalAdvisers = Worksheets("Master").Range("RNL65536").Value For CurrentRow = StartRow To TotalAdvisers + StartRow - 1 AdviserDisplayCell = "A" + Trim(Str(CurrentRow)) AdviserDisplay = Range(AdviserDisplayCell).Value Dim AdviserFound As String Dim CellToCheck As String Dim NumberChecked As Integer AdviserFound = "Not Found" NumberChecked = 0 While AdviserFound = "Not Found" And NumberChecked < Worksheets("Master").Range("RNLH65536") CellToCheck = "A" + Trim(Str(5 + NumberChecked)) If AdviserDisplay = Worksheets("YTDRenewalsHistory").Range(CellToCheck ) Then AdviserFound = "Found" Else NumberChecked = NumberChecked + 1 End If Wend If AdviserFound = "Not Found" Then CellToInsertInto = "A" + Trim(Str(Worksheets("Master").Range("RNLH65536") + 5)) Worksheets("YTDRenewalsHistory").Range(CellToInser tInto).Value = "'" & AdviserDisplay End If Next CurrentRow End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Macro has stopped working - Compile Error
Fantastic.
We had a software upgrade recently. I did this immediately afterwards, but I've just checked, and it clearly didn't stay set. That's saved my bacon - thanks, Jim. Tom. "Jim Thomlinson" wrote: In the VBE select Tools - References and look for a checked item starting with "Missing:". You might be able to get away with just unchecking the reference or you might have to update the reference... -- HTH... Jim Thomlinson "mr tom" wrote: Chaps, I'm running a macro which has always worked previously. It now stops immediately and gives the error: Compile Error: Can't find project or library. The macro compares two lists of staff numbers - a current one and a historic one. It adds any missing staff numbers from the current list onto the old one. Hitting "debug" highlights the term "str" in the floowing line: CellToCheck = "A" + Trim(Str(5 + NumberChecked)) Any help you can provide will be very greatly appreciated. Full code below: Private Sub ConsolidateRenewalsAdviserList_Click() Dim AdviserDisplay As String Dim AdviserDisplayCell As String Dim CellToInsertInto As String Dim TotalAdvisers As Integer Dim StartRow As Integer Dim CurrentRow As Integer StartRow = 8 TotalAdvisers = Worksheets("Master").Range("RNL65536").Value For CurrentRow = StartRow To TotalAdvisers + StartRow - 1 AdviserDisplayCell = "A" + Trim(Str(CurrentRow)) AdviserDisplay = Range(AdviserDisplayCell).Value Dim AdviserFound As String Dim CellToCheck As String Dim NumberChecked As Integer AdviserFound = "Not Found" NumberChecked = 0 While AdviserFound = "Not Found" And NumberChecked < Worksheets("Master").Range("RNLH65536") CellToCheck = "A" + Trim(Str(5 + NumberChecked)) If AdviserDisplay = Worksheets("YTDRenewalsHistory").Range(CellToCheck ) Then AdviserFound = "Found" Else NumberChecked = NumberChecked + 1 End If Wend If AdviserFound = "Not Found" Then CellToInsertInto = "A" + Trim(Str(Worksheets("Master").Range("RNLH65536") + 5)) Worksheets("YTDRenewalsHistory").Range(CellToInser tInto).Value = "'" & AdviserDisplay End If Next CurrentRow End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet macro stopped working! | Excel Discussion (Misc queries) | |||
Macro stopped working | Excel Discussion (Misc queries) | |||
Macro Suddenly Stopped Working | Excel Programming | |||
Macro has stopped working!!! | Excel Programming | |||
why did the macro stopped working? | Excel Programming |