Performing functions on array variables

[samjsteffes]

Hi guys,

First time posting, and hopefully an easy solution.

I have a table called "Workbank" which is 33 columns, but will have a variable number of rows. One of the columns is comprised of alphanumeric strings such as: aa-###, aaa-##, aaaa-#. I would like to store these column values to a temporary array (myarray), extract the numbers at the end of each entry and paste back into the column. After performing some other functions, I will eventually paste the original values back to the column.

I only want to extract the number from values with the aa-### format, i.e. those that end with a 3 digit number. For all others, where the rightmost 3 characters may could include a "dash" or letter, I want to return a 0.

If I were to perform this in a worksheet, I would use the following formula:

IFERROR(VALUE(RIGHT(Workbank[Symptom Codes],3)),0).

Sub test()


Dim myarray As Variant
Dim temparray As Variant
Dim tbl As ListObject


'define listObject
Set tbl = Worksheets("Workbank").ListObjects("Workbank")


'store ListColumn contents to array
myarray = tbl.ListColumns("Symptom Code").DataBodyRange.Value


'XXXX next line is resulting in error type: mismatch
'manipulate column values
    'determine if the last 3 characters are numbers; if so, return the numbers, otherwise return zero
temparray = WorksheetFunction.IfError(Evaluate(Right(myarray, 3)), 0)


'paste manipulated values back to ListColumn
tbl.ListColumns("Symptom Code").DataBodyRange.Value = temparray


'other stuff to be done


'paste original column contents back to ListColumn
tbl.ListColumns("Symptom Code").DataBodyRange.Value = myarray


End Sub

Is there a way to do this without looping through each value in the array. This would be ideal, since the number of rows could grow to be pretty large, which would increase the processing time.

Greatly appreciate any feedback!


- SJS

 

[Rick Rothstein]

I have a follow-up question to this...

I've gotten Marco's suggested code to work in one situation, but not in the following:

I've included the code lines in an "event" code (button click). The button is located on a different worksheet from the "Workbank" table, and it appears that the .Address property is using the value in the same cell address, but of the originating worksheet instead of the value from the table column.

I don't have time to setup your layout again, so this is just a guess (but I think it should work)... try changing the code line in question to this,

.Value = Evaluate("=IFERROR(VALUE(RIGHT('" & .Parent.Name & "'!" & .Address & ",3)),0)")

 

[samjsteffes]

Hi Rick,

Thanks for the quick reply. Through some research - but mostly dumb luck - I think I may have found a solution myself. I've modified as such:

.Value = Evaluate("=IFERROR(VALUE(RIGHT(" & .Address(External:=True) & ",3)),0)")

It seems that setting the optional "External" parameter allows it to look outside the local sheet reference. The default condition is (External:=False), in which case it just looks at the local sheet. I'm still curious why activating the desired sheet wouldn't do the trick, but based on past experiences I'm guessing that simply activating a sheet does not make it the "local" sheet for range object references.

I'll try your suggestion as well to see if it works the same.


Thanks again. Truly appreciate your help.

-sjs