Seems Like Weird Messup With Array Variable 3

[kjv1611]

I am attempting to "convert" stings containing SSNs from one format to another.

From:
123-45-6789
To:
123456789

So, along those lines, as part of a larger VBA procedure, I have a loop that is finding those cells that have a SSN in the first format, and I am attempting (using an array and the SPLIT function)to take out the dashes. But for whatever reason, I keep getting an error message when I first start with the array. I've also tried a couple different methods for looking at the data. I'll detail the code and error message below:

    Dim lngRow As Long
    Dim strFormulaArray() As String
    Dim intArray As Integer 'Count of array
    Dim strNewFormula As String
    For lngRow = tr To br
        If InStr(Cells(lngRow, 8), "SSN") Then
            If InStr(Cells(lngRow, 3), "-") Then
                strForumlaArray = Split(Range("C" & lngRow).Formula, "-")
                For intArray = LBound(strFormulaArray) To UBound(strFormulaArray)
                    If intArray = 0 Then
                        strNewFormula = strFormulaArray(0)
                    Else
                        strNewFormula = strNewFormula & strFormulaArray(intArray)
                    End If
                Next intArray
                Cells(lngRow, 3).Formula = strNewFormula
            End If
        End If
    Next lngRow

I tried Split(Cells(lngRow, 3).Formula, "-") as well as the current method of looking at the cell as a Range.

Here is the actual error message I keep receiving:

Run-time error '9':

Subscript out of range

At first I was trying For intArray = 0 to UBound(...) but neither change there seems to make a difference.

The error code comes up on this line of code:
For intArray = LBound(strFormulaArray) To UBound(strFormulaArray)

If I hover over the variables,LBound(..) and UBound(...), VBA says "variable = <subscript out of range>"

Any thoughts/suggestions?

Oh, and I did double check. The string where this is occuring is the first SSN text with the format 123-45-6789

Thanks in advance for any advice/suggestions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[sugarflux]

Hi kjv

Try replacing the
Dim strFormulaArray() AS string
with
Dim strFormulaArray() AS variant

also you will probably have to declare the upper bound of your array before assigning values. For example:

Dim strFormulaArray(10) as Variant ' For 10 cells

This can be a problem if you don't know how many you want to check so you can leave the array blank at the top and then in your code use

Redim Preserve strFormulaArray(intArray+1)

This redefines the array upper bound to one more each loop. The preserve keyword keeps your data instead of emptying the array. Hope this helps - let me know if i've rambled and confused ya!

sugarflux

 

[kjv1611]

sugarflux,

Thanks for the advice. However, I don't think this is the problem. But just to be sure I did test changing to a Variant AND specifying the size for the array variable (I just put 2, as I don't think I'd need anything more than 0,1,2 for array parts in this example). With both or either of the suggestions, I get the same problem, as that didn't change anything. The only change I DID get was this:

When I specified the size of the Array, it would go past the error-ridden step, and start the loop. However, it just shows the value for each part of the array as empty!

So, why is it not taken the string data from the cell?!

--

"If to err is human, then I must be some kind of human!" -Me

 

[Andrzejek]

One line of:

Option Explicit

at the begining of your code would fix your problem.

Dim strFormulaArray() As String
...
[red]strForumlaArray[/red] = Split(Range("C" & lngRow).Formula, "-")
For intArray = LBound(strFormulaArray) To UBound(strFormulaArray)

Check the spelling of [red]strForumlaArray [/red] and strFormulaArray



Have fun.

---- Andy

 

[kjv1611]

Okay, now we're getting somewhere!

Andrzejek,

When I put in the Option Explicit part at the top of the module, and recompiled, I now get the error messaage:

Compile error: Variable not defined

and it highlights strFormulaArray!

Eureka! I did a typo and switched 2 letters! I had looked and looked and looked at those two instances, and still just did not see it! I guess it's just too early on Monday morning!

Thanks, Andrzejek! I can now run the code with no errors once again!

--

"If to err is human, then I must be some kind of human!" -Me

 

[strongm]

And from now on you'll remember to have Option Explicit set, yes? Best thing to do is, in the VB editor, Tools/Options/Editor and tick 'Require Variable Declaration'. It'll save you a bunch of heartache ...

 

[kjv1611]

Good point, strongm! I have this set this way in Access, but just never thought to do it in Excel VBA. So, yeah, I guess I've just worked it out before with no issues until today, so it's definitely a lesson learned!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Anyway, why not simply this ?
...
If InStr(Cells(lngRow, 8), "SSN") Then
Cells(lngRow, 3) = Replace(Cells(lngRow, 3), "-", "")
End If
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Well, my goodness! I didn't even think of that! That would use less memory in many ways, so it should greatly speed things up!

That's a keeper for sure! Thanks, PHV!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And I SHOULD have thought about that one, b/c I do that manually ALL the time, when I want to make mass changes, including just taking something out!

So, to me: DUH DUH DUH DUH DUH!

[WINK]

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

kjv,

Not to throw a fly in the ointment, or any thing like that, but...

there's this issue that I often run into, of NUMBERS versus IDENTIFIERS.

Numbers are often used as identifiers (hence we get masks in Access for phone, ssn, zips, etc)

And in the days of expensive core memory, "we" used to take all kinds of abbreviations to SAVE memory and storage.

But, hey, this is the twenty first century, the age where we argue not the question of how many angels can dance on the head of a pin, but how many gigs can fit thereon.

Gigs not gigs!

So back to NUMBERS versus IDENTIFIERS. Not so much in the Access world, but in the Shreadsheet world, people can enter
[tt]
999-99-9999
or
999999999
[/tt]
in a column FORMATTED for a SS-NUMBER. So you get this MIXTURE of NUMBERS and STRINGS -- very confusing when it comes to sorting and querying and parsing etc. Then there's the issue of leading zeros, a whole nuther aspect.

So as a matter of practice, shouldn't IDENTIFIERS be strings to begin with? Hope that this is not rain on your parade, but it's sunny in Fort Worth.

Skip,

_[red][/red]

 

[kjv1611]

Skip,

Was this meant to go in the other thread about the SSN format and such?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Yes, sorry.

Skip,

_[red][/red]

 

[kjv1611]

No problem. Just wanted to make sure. [LOL]

--

"If to err is human, then I must be some kind of human!" -Me