Instr function problem

  • Thread starter Thread starter Ross Withey
  • Start date Start date
R

Ross Withey

Hello,
If cell A1 has the string value "Bananas"

Instr(1,"Range("A1"), "a") finds the first "a". My problem is, I want to
find the final "a".
Can you suggest the right code? I know I can increase the "1", but I need to
find the final "a" at the first attempt in a loop. Can you help please?
Thanks,
Ross
 
Ross,

Use the InStrRev function. E.g.,

Debug.Print InStrRev(Range("A1"), "a", -1, vbTextCompare)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Ross,

Depending on your version of excel you might have
InStrRev

InStrRev(Range("A1"), "a") will return 6 if A1 is banana

Dan E
 
Sorry, I forgot to say I'm on Excel 97 which does not recognise InstRev. Do
I need a custom function?
Thank you!
Ross.
 
Gentlemen,
I have come up with this to reverse a range, so that Instr could then be
used. It is not very elegant, though. Can you think of anything better?
Ross

Sub Reversit()
x = Len(Range("A1")) + 1
For r = 1 To x
Range("A2") = Mid(Range("A1"), r, 1) & Range("A2")
Next
End Sub
 
fruitName = "Bananas"
For t = 0 To Len(fruitName)
If InStr(Len(fruitName) - t, fruitName, "a") Then Exit For
Next t
MsgBox Len(fruitName) - t
 
Stuart,
Thanks -that looks elegant!
Ross

Stuart said:
fruitName = "Bananas"
For t = 0 To Len(fruitName)
If InStr(Len(fruitName) - t, fruitName, "a") Then Exit For
Next t
MsgBox Len(fruitName) - t


need
Click to expand...
 
yes, use this

Function InStrRev(Strng As String, Char As String) As Integer
Dim Lngth As Integer, i As Integer
Lngth = Len(Strng)
For i = Lngth To 1 Step -1
If Mid(Strng, i, 1) = Char Then
InStrRev = i
Exit Function
End If
Next i
End Function
'Howard Groves cmmroom@ ddre.detroitdiesel.com
 
Ross

unless you can guarantee that the cell/string will contain what you are
looking for, you may need to modify the code slightly:

Sub InstrTest()
'fruitName = "Bananas"
fruitName = "Melon"
For t = 0 To Len(fruitName)
On Error Resume Next
If InStr(Len(fruitName) - t, LCase(fruitName), "a") Then Exit For
On Error GoTo 0
Next t
If t = Len(fruitName) Then
MsgBox "Not found"
Else
MsgBox Len(fruitName) - t
End If
End Sub

And you probably need to check the case if it's an alpha character you're
looking for.

Regards

Trevor
 
Did for me. Run-time error '5': Invalid procedure call or argument.

on "If InStr(Len(fruitName) - t, LCase(fruitName), "a") Then "
 
Back
Top