Find size data from Item Title column to create a Size column

[frewert]

Originally the workbook only contained Column A and the intention is to create Column C (Size Column).

Here is my workbook so far.

Size project method A.xlsm

drive.google.com

Here is an image preview.

https://imgur.com/a/nOEYEbC

I have highlighted the ones that aren't working as intended and put a column of what the desired result should be.

You will notice I have columns F and G that are valid sizes and conversions.

My goals now:

• Item Titles with (Number)X(Number) should return the first number. E.g., 32X30 should return 32.
• Item Titles with M/L are returning nothing. It should return L (the largest size)
• Item Titles ending in 0,2,4,6,8,S,M,L are not returning a result. Something to do with not being surrounding by the space character.
• Item Titles with 6S, 6R, 6M, 6L, 6T should return 6. (Should work with all numbers, not just 6 in example)

Here is the vba code used. (credit: reddit.com/user/arkangelshadow007)

Sub shoe_sizes()

Dim items As Range, validSizes As Range, conversion As Range

Set validSizes = Range("f2:f200") 'adjust ranges
Set conversion = Range("g2:g200")
Set items = Range("a2:a4200")

For Each Item In items.Cells

    For Each validSize In validSizes.Cells

        x = InStr(Item.Value, validSize.Value)

        If x > 0 Then

            Cells(Item.Row, 3) = Cells(validSize.Row, 7)

            GoTo next_item

        End If

    Next validSize

next_item:

Next Item

End Sub

Thank you!

 

[Akuini]

You're welcome! Best of luck with your business ventures.