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

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe this...
(try it in a copy of your workbook)

Sub aTest()
    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
    
    'manipulate column values
    With tbl.ListColumns("Symptom Code").DataBodyRange
        .Value = Evaluate("=IFERROR(VALUE(RIGHT(" & .Address & ",3)),0)")
    End With
    
    'just to check...
    MsgBox "Done!"
    
    'other stuff to be done
    
    'paste original column contents back to ListColumn
    tbl.ListColumns("Symptom Code").DataBodyRange.Value = myarray
End Sub

Hope this helps

M.

 

[samjsteffes]

Worked like a charm! Not what I was expecting the solution would be, but it works and avoids looping.

Thanks, Marcelo!!!!

Welcome to Mr Excel forum

Maybe this...
(try it in a copy of your workbook)

Sub aTest()
    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
    
    'manipulate column values
    With tbl.ListColumns("Symptom Code").DataBodyRange
        .Value = Evaluate("=IFERROR(VALUE(RIGHT(" & .Address & ",3)),0)")
    End With
    
    'just to check...
    MsgBox "Done!"
    
    'other stuff to be done
    
    'paste original column contents back to ListColumn
    tbl.ListColumns("Symptom Code").DataBodyRange.Value = myarray
End Sub

Hope this helps

M.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[Rick Rothstein]

Maybe this...
(try it in a copy of your workbook)

Sub aTest()
    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
    
    'manipulate column values
    With tbl.ListColumns("Symptom Code").DataBodyRange
        [B][COLOR="#FF0000"].Value = Evaluate("=IFERROR(VALUE(RIGHT(" & .Address & ",3)),0)")[/COLOR][/B]
    End With
    
    'just to check...
    MsgBox "Done!"
    
    'other stuff to be done
    
    'paste original column contents back to ListColumn
    tbl.ListColumns("Symptom Code").DataBodyRange.Value = myarray
End Sub

The OP said he wanted only the numbers that were three digits long... since the code has a dash in front of the numbers, the red highlighted code line will not return zero for codes with two digits because it will interpret the dash as a minus sign and return the two digit numbers as minus values (for example, aa-12 will be returned as -12 not 0). I think changing the red highlighted line of code to this will solve the problem...

[table="width: 500"]
[tr]
	[td].Value = Evaluate("=IFERROR(VALUE(RIGHT(SUBSTITUTE(" & .Address & ",""-"",""X""),3)),0)")[/td]
[/tr]
[/table]

 

[Marcelo Branco]

Rick,

I hadn't noticed that a dash could be present in alphanumeric code .
Thanks for pointing out and suggesting a more robust code.

M.
ps: i had seen the formula showed in post 1
IFERROR(VALUE(RIGHT(Workbank[Symptom Codes],3)),0)
and i didn't take into account the dashes

 

[Rick Rothstein]

Worked like a charm! Not what I was expecting the solution would be, but it works and avoids looping.

Please see Message #5 .

 

[Marcelo Branco]

Please see Message #5 .

Rick,

Have you noticed the formula in post 1?
IFERROR(VALUE(RIGHT(Workbank[Symptom Codes],3)),0)
This is what led me to error.

M.

 

[samjsteffes]

Rick,

Thanks for the improvement! As you may have guessed, I needed to temporarily change the column values to numeric format so that I could extract counts between a specific range (i.e. between 200 and 299). As it turns out, values of the format '-##' resulting in negative values instead of zero does not impact the ranges I sort by; however, your suggestion does accomplish specifically what I asked for and makes the code more flexible.

Really appreciate both of your efforts.

Cheers,
SJS

 

[samjsteffes]

Hi Marco & Rick,

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 tried activating the worksheet that the table is on prior to the ".value = Evaluate..." line, and also tried using the similarly named .AddressLocal property which I thought sounded promising. Neither of these have worked. Is there a better way/any way to specify which worksheet the .Address property should reference when collecting values?

Apologies if my explanation is not clear. This is the best way I can think to describe the issue.

Thanks,
sjs