| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Private Sub Question
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Question
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Question
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Question
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" 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Question
Dave
thanks for the help, I'm sorted now. "Dave Peterson" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Special Private Character Question | Excel Discussion (Misc queries) | |||
| Private sub Worksheet change question | Excel Programming | |||
| Question about private subs | Excel Programming | |||
| Private Textbox Exit Sub question... | Excel Worksheet Functions | |||
| Private Sub Workbook Open() - question | Excel Programming | |||
Linear Mode
