Copying Some Elements from One Array to Another (1 Viewer)

[wilderfan]

I am trying to copy the contents of an array [sNames] into another one [sNames2], but only if the element is NOT EQUAL to " ".

My current code is:

====================================
Dim i, j As Integer
j = 0
For i = LBound(sNames) To UBound(sNames)
If sNames(i) <> " " Then
sNames2(j) = sNames(i)
j = j + 1
End If
Next i
====================================

But I'm getting an error:

Run-time error '9':
Subscript out of range


I know there must be a simple fix, but I'm not sure what I'm doing wrong.

Any suggestions?

 

[MarkK]

So it's acceptable that if sNames(i) = "" it gets copied? What about multiple spaces?
Maybe you only want to copy if Trim(sNames(i)) <> "" something.
And where have you declared the second array sNames2()? If it is not big enough, which is probably the case, you need to ReDim it.
HTH,
Mark

 

[wilderfan]

I didn't include all the coding in my first post. Definitions were simply:

dim sNames() as String
dim sNames2() as String


The first array was populated by applying the Split Function to the contents of a form's textbox.

Due to multiple spacing between words keyed into the form's textbox, the first array definitely contains spaces. The delimiter I used with the Split function was " ". Unfortunately, when users insert multiple spaces between words, the Split function winds up placing some of the spaces into the first array.

 

[MarkK]

What you might consider is to remove the double spaces before you split the string, so something like...

Do While Instr(OriginalText, Space(2))
  OriginalText = Replace(OriginalText, Space(2), Space(1))
Loop

...which replaces all double spaces with single spaces. Then run your Split().

And your 'subscript out of range' occurs because your sNames2() array has no dimensions, so you can't assign a value to sNames2(0) because it doesn't exist. Check out the ReDim statement in VBA help.

HTH,
Mark

 

[gemma-the-husky]

what is the point of this, anyway. the second array ends up the same as the first, anyway. doesn't it?

 

[VilaRestal]

Obviously lagbolt is right (fix the data before the split function)

If it worked, the second array would end up the same as the first but with uninitialized elements in the second corresponding to the " " elements in the first - something that would then have to be addressed whenever dealing with the array in much the same way as dealing with a " " in elements would be.
It is not (would not be if it worked) solving anything.

For i = LBound(sNames) To UBound(sNames)
If sNames(i) = " " Then sNames(i) = ""
Next i

would be an easier way of achieving that dubious objective

 

[wilderfan]

The first array contains " " elements when a user inputs multiple spaces between words in the textbox of a form.

My objective was to create a 2nd array that contained only those string elements which were not empty spaces. The 2nd array would be similar to the 1st array, but it would not contain blank " " elements.

 

[gemma-the-husky]

but what is the point?

you are not "collapsing" the array to a fewer number of elements by doing this. You can easily ignore any array elements set to spaces anyway.

what are you trying to achieve?

 

[wilderfan]

My use of the Split function and arrays came about because I wanted to have a form with one unbound textbox to input names.

However, the table has a first name field and a last name field (Fname and Lname, respectively).

The Split function works perfectly when the user inputs only 2 names and inserts only 1 space between those 2 names.

But if the user accidentally or deliberately types multiple spaces between the 2 names, the number of array elements expanded beyond Array(0) and Array(1).

Then I got thinking about multi-part names. For example, Michael J. Fox or Dick Van Dyke. Or what about persons with only 1 name: Madonna, for example.

With these potential complications, I envisioned having a second array without any blank elements. Then, I was going to ask the user to specify how many elements should be saved in the Fname field, with the remainder falling into the Lname field.



By the way, the power supply unit on my home PC just "died". So my responses will be a bit sporadic for the next couple of days til I get the new PSU installed.

 

[VilaRestal]

To reiterate lagbolt

OriginalText = Replace(OriginalText, Space(2), Space(1))

before the Split function
you could do it multiple times to remove triple spaces or more:

Do While InStr(OriginalText,Space(2))
OriginalText = Replace(OriginalText, Space(2), Space(1))
Loop

And you won't need to remove any entries from the array (results of the Split)