Varriable array size

I have the following code, and what it is doing is going through a spreadsheet in excel and splitting up the string that is dumped out of Novell. What it does is for example it splits "username.context.tree" into three parts. This works fine except when it comes to things like "username.subcontext.context.tree" or "username.tree". So how would i go about making choices and giving me the right bits in the places??

VB Code:

Option Explicit
Function mySplit(strToSplt, strSplitOn) 'Split Function
    Dim ip1, ip2
    Dim strArray()
    Dim iCount
    
    
    ip1 = 1: ip2 = 1
    Do
      ip2 = InStr(ip1, strToSplt, strSplitOn)
      If ip2 = 0 Then
        ip2 = Len(strToSplt) + 1
      End If
      
      If iCount Mod 100 = 0 Then
        ReDim Preserve strArray(iCount + 100)
      End If
      strArray(iCount) = Mid$(strToSplt, ip1, ip2 - ip1)
      
      ip1 = ip2 + Len(strSplitOn)
      iCount = iCount + 1
      
    Loop Until ip2 >= Len(strToSplt)
    
    ReDim Preserve strArray(iCount - 1)
    mySplit = strArray
    
End Function
 
Sub test()
Dim rw
Dim intRow As Long
Dim Mystr, strParams, strUserName, strContext, strSubContext, strTree As String
 
intRow = 1
For Each rw In Worksheets("allusers").Cells(1, 1).CurrentRegion.Rows 'Do this for every row in excel
    intRow = intRow 'get the row number (Im not really sure how this works but it does further down
    Mystr = rw.Cells(1, 1).Value 'Get the string to split up
    strParams = mySplit(Mystr, ".") 'Send string tho be split
    If isArray(strParams) Then 'assign the split string to the right variables
        strUserName = Trim(strParams(0))
        strSubContext = Trim(strParams(1))
        strContext = Trim(strParams(2))
        strTree = Trim(strParams(3))
    End If
    rw.Range("C" & intRow).Value = strUserName 'put the username in cell D and the row
    rw.Range("D" & intRow).Value = strSubContext 'put the context1 if it exsits in cell D and the row
    rw.Range("E" & intRow).Value = strContext 'put the context 2 in cell D and the row
    rw.Range("F" & intRow).Value = strTree 'put the tree in cell E and the row
 
Next rw
 
End Sub

Hope you see what I am getting at..

Let's change your Sub a little bit:

VB Code:

Sub test()
    Dim rw
    Dim intRow As Long
    Dim Mystr, strParams(), strUserName, strContext, strSubContext, strTree As String
 
    intRow = 1
    For Each rw In Worksheets("allusers").Cells(1, 1).CurrentRegion.Rows 'Do this for every row in excel
        intRow = intRow 'get the row number (Im not really sure how this works but it does further down
        Mystr = rw.Cells(1, 1).Value 'Get the string to split up
        strParams = mySplit(Mystr, ".") 'Send string tho be split
        If isArray(strParams) Then 'assign the split string to the right variables
            strUserName = Trim(strParams(0))
        End If
        If UBound(strParams) = 1 Then
            strTree = Trim(strParams(1))
        ElseIf UBound(strParams) = 2 Then
            strContext = Trim(strParams(1))
            strTree = Trim(strParams(2))
        ElseIf UBound(strParams) = 3 Then
            strSubContext = Trim(strParams(1))
            strContext = Trim(strParams(2))
            strTree = Trim(strParams(3))
        End If
        rw.Range("C" & intRow).Value = strUserName 'put the username in cell D and the row
        rw.Range("D" & intRow).Value = strSubContext 'put the context1 if it exsits in cell D and the row
        rw.Range("E" & intRow).Value = strContext 'put the context 2 in cell D and the row
        rw.Range("F" & intRow).Value = strTree 'put the tree in cell E and the row
    Next rw
End Sub

Does this work?

I have come up with the following all i had to do was use the ubound thing. But I still don't know what is going on with the following line in the following code.

VB Code:

'what is this intRow needed for is there something else
'I can stick in this line??
rw.Range("C" & intRow).Value = strUserName

VB Code:

Option Explicit
Function mySplit(strToSplt, strSplitOn)
    Dim ip1, ip2
    Dim strArray()
    Dim iCount
    
    ip1 = 1: ip2 = 1
    Do
      ip2 = InStr(ip1, strToSplt, strSplitOn)
      If ip2 = 0 Then
        ip2 = Len(strToSplt) + 1
      End If
      
      If iCount Mod 100 = 0 Then
        ReDim Preserve strArray(iCount + 100)
      End If
      strArray(iCount) = Mid$(strToSplt, ip1, ip2 - ip1)
      
      ip1 = ip2 + Len(strSplitOn)
      iCount = iCount + 1
      
    Loop Until ip2 >= Len(strToSplt)
    
    ReDim Preserve strArray(iCount - 1)
    mySplit = strArray
    
End Function
 
Sub test()
Dim intRow, intThisOne As Integer
Dim rw As Variant
Dim MyStr, strParams, strUserName, strSubContext, strContext, strTree As String
intRow = 1
 
'Do this for every row in worksheet
For Each rw In Worksheets("allusers").Cells(1, 1).CurrentRegion.Rows 
    MyStr = rw.Cells(1, 1).Value    'Get the string to split up
    strParams = mySplit(MyStr, ".") 'Send string tho be split
    If UBound(strParams) = 2 Then intThisOne = 1 'username.context.tree
    If UBound(strParams) = 1 Then intThisOne = 2 'username.tree
    If UBound(strParams) = 3 Then intThisOne = 3 'username.subcontext.context.tree
    
    Select Case intThisOne
        Case 1 'username.context.tree
            strUserName = Trim(strParams(0))
            strSubContext = ""
            strContext = Trim(strParams(1))
            strTree = Trim(strParams(2))
       
        Case 2 'username.tree
            strUserName = Trim(strParams(0))
            strSubContext = ""
            strContext = ""
            strTree = Trim(strParams(1))
                    
        Case 3 'username.subcontext.context.tree
            strUserName = Trim(strParams(0))
            strSubContext = Trim(strParams(1))
            strContext = Trim(strParams(2))
            strTree = Trim(strParams(3))
                
    End Select
    
    If strUserName <> "" Then
        rw.Range("C" & intRow).Value = strUserName      'put the username in column D and the row
        rw.Range("D" & intRow).Value = strSubContext    'put the subcontext  in column D and the row
        rw.Range("E" & intRow).Value = strContext       'put the context in column D and the row
        rw.Range("F" & intRow).Value = strTree          'put the tree in column E and the row
    End If
    
Next rw
 
End Sub

It does work, thank you. I did the same thing just a bit more long winded though. I am fairly new to programing vb/vba/vbscript so I guess I will learn to do things more quickly as I go along.

I want to get a hold of vb.net so i can make my asp pages into asp.net pages more easily.

EDIT: But I still don't know how to go about the following line because obviously it can be shortened.

VB Code:

rw.Range("C" & intRow).Value = strUserName

First of all, that "intRow" is a part of YOUR code. I didn't add it.

Second, it seems to always point to a alue of 1. Therefore, I'm assuming that you can replace intRow with "1":

"C1"

Never mind, then.

That works, and i know i put intRow in I thought I had to obviosly not.

I think it also relates to the right cell because of the for each statement. so it is only looking at the the one row at a time so even though it really is D155 it sees that row as A1, B1 C1 etc.