Upgraded to Excel 2007 "Compile error"

S

Steved

Hello from Steved

The below has been runnining fine on previous Excel version office 2000 now
That the company has upgraded us to Office 2007, What change is require
please for it to operate on the Office 2007. Thankyou.

Compile error
Can't find project or library

Highlighting "UCase"

Sub FindPart()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("F:F")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
' commenting out the next line should do it
MsgBox "Click to Continue Searching"
' Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox " School Not Found"
End If
End Sub
 
G

Gary Keramidas

in the vb editor, click tools / references and look for any references that
have missing next to them and click to check the box.
 
S

Steved

Hello from Steved

Thankyou the box was checked so I unchecked it and now it works perfectly.

Cheers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top