Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upgraded to Excel 2007 "Compile error"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upgraded to Excel 2007 "Compile error"
in the vb editor, click tools / references and look for any references that have missing next to them and click to check the box. -- Gary "Steved" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upgraded to Excel 2007 "Compile error"
Hello from Steved
Thankyou the box was checked so I unchecked it and now it works perfectly. Cheers "Gary Keramidas" wrote: in the vb editor, click tools / references and look for any references that have missing next to them and click to check the box. -- Gary "Steved" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How correct (or remove) "Compile error in hidden module: DistMon" | Excel Discussion (Misc queries) | |||
just upgraded to Office 2007. Where's the "Save As" tag? | Excel Discussion (Misc queries) | |||
Compile Error in Excel 2004 when Inputbox contains "VBCRLF" | Excel Discussion (Misc queries) | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |