Hi,

I'm trying to write a small utility to convert names of days and months from
English to another language.

I have a form with four multi-line text boxes (FromMonth, FromDay, ToMonth,
ToDay), a "Translate" button and a "Cancel" button.

The English months & days are populated in the From textboxes and I want to
be able to paste the equivalent list of names in the other languages in the
to boxes, convert the text in the boxes to arrays, then loop through the
arrays and replace "January" with "Janvier" and "Monday" with "Lundi", etc.

When I run the code below, the first time through the arrays I get the
results I expect, but the remainder of each array never seems to process. I
step through the code and all looks good. If I manually run the Find/Replace
all is OK (February, March, April... are all found and replaced.)

Is there an Excel equivalent to word's Selection.HomeKey wdStory that I need
to invoke, or is there something wrong with my code?

Thanks so much for your help!



Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdTrans_Click()

Me.frMonths.Text = Replace(Me.frMonths.Text, Chr(13) & Chr(13), "")

Me.toMonths.Text = Replace(Me.toMonths.Text, vbCrLf, Chr(13))
Me.toMonths.Text = Replace(Me.toMonths.Text, Chr(13), Chr(13))

Me.toMonths.Text = Me.toMonths.Text & Chr(13)

Me.frDays.Text = Replace(Me.frDays.Text, Chr(13) & Chr(13), Chr(13))

Me.toDays.Text = Replace(Me.toDays.Text, vbCrLf, Chr(13))
Me.toDays.Text = Replace(Me.toDays.Text, Chr(13), Chr(13))

Me.toDays.Text = Me.toDays.Text & Chr(13)

Me.Hide

frMonthArray = Split(Me.frMonths.Text, Chr(13))
frDayArray = Split(Me.frDays.Text, Chr(13))

toMonthArray = Split(Me.toMonths.Text, Chr(13))
toDayArray = Split(Me.toDays.Text, Chr(13))

If UBound(frMonthArray) < UBound(toMonthArray) Then
MsgBox "From and to months don't have equal number of elements"
'Exit Sub
End If

If UBound(frDayArray) < UBound(toDayArray) Then
MsgBox "From and to days don't have equal number of elements"
Exit Sub
End If

'THIS WORKS 1st TIME THROUGH, BUT THE REST OF THE ARRAY DOES NOT FIND/REPLACE

For i = 0 To UBound(frMonthArray)
If frMonthArray(i) = "" Or frMonthArray(i) = vbCr Then GoTo
skipmonth
ActiveSheet.Cells.Replace What:=frMonthArray(i),
Replacement:=toMonthArray(i), LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
skipmonth:
Next i

For i = 0 To UBound(frDayArray)
Range("A1").Select
If frDayArray(i) = "" Or frDayArray(i) = vbCr Then GoTo skipday
ActiveSheet.Cells.Replace What:=frDayArray(i),
Replacement:=toDayArray(i), LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
skipday:
Next i
' Unload Me

End Sub

Private Sub UserForm_Activate()
Dim EngMonthsArr(11)
EngMonthsArr(0) = "January"
EngMonthsArr(1) = "February"
EngMonthsArr(2) = "March"
EngMonthsArr(3) = "April"
EngMonthsArr(4) = "May"
EngMonthsArr(5) = "June"
EngMonthsArr(6) = "July"
EngMonthsArr(7) = "August"
EngMonthsArr(8) = "September"
EngMonthsArr(9) = "October"
EngMonthsArr(10) = "November"
EngMonthsArr(11) = "December"

Dim EngDaysArr(6)
EngDaysArr(0) = "Monday"
EngDaysArr(1) = "Tuesday"
EngDaysArr(2) = "Wednesday"
EngDaysArr(3) = "Thursday"
EngDaysArr(4) = "Friday"
EngDaysArr(5) = "Saturday"
EngDaysArr(6) = "Sunday"

For i = UBound(EngMonthsArr) To 0 Step -1
Me.frMonths.Text = EngMonthsArr(i) & Chr(13) & Me.frMonths.Text
Next i

For i = UBound(EngDaysArr) To 0 Step -1
Me.frDays.Text = EngDaysArr(i) & Chr(13) & Me.frDays.Text
Next i

End Sub

--
Steven Lee
Vaporloop Technology Solutions