Substitute worksheet function

[SIGE]

Hi All,

I would like to replace a part of the text in A3 by "" (empty)for the
value in A5. In short the substitute worksheetfunction ... it is
bugging!
Any reason why?


Sub d()
Dim naam As String

naam = Application.WorksheetFunction.Substitute(ws.Range("A3").Value,
ws.Range("A5").Value, "")
MsgBox naam
End Sub

 

[Vasant Nanavati]

Could you define "it is bugging?"

 

[SIGE]

Hi Vasant
Seems I bug in my for For Each-loop ...as underneath code works fine.
I should replace in all worksheets of the active workbook the string
(always on sheet1)in A5 with "" ...


Sub d()
Dim naam As String
Dim ws As Worksheets

'For Each ws In ActiveWindow.SelectedSheets

naam = Application.WorksheetFunction.Substitute(Worksheets("Sheet1").Range("A3").Value,
Worksheets("Sheet1").Range("A5").Value, "")
MsgBox naam

'Next

End Sub

 

[SIGE]

Got what I wanted,
Cheers Sige

sub d()
Dim naam As String
Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets
naam = Application.WorksheetFunction.Substitute(ws.Range("A3").Value,
Worksheets(1).Range("A5").Value, "")
MsgBox naam
ws.Name = naam
Next ws


End Sub