Private Sub Question
If you're using xl2k or higher, look at Replace in VBA's help.
Dim myStr As String
myStr = "JANAURY [2008]"
myStr = Replace(myStr, "[", "")
myStr = Replace(myStr, "]", "")
MsgBox myStr
If you wanted, you could nest the replaces:
myStr = Replace(Replace(myStr, "[", ""), "]", "")
(I find that more difficult to read.)
If you're using xl97 (or have to support it), then you could use:
mystr = application.substitute(myStr, "[", "")
=======
ps. Instead of using WorksheetFunction.Find, you can use VBA's own Instr().
You may be getting errors from the .find version if the space character wasn't
found.
Richard wrote:
Jacob
I don't quite understand your response, but I probably didn't make my self
very clear.
I am trying to create a string from the active cell contents which might
look like JANAURY [2008] and needs to look like JANUARY 2008, without the [ ]
as these brackets cannot be used when naming ranges
"Jacob Skaria" wrote:
From what you mentioned; the below if copied to a separate module should give
error. Try and feedback....You dont need to use worksheetfunction.Find...See
below
Public Blah As String
Private Sub Second()
Dim i as Long, LastColumn as Long
LastColumn = 10
For i = 2 To LastColumn
Blah = Split(ActiveCell)(0)
Next
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"Richard" wrote:
Hi
I have the following
Public Blah As String
I then run Sub dia()
which as part of it's code runs private sub second()
Part of the code of private sub second() is
For i = 2 To FinalColumn
Blah = Left(ActiveCell, WorksheetFunction.Find(" ", ActiveCell) - 1)
etc etc
when the code gets to the above line I get a run-time error 13, type mismatch
If sub second() is not private, I don't get this error.
Can someone please advise how to rectify this issue
thanks
Richard
--
Dave Peterson
|