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).
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
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