Oops I put an x in the name to test in on 2000. Try this version
#If VBA6 Then
#Else
Function Replace(expression As String, find_string As String, replacement As
String)
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String
sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i
Replace = sTemp
End Function
#End If
--
HTH
Bob Phillips
"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Sorry Max,
>
> Missed the XL97 at the foot. About time you entered the 21st century young
> man :-).
>
> Add this to your code
>
> #If VBA6 Then
> #Else
> Function Replacex(expression As String, find_string As String, replacement
> As String)
> Dim i As Long
> Dim iLen As Long
> Dim iNewLen As Long
> Dim sTemp As String
>
> sTemp = expression
> iNewLen = Len(find_string)
> For i = 1 To Len(sTemp)
> iLen = Len(sTemp)
> If Mid(sTemp, i, iNewLen) = find_string Then
> sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
> i - iNewLen + 1)
> i = i + iNewLen - 1
> End If
> Next i
>
> Replacex = sTemp
>
> End Function
> #End If
>
> This will still work then when you upgrade.
>
> --
> HTH
>
> Bob Phillips
>
> "Max" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the suggestions, Bob !
> >
> > Tried:
> > > Dim sh
> > > For Each sh In ActiveWorkbook.Worksheets
> > > sh.Name = Replace(sh.Range("A2").Value, "/", "-")
> > > Next sh
> >
> > but hit a compile error: Sub or Function not defined:
> > "Replace" was highlighted in this line
> > > sh.Name = Replace(sh.Range("A2").Value, "/", "-")
> >
> > Think maybe my Excel 97 just doesn't have this function ..
> >
> > As for your 2nd suggestion, I got it to work after amending slightly the
> > line:
> > > sh.Name = Format(sh.Name, "yyyy-mmm-dd")
> >
> > to:
> > > sh.Name = Format(sh.Range("A2"), "yyyy-mmm-dd")
> >
> > (Amended after comparing Mangesh's code - which worked earlier - with
> yours)
> >
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> >
> >
>
>
Bookmarks