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 Withey" wrote in message
...
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,

Depending on your version of excel you might have
InStrRev

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

Dan E

"Ross Withey" wrote in message ...
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

Sorry, I forgot to say I'm on Excel 97 which does not recognise InstRev. Do
I need a custom function?
Thank you!
Ross.

"Chip Pearson" wrote in message
...
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 Withey" wrote in message
...
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

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



"Dan E" wrote in message
...
Ross,

Depending on your version of excel you might have
InStrRev

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

Dan E

"Ross Withey" wrote in message
...
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

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


Ross Withey wrote in message
...
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

Stuart,
Thanks -that looks elegant!
Ross

"Stuart" wrote in message
...
fruitName = "Bananas"
For t = 0 To Len(fruitName)
If InStr(Len(fruitName) - t, fruitName, "a") Then Exit For
Next t
MsgBox Len(fruitName) - t


Ross Withey wrote in message
...
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

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

--
Don Guillett
SalesAid Software

"Ross Withey" wrote in message
...
Sorry, I forgot to say I'm on Excel 97 which does not recognise InstRev.
Do
I need a custom function?
Thank you!
Ross.

"Chip Pearson" wrote in message
...
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 Withey" wrote in message
...
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

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


"Ross Withey" wrote in message
...
Stuart,
Thanks -that looks elegant!
Ross

"Stuart" wrote in message
...
fruitName = "Bananas"
For t = 0 To Len(fruitName)
If InStr(Len(fruitName) - t, fruitName, "a") Then Exit For
Next t
MsgBox Len(fruitName) - t


Ross Withey wrote in message
...
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

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

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


"Stuart" wrote in message
...
Not finding the characture does not cause an error!



Trevor Shuttleworth wrote in message
...
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


"Ross Withey" wrote in message
...
Stuart,
Thanks -that looks elegant!
Ross

"Stuart" wrote in message
...
fruitName = "Bananas"
For t = 0 To Len(fruitName)
If InStr(Len(fruitName) - t, fruitName, "a") Then Exit For
Next t
MsgBox Len(fruitName) - t


Ross Withey wrote in message
...
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