Rowan,

Thank you so much for all of the help. Unfortunately, the code does not
work for me. Since I was getting type mismatch errors - I got rid of
the variable declarations. That stopped that problem.

Also, for some reason, the Values(i), in the watch window, returned
nothing. I tried some tweaking, but it just gave me weird values.

Does this code work for you? I'll give you an example of the text that
I'm trying to translate into US$'s:

"car allowance will be 500 yen over budget due to the two unexpected 250
yen payments that were not anticipated"

Would you have any idea why this code would not work? Tracking it in
the watch window always gets me hopeful - but then the final conversion
should happen and I either get a type mismatch, or it simply won't do
what is expected.

Again, thank you so much for all that you have put into this!

Best Regards,
Anita
Rowan wrote:
Anita

The nested instring finds the first space after finding the currency.

isSpace = InStr(InStr(startNo, myText , oldCurr) + 1, myText, " ")

The nested portion finds the position of the Currency and moves one
character right from there as the start position for the outer instring. Why
I didn't just add one to the isCurr variable set in the line above and use
that I have no idea - I was in a bit of a hurry.

Anyway, I have had a little more time to look at it now and having the
currency after the value actually makes the vba a little easier using the
instrev function.

This new macro is very similar to the old one but once finding the currency
it uses the instrrev function to move back to the previous space and from
there picks up the value. Again these values are placed in an array (which I
have also tidied up so that it doesn't assume only 30 repetitions).

In the final loop each value from the array is used in a find and replace
exercise.

I hope this makes more sense this time.

Regards
Rowan - code follows

Sub ChangeCurrnew()

Dim isSpace, isCurr, startNo As Integer
Dim i As Integer
Dim myText As String
Dim myValues() As Double
Dim myVal As Variant
Dim msg As String

Dim oldCurr, newCurr As String
Dim convertCurr As Double

msg = "Enter Currency Code to be" & Chr(13)
msg = msg & "replaced e.g. AUD."
oldCurr = InputBox(msg)

msg = "Enter New Currency Code" & Chr(13)
msg = msg & "e.g. US$."
newCurr = InputBox(msg)

msg = "Enter conversion factor" & Chr(13)
msg = msg & "e.g. 1.2"
convertCurr = InputBox(msg)

myText = ActiveCell.Value

startNo = 1
isCurr = 1
i = 0
Do While isCurr 0
isCurr = InStr(startNo, myText, oldCurr)
If isCurr 0 Then
isSpace = InStrRev(myText, " ", isCurr)
ReDim Preserve myValues(i)
myValues(i) = Mid(myText, isSpace, isCurr - isSpace)
startNo = isCurr + 1
i = i + 1
End If
Loop


For Each myVal In myValues
ActiveCell.Replace What:="" & myVal & oldCurr _
, Replacement:="" & Round(myVal * convertCurr, 2) _
& newCurr, LookAt:=xlPart
Next

End Sub

"Anita" wrote:


Rowan,

Thank you so much for the code. I'm not familiar with some of the
syntax and so I am unable to make it work unless I put the currency
description in front of the currency amount. All of the comments that I
receive have the currency description following the currency amount.

I've been able to figure out the instr function, but not the second
nested one. I'm not sure why I'm coming up with the number that I come
up with.

Would you be able to help me understand that nested instr? I think that
this is what is tripping me up.

Again, thank you so much for the wonderful code!!

Anita


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!