index/match two sheets each value in list but returns only last value match

[KDS14589]

I have 2 worksheets that I’m trying an index/match.

Sheet1 has a column of data (color hex #’s) starting in K2 and goes down (presently at 8 rows) (last row is defined as ‘ LRselected’)

Sheet3 has a 2 columns of data listing color names starting in A2 and corresponding Hex numbers in B2 and goes down (presently at 30282 rows) (last row is defined as ‘ Lastr3’)

My code for the last rows is

​

With Sheet1

Dim Lastr1 As Long

Lastr1 = .Range("C" & .Rows.Count).End(xlUp).Row 'presently at 2863



Dim LRselected As Long

LRselected = .Range("K" & .Rows.Count).End(xlUp).Row 'presently at 8

End With



With Sheet3

Dim Lastr3 As Long

Lastr3 = .Range("A" & .Rows.Count).End(xlUp).Row 'presently at 30282

End With

My experimental code for the index/match is

Dim CLL1 As Range

Dim r As Long

For r = 2 To LRselected



For Each CLL1 In Sheet1.Range("K2:K" & LRselected).Rows

If CLL1.Value <> "" Then

CLL1.Offset(0, 5).Value = WorksheetFunction.Index(Sheet3.Range("A2:A" & Lastr3), WorksheetFunction.Match(Sheet1.Cells(r, "K"), Sheet3.Range("B2:B" & Lastr3), 0))

End If

If CLL1.Value = "" Then

CLL1.Offset(0, 5).Value = ""

End If

Next

This code matches the hex number listed on Sheet1 in column “K” with Sheet3 column “B” and gives the Color Name from Sheet3 column “A” into column “P” on Sheet1 , but it gives the match from the last value in column “K” into all cells in column “P”.
I'm trying to get a match for EACH value listed in "K" but it returns only match for the last value in "K'.
Do I need to add/modify this code or use something totally different?

 

[Sam_D_Ben]

Quick question, where did you store the code I shared with you? Was it saved in an existing module or in a new one?

 

[KDS14589]

Quick question, where did you store the code I shared with you? Was it saved in an existing module or in a new one?

ordinally, I tried inside my code, with minor changes to get it working.
finally, I put it after my final end as another procedure then added a call procedure inside my original code

 

[Sam_D_Ben]

in a flash I tried your code but get a 'Run-Time error #9' 'Subscript out of range' I'll have more time to work on it

ordinally, I tried inside my code, with minor changes to get it working.
finally, I put it after my final end as another procedure then added a call procedure inside my original code

Lord Jesus Christ, You said You ran my code and got a 'Run-Time error #9 – Subscript out of range,' but You didn’t share the complete details of what happened.

 

[KDS14589]

Lord Jesus Christ, You said You ran my code and got a 'Run-Time error #9 – Subscript out of range,' but You didn’t share the complete details of what happened.

the program froze/stopped, i got it to run again when i commented out your code

 

[KDS14589]

Lord Jesus Christ, You said You ran my code and got a 'Run-Time error #9 – Subscript out of range,' but You didn’t share the complete details of what happened.

i commented out the call procedure and when i ran your code after the call was out it still error #9

 

[Sam_D_Ben]

Let me see, Are you working on Sheet3 named as "30k Colornames" and Sheet1 named as "New List"

 

[KDS14589]

Let me see, Are you working on Sheet3 named as "30k Colornames" and Sheet1 named as "New List"

both'
sheet3 is 30k Colornames i checked if info was correct and in correct columns -- all OK
sheet1 is New List and all is in correct cells and columns
i cheek all variables --all OK
I just cant get match to work
i still get #9 from your code

 

[KDS14589]

Let me see, Are you working on Sheet3 named as "30k Colornames" and Sheet1 named as "New List"

i have to leave.
I an old and in wheelchair, I can't drive in last 40 years, so depends on transportation service
I haave a Dr appioointment

 

[Sam_D_Ben]

both'
sheet3 is 30k Colornames i checked if info was correct and in correct columns -- all OK
sheet1 is New List and all is in correct cells and columns
i cheek all variables --all OK
I just cant get match to work
i still get #9 from your code

Ok, Please insert a new module and paste/run this code. Please dont mix your old code with this.

Sub MatchHex()

    Dim ws1 As Worksheet, ws3 As Worksheet
    Dim LRselected As Long, Lastr3 As Long
    Dim r As Long
    Dim hexToFind As String
    Dim matchRow As Variant
    
    Set ws1 = ThisWorkbook.Sheets("New List")
    Set ws3 = ThisWorkbook.Sheets("30k Colornames")
    
    LRselected = ws1.Cells(ws1.Rows.Count, "K").End(xlUp).Row
    Lastr3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row
    
    For r = 2 To LRselected
        hexToFind = Trim(UCase(ws1.Cells(r, "K").Value))

        If hexToFind <> "" Then
            matchRow = Application.Match(hexToFind, ws3.Range("B2:B" & Lastr3), 0)

            If Not IsError(matchRow) Then
                
                ws1.Cells(r, "P").Value = ws3.Cells(matchRow + 1, "A").Value
            Else
                ws1.Cells(r, "P").Value = "Not Found"
            End If
        Else
            ws1.Cells(r, "P").Value = ""
        End If
    Next r
    
End Sub

 

[KDS14589]

Ok, Please insert a new module and paste/run this code. Please dont mix your old code with this.

Sub MatchHex()

    Dim ws1 As Worksheet, ws3 As Worksheet
    Dim LRselected As Long, Lastr3 As Long
    Dim r As Long
    Dim hexToFind As String
    Dim matchRow As Variant
   
    Set ws1 = ThisWorkbook.Sheets("New List")
    Set ws3 = ThisWorkbook.Sheets("30k Colornames")
   
    LRselected = ws1.Cells(ws1.Rows.Count, "K").End(xlUp).Row
    Lastr3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row
   
    For r = 2 To LRselected
        hexToFind = Trim(UCase(ws1.Cells(r, "K").Value))

        If hexToFind <> "" Then
            matchRow = Application.Match(hexToFind, ws3.Range("B2:B" & Lastr3), 0)

            If Not IsError(matchRow) Then
               
                ws1.Cells(r, "P").Value = ws3.Cells(matchRow + 1, "A").Value
            Else
                ws1.Cells(r, "P").Value = "Not Found"
            End If
        Else
            ws1.Cells(r, "P").Value = ""
        End If
    Next r
   
End Sub

i tried but still #9, i tried to IM you but cant so here goes....

Sorry about leaving the other day for a doctor appointment.

When your old, living alone (no living relatives within 500 miles), handicap (from brain surgery), in a wheelchair and a former cross county trucker not allowed to driver for the last 40 years, that also depends on a transportation provider for wheelchairs, you live on the time schedule of others that can’t be changed at the last moment.

My doctor appointment was for test results they preformed recently and came out not what I was hoping for.

Because of this, my mental focus and schedule has changed for the present.

This excel workbook is just a learning program for me, so data process/sheets can be deleted, changed, or the timeline can be drawn-out without conciseness. I used it to pass my time and keep my mind sharp/active. At some future time, I’ll be able to focus more on it.

Presently I’m asking you to stop working on a solution to my excel problem.

I DEEPLY appreciate all the assistance you gave me.

Thanks again

Ken