Function Help for UDF needed

DougStroud

Well-known Member
Here is a block of code from a UDF that was provided to me-
This looks in a table to validate size values. But what I would like to do is change the condition slightly to validate it in column 1 and use the value adjacent in column 2.
Here is the original block of code:

Code:
``````Else
'Check text sizes
Set c = Range("Sizes").Find(tmp(x))
If Not c Is Nothing Then
LVS = tmp(x)
Else
LVS = ""
End If``````

I was thinking that adding a .Resize(,2) would accomplish this, but it fails.

Code:
``````Else
'Check text sizes
Set c = Range("Sizes").Find(tmp(x))
If Not c Is Nothing Then
LVS = tmp(x).Resize(,2)
Else
LVS = ""
End If``````

Thanks,

Doug

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jindon

MrExcel MVP
Not sure
Is this what you mean?
Code:
``````Dim myRng As Range

Else
'Check text sizes
Set c = Range("Sizes").Find(tmp(x))
If Not c Is Nothing Then
Set myRng = c.Resize(,2)
Else
LVS = ""
End If``````

It is hard to say anything without knowing wha tyou are trying to do...
BTW how big is the "size" ?

DougStroud

Well-known Member
Thanks Jindon,
Here is the entire Function: (What the Function is Doing).
The function is looking at a text string and extracting the Last Value, a valid Last Value. Which is the size of the item, and this can be either a numerical or a text value. It could be a whole number, mixed number or fractional number. The function is used because the text string in a lot of cases does not have a size and it evaluates this too.
When you ask how big the "size" is, I do not know what you mean. But that may be because you do not know what I am doing,
It is looking in a size table, a range named "Sizes", for valid values. But I have some where they are valid by nature, but want to replace them, i.e. Large, Lrg, L would be replaced w/ Lg. I have all the valid values in a table w/ a proper size adjacent to it.
If the function is incapable of handling this, I can always do a find and replace, but trying to avoid adding one more process to this.

Code:
``````Function LVS(Data As Range)
Dim tmp, Siz, x As Long, c As Range
tmp = Split(Data)
x = UBound(tmp)
If InStr(1, tmp(x), "#") > 0 Or InStr(1, tmp(x), "-") > 0 Then
LVS = ""
Exit Function
End If
If IsNumeric(Right(tmp(x), 1)) Then
'Number sizes
Siz = tmp(x)
If InStr(Siz, "/") > 0 And IsNumeric(Right(Siz, 1)) Then
If IsNumeric(tmp(x - 1)) Then
Siz = tmp(x - 1) & " " & tmp(x)
End If
End If
LVS = Siz
Else
'Check text sizes
Set c = Range("Sizes").Find(tmp(x))
If Not c Is Nothing Then
LVS = tmp(x)
Else
LVS = ""
End If
End If
If UCase(Right(tmp(x), 2)) = "CM" Then
LVS = tmp(x)
End If
End Function

If the function is incapable of``````

The original developer of the function is on vacation for another week and I just discovered this, and as I wrote previously I am hoping to finish this in the next day or two.

jindon

MrExcel MVP
Doug,

If you don't mind, can you post the sample of the "text" and tell us what you want to do with it and the result how you want?

DougStroud

Well-known Member
Hello Jindon,
Sure-- The following table contains two columns, left column is the valid data that the Function compares and populates my sheet. I would rather it validate it and take the value to the adjacent cell to the right of it to standardize my size values. Like a Vlookup, but use the Function above. This function is splitting the data from the second table below and compares it to the table and then places it in the sheet.
MasterImportSheetWebStore.xls
ABCD
498LLg
499L/Xl
500LARGELg
501LGLg
502Lg/Xl
503LRGLg
504MMd
505MDMd
506MEDMd
507MEDIUMMd
508SSm
509S/MSm/Md
510SMSm
511Sm/MdSm/Md
512SMALLSm
513SMLSm
514XLXl
515XlargeXl
516XLGXl
517XLRGXl
518XSXs
519XsmlXs
520XXL2x
521XXSXXS
Table

jindon

MrExcel MVP
Doug,

Sorry, but I can not find any relation to your sheet with the function posted.

Teh function
1) slipt the text in the cell by "sapce"
2) compare if the last element includes "#" or "-", and if there is Exit
3) if the last element is Numeric....

then if not, the function goes to find the last element if the named range "Size".

I don't think this is the right way, because even if the function is working OK, it will not respond any changes made to "Size".

Again, I can not find the relation with this function to the tables above.

DougStroud

Well-known Member
Hi Jindon,
I am not sure if what I will write here will help explain it any further, but will try. You seem to have an accurate understanding of the function. So it may be that I fail to understand, but I will try to add a few more details here to help clear up for either one of us.
If I add a value to the table in the left column, which is actually a named range- "Sizes" and "Sizes" is actually a List, this value becomes a valid term the function will validate and keep in my second table in Col. M
If the last term in any cell in Col. D is anything other than numeric or a valid term, it does not place the value in Col. M.
For example, D71 has a last value of "Gunmetal", it does not meet the function's criteria and so M71 is left blank. (This I believe you already know, but just for assuredness sake).
But what I want the function to do is if it finds a valid value and is non-numeric, I want it to compare the values in "Sizes" List and swap out the value- So "S" which is a valid size in "Sizes" would be swapped w/ "Sm", "Large" would become "Lg", etc... Again- I think you understand this-
So if I do make a change to the named range "Sizes" and add a value it will add another valid value, just not look up, compare and swap.

If the function will not handle the additional function by the design of the function and cannot be edited to perform what I require or it can not handle it by the nature of what function are capable, I will add a vlookup or Find/Replace.

Thanks for assisting- sorry I had a long day and fell asleep at the keyboard--

Cheers,

Doug

Replies
13
Views
3K
Replies
1
Views
612
Replies
2
Views
119
Replies
5
Views
329
Replies
3
Views
241

1,181,728
Messages
5,931,696
Members
436,798
Latest member
spprtpplcm

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.

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

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