Private Sub Question

[Richard]

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

 

[Jacob Skaria]

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

 

[Richard]

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

 

[Dave Peterson]

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.

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

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

 

[Charlie]

If you actullay named the sub "Second", i.e. it was not just part of your
example, try using a different name because "Second" is already in use as an
intrinsic function.

 

[Richard]

Dave

thanks for the help, I'm sorted now.

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.

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

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


:

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