Adding Values Based On Matching Cells

[americantuna]

Hello,

I am trying to create a salary table to upload to a fantasy sports site. In order to do this quickly, I need to find a way (if it's possible) to automatically fill in values from one list to another.

In the image attached, I would like to compare Column C and Column J. If a name in C matches a name in J, I would like to copy the value of Column M to that name in Column F.

For example, in the image, Justin Abdelkader is matched in C7 and J1. I would like to take the value listed in M1 (the new salary) and have it automatically copy to cell F7 (overwriting the old salary).

**NOTE: At no time can the values in Columns A thru E be changed, otherwise the file cannot be uploaded.

I have a feeling I've done this a long time ago, but completely forgot how to do it. If this is possible, any help would be appreciated.

EDIT: These two tables are on the same sheet. I don't know if that matters, but if they need to be on separate sheets to make this work, let me know.

 

[igold]

Does this do what you want...

Sub test()
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

 

[americantuna]

Does this do what you want...

Sub test()
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

PERFECT!

I'm not a code guy, so all of that looks like gibberish to me. But I know how to input it and that's all that matters.

Thank you!

 

[igold]

You are welcome. I was happy to help. There is not much to the code. I put some comments in to give you an idea of what is happening...

Sub test()
    'Declare the variables
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    
    'Create two ranges that are column C as one range and column J as the second
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    'Using a nested loop, compare each value in column C to each value in column J
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                'If a match is found, make the value of the cell 3 columns to the
                'right of the cell in column C = to the value of the cell 3 columns
                'to the right of the matching cell in column J
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

 

[americantuna]

You are welcome. I was happy to help. There is not much to the code. I put some comments in to give you an idea of what is happening...

Sub test()
    'Declare the variables
    Dim c As Range, j As Range, rngC As Range, rngJ As Range
    
    'Create two ranges that are column C as one range and column J as the second
    Set rngC = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngJ = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    'Using a nested loop, compare each value in column C to each value in column J
    For Each c In rngC
        For Each j In rngJ
            If c.Value = j.Value Then
                'If a match is found, make the value of the cell 3 columns to the
                'right of the cell in column C = to the value of the cell 3 columns
                'to the right of the matching cell in column J
                c.Offset(0, 3).Value = j.Offset(0, 3).Value
            End If
        Next j
    Next c
End Sub

Thank you, again.

I keep a cheatsheet at my desk and put these types of things in it for future reference. Makes things so much easier.