Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
The vntx(0)
Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name CBOMoFa = vntX(2) ' Mother/Father TxtChild = vntX(3) ' of Child TxtIssued = vntX(4) ' DateIssue TxtServed = vntX(5) ' Date Served SplitMe = TxtFirstName & "," & TxtLastName End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
Insert a
MSGBOX(UBOUND(vntX)) to make sure all the parts are there. -- Gary''s Student - gsnu2007a " wrote: The vntx(0) Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name CBOMoFa = vntX(2) ' Mother/Father TxtChild = vntX(3) ' of Child TxtIssued = vntX(4) ' DateIssue TxtServed = vntX(5) ' Date Served SplitMe = TxtFirstName & "," & TxtLastName End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
On Nov 8, 3:08 pm, Gary''s Student
wrote: Insert a MSGBOX(UBOUND(vntX)) to make sure all the parts are there. -- Gary''s Student - gsnu2007a " wrote: The vntx(0) Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name CBOMoFa = vntX(2) ' Mother/Father TxtChild = vntX(3) ' of Child TxtIssued = vntX(4) ' DateIssue TxtServed = vntX(5) ' Date Served SplitMe = TxtFirstName & "," & TxtLastName End Function- Hide quoted text - - Show quoted text - it shows that they are all there (5) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
Maybe one of the last parts of the input string is not present (i.e.
'DateIssue' or 'Date Served'). Since you are only returning FirstName and LastName, you could use: Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name SplitMe = TxtFirstName & "," & TxtLastName End Function You should check Ubound(vntX) to be sure that it is at least 1, as Gary's Student mentioned. You should also declare all of your variables and use Option Explicit at the top of all code modules. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
The vntx(0)
Why did you write the above in your original post? Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name CBOMoFa = vntX(2) ' Mother/Father TxtChild = vntX(3) ' of Child TxtIssued = vntX(4) ' DateIssue TxtServed = vntX(5) ' Date Served SplitMe = TxtFirstName & "," & TxtLastName End Function There is a lot you are not telling us. All those variables you are assigning things to... Where are the Dim'med at? Do you have vntX Dim'med elsewhere? Are you actually making it through the function and dying elsewhere? For this latter question, put a breakpoint on the End Function line and run your code... do you make it to the breakpoint or not? If not, put a breakpoint on the Split statement line and then press F8 until your code dies... what line did it die on? Can you show us the formula (your function is a UDF, right?) you are using to call the function (in particular, I'm interested in the original text you are trying to split). We would also be interested in any other details about what you are doing that you think may impact your use of the function. Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting strings, error subscript out of range
On Nov 9, 2:34 am, "Rick Rothstein \(MVP - VB\)"
wrote: The vntx(0) Why did you write the above in your original post? Private Function SplitMe(strToSplit As String) Dim vntX As Variant vntX = Split(strToSplit, ",") TxtFirstName = vntX(0) ' FirstName TxtLastName = vntX(1) ' Last Name CBOMoFa = vntX(2) ' Mother/Father TxtChild = vntX(3) ' of Child TxtIssued = vntX(4) ' DateIssue TxtServed = vntX(5) ' Date Served SplitMe = TxtFirstName & "," & TxtLastName End Function There is a lot you are not telling us. All those variables you are assigning things to... Where are the Dim'med at? Do you have vntX Dim'med elsewhere? Are you actually making it through the function and dying elsewhere? For this latter question, put a breakpoint on the End Function line and run your code... do you make it to the breakpoint or not? If not, put a breakpoint on the Split statement line and then press F8 until your code dies... what line did it die on? Can you show us the formula (your function is a UDF, right?) you are using to call the function (in particular, I'm interested in the original text you are trying to split). We would also be interested in any other details about what you are doing that you think may impact your use of the function. Rick There is alot, i havent said, for instance this is the only split function and vntX that i have, these variables are assigned I have set the values to text boxes and a combobox Im still very new to "programming" so i really dont know any of the tricks, and my code is really bad looking, basically what i was trying to do with this, Private Sub CmdEdit_Click() Dim strNames As String Dim vntX as Variant If CBONames.Text = "" Then MsgBox "Select a Name Please" End If If CBONames.Text < "" Then strNames = GetInfo(CBONames.Text) End If End Sub Private Function GetInfo(MyTxtName As String) Dim Tester As String Dim Thistest As String ActiveSheet.Unprotect Password:="g45721" Thistest = Range("A5:AX4500").Find(LblName, LookIn:=xlValues).Offset(0, 25) Tester = SplitMe(Thistest) If MyTxtName = Tester Then GetInfo = Thistest End If Private Function SplitMe(strToSplit As String) Dim vntX As Variant Dim First As String Dim last As String Dim child As String Dim issed As String Dim served As String vntX = Split(strToSplit, ",") First = vntX(0) ' FirstName last = vntX(1) ' Last NameCBOMoFa = vntX(2) ' Mother/Father SplitMe = First & "," & last End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range (Error 9) | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |