Performing functions on array variables

samjsteffes

New Member
Joined
Feb 27, 2018
Messages
16
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).

Code:
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
 
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)")
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top