add dimension to array in vba

I have created an array by using the split function.

ClientWords = Split(Trim(txt))

I would like to do a redim preserve to add an extra element so I can mark a specific word in the array.

How do i do that

I tried ReDim Preserve ClientWords(UBound(ClientWords),0)

but i get subscript out of range
PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncanb7Commented:
Is it what you want for redim ?

Hope understand your question.If not, pls pt it out

Duncan
Sub Test()
Dim ClientWords() As String
ClientWords() = Split("How are you")
'Debug.Print ClientWords(1)
ReDim Preserve ClientWords(Application.CountA(ClientWords))
ClientWords(Application.CountA(ClientWords) - 1) = "Today"
Debug.Print ClientWords(3)
End Sub

Open in new window

0
PeterBaileyUkAuthor Commented:
vba gives compile error method or data member not found on counta

I have
207
s16
1.6
[120]

I want it to go through a record set and if it finds the current word clientwords(1) s16 in this case it will tag 16 so i can use that later.
0
PeterBaileyUkAuthor Commented:
heres my code so far its just a question of tagging i thought adding an extra element would allow that

    ClientWords = Split(Trim(txt))
   
    
    For x = LBound(ClientWords) To UBound(ClientWords) Step 1

        With rstWordExceptions
    
            .MoveFirst
    
            'cycle through the query grabbing strings
             Do Until .EOF
                    If ClientWords(x) = rstWordExceptions.Fields("sdesc").Value Then
            
            
            
                    Else
            
            
            
                    End If
        
    
    
    
                    rstWordExceptions.MoveNext
            Loop

        End With


    Next x 'loop to next word in array

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

duncanb7Commented:
try this with UBound
Sub Test()
Dim ClientWords() As String
ClientWords() = Split("How are you")
Debug.Print UBound(ClientWords, 1)
ReDim Preserve ClientWords(UBound(ClientWords, 1) + 1)
Debug.Print UBound(ClientWords, 1)
ClientWords(UBound(ClientWords, 1)) = "Today"
Debug.Print ClientWords(UBound(ClientWords, 1))
End Sub

Open in new window

0
James ElliottManaging DirectorCommented:
See if this helps at all:

Sub SplitWithTag()

Dim arr As Variant
Dim arrTag As Variant
Dim x As Long

arr = Split(Range("A1"))

ReDim arrTag(UBound(arr), 1)

For x = LBound(arr) To UBound(arr)

    arrTag(x, 0) = arr(x)
    arrTag(x, 1) = "Your tag"

Next x

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
I must have said it wrong the split creates the array fine I need to add another dimension so I can flag them
0
PeterBaileyUkAuthor Commented:
our responses are crossing just seen 40219078
0
PeterBaileyUkAuthor Commented:
thank you
0
duncanb7Commented:
Dear Author,

Is the code matched to your question post?

Please advise

Duncan
0
Dale FyeCommented:
I don't believe you can use redim to change the number of dimensions in an array.

You could write your own code to do this with a 2 dimensional array

    Dim xWords() As String
    Dim ClientWords() As Variant

    xWords = Split("Your text string goes here")
    ReDim ClientWords(UBound(xWords), 1)
    For x = LBound(xWords) To UBound(xWords)
        ClientWords(x, 0) = xWords(x)
    Next
0
Dale FyeCommented:
Peter,

I'm confused.  You posted to an Access topic area, yet accepted an Excel solution?
0
duncanb7Commented:
Dear Author,
I would like to do a redim preserve to add an extra element so I can mark a specific word in the array

from your post , it seems you want add one more cell  into array.
but you want multi-dimension array,

Thanks for your reply

Duncan
0
James ElliottManaging DirectorCommented:
fyed,

what, aside from my testing my code in Excel and forgetting to take out the 'range' reference, makes it an "Excel" solution?
0
Dale FyeCommented:
jell,

nothing.  I focused in on "Range".  After I posted the comment I realized you had basically recommended the same thing that I did, use a second array.
0
James ElliottManaging DirectorCommented:
Maybe my interpretation of the question would help:

The OP wanted to be able to tag certain words in his one-dimensional array, created using the split function.

I provided a solution which copied the 1-d array to a 2-d array, so that he could place a tag (in the second dimension) next to each word if he wished to.
0
Dale FyeCommented:
Nope, Jell has a good working solution that works in Access VBA.
0
PeterBaileyUkAuthor Commented:
It seems you all answered your own things, the solution provided by Jell at that time was the best solution, I believe I am always fair when dishing out points (I think fyed would agree) if I have two that work then I usually share but in this case Jell understood perfectly the requirement. Hence the full points. In fact I had already posted the first part hence I ignored the "range" it was the addition of the second array that completed the task.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.