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

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

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