File or function not found error

I've written the following:

Sub CountReasonContactCode()
Dim Wksht As Worksheet
Dim rReason As Range
Dim lRow As Long
Dim lCurRow As Long
Dim rCell As Range
Dim l16Rct As Long
Dim l16Act As Long
Dim l16BGct As Long
Dim lCt As Long

lRow = 107

l16Rct = 0
l16Act = 0
l16BGct = 0
Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
rReason.Select

For Each rCell In rReason
If rCell = "13" Then

lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
If lCt > 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If
(rest of code not shown)

I'm getting a compile error on "InstrUcase". I Googled here, and looked
for a "missing" component in VBE references. None there. What is
causing this? I know it's not the instr because without the ucase it
works fine. I looked for ucase in VBE help, and it isn't there. Not
sure this means anything because lots of VBA
command/function/properties aren't listed in help.
Thanks for the help.

lCt = InStr(1, ucase(rCell.Offset(0, 2).Value), "R")

or for a case insensitive ( r = R, R = R, r = r)

lCt = InStr(1, rCell.Offset(0, 2).Value, "R", vbTextcompare)


--
Regards,
Tom Ogilvy



"davegb" <[email protected]> wrote in message
news:[email protected]...
> I've written the following:
>
> Sub CountReasonContactCode()
> Dim Wksht As Worksheet
> Dim rReason As Range
> Dim lRow As Long
> Dim lCurRow As Long
> Dim rCell As Range
> Dim l16Rct As Long
> Dim l16Act As Long
> Dim l16BGct As Long
> Dim lCt As Long
>
> lRow = 107
>
> l16Rct = 0
> l16Act = 0
> l16BGct = 0
> Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
> rReason.Select
>
> For Each rCell In rReason
> If rCell = "13" Then
>
> lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
> If lCt > 0 Then
> l16Rct = l16Rct + 1
> lCt = 0
> End If
> (rest of code not shown)
>
> I'm getting a compile error on "InstrUcase". I Googled here, and looked
> for a "missing" component in VBE references. None there. What is
> causing this? I know it's not the instr because without the ucase it
> works fine. I looked for ucase in VBE help, and it isn't there. Not
> sure this means anything because lots of VBA
> command/function/properties aren't listed in help.
> Thanks for the help.
>

You cannot combine functions like that. Each function can be combined but
must be arranged to act on the elements relevant for the function. In this
case you want the UCase function to act on the test string part of the InStr
function e.g........

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")

BTW: UCase is referenced in VBA help

--
Cheers
Nigel



"davegb" <[email protected]> wrote in message
news:[email protected]...
> I've written the following:
>
> Sub CountReasonContactCode()
> Dim Wksht As Worksheet
> Dim rReason As Range
> Dim lRow As Long
> Dim lCurRow As Long
> Dim rCell As Range
> Dim l16Rct As Long
> Dim l16Act As Long
> Dim l16BGct As Long
> Dim lCt As Long
>
> lRow = 107
>
> l16Rct = 0
> l16Act = 0
> l16BGct = 0
> Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
> rReason.Select
>
> For Each rCell In rReason
> If rCell = "13" Then
>
> lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
> If lCt > 0 Then
> l16Rct = l16Rct + 1
> lCt = 0
> End If
> (rest of code not shown)
>
> I'm getting a compile error on "InstrUcase". I Googled here, and looked
> for a "missing" component in VBE references. None there. What is
> causing this? I know it's not the instr because without the ucase it
> works fine. I looked for ucase in VBE help, and it isn't there. Not
> sure this means anything because lots of VBA
> command/function/properties aren't listed in help.
> Thanks for the help.
>