Private Sub Question

  • Thread starter Thread starter Richard
  • Start date Start date
R

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
 
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

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
 
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

Jacob Skaria said:
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
Click to expand...
Click to expand...
 
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.
 
Dave

thanks for the help, I'm sorted now.

Dave Peterson said:
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

Jacob Skaria said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top