Change value in one cell based on matching values a corresponding cell

[DonEB]

I've been trying to change the value in one cell based on matching values in different cells. I was successful in finding a solution for changing values from 2 to 1 in column A but I'm having difficulty in coming up with a solution for changing the values in D3:AG3. I appear to be having an issue dealing with columns.

In a worksheet called CommonData2, I have two ranges of data. One range is from AM6:AM25 and can contain from 4 to 20 different numbers (each number 1 - 30 reflecting a different player). The second range is from D3:AG35 but the focus of this issue revolves around the data found in D3:AG4.

Below is an excerpt from the worksheet which reflects what might be found in AM6:AM25 and the upper portion of what is found in D3:AG35.

Here is what I am attempting to do.
I look at each number located in AM6:AM25 and if found in D4:AG4 then I change the corresponding value found directly above it in D3:AG3 from 2 to 1. In the example below, AM6 contains the number 8. When looking thru D4:AG4, you will find the number 8 in K4. As a result, K3 should get changed from 2 to 1 and so on.

Any help or direction would be appreciated.

Thanks,
Don

 

[DonEB]

If I may... I should have provided the following code within my first request to reflect that I have attempted to develop the code myself but have failed in obtaining the results I'm looking for. The following code successfully does the following... it just doesn't do what I need:

1. Locates the value found in the AM6:AM25 range.
2. Compares the value found to the value located in row 4, starting with D4 thru AG4.
3. It replaces the value found with the number 1.
4. My problem is that I need to change the value directly above the cell where there was a match to the number 1. And I am unable to determine how to accomplish this. Your assistance would be greatly appreciated.

Dim Player As Long
Dim LastRowa As Long
Dim lColumna As Long
Dim a As Long
Dim ca As Long
Dim i As Long
Dim ci As Long
LastRowa = Sheets("CommonData2").Range("AM" & Rows.Count).End(xlUp).Row
lColumna = Sheets("CommonData2").Range("D" & Columns.Count).End(xlToRight).Column

For i = 6 To LastRowa
Player = Sheets("CommonData2").Range("AM" & i).Value

For Each Column In Sheets("CommonData2").Range("D4:AG4").Columns
For Each cVal In Column.Cells
If cVal.Value = Player Then
cVal.Value = 1
End If
Next
Next

 

[DonEB]

Just wanted everyone to know that thru trial and error I came up with a solution that works. Can't say that it is the best solution so if someone has opinion on how to make it better, your expertise would be welcomed.

Sub testcolumn2()

Dim Player As Long ' Used to capture player number from column AM
Dim LastRow As Long ' Used to identify last row used in column AM
Dim i As Long
Dim counter As Integer

LastRow = Sheets("CommonData3").Range("AM" & Rows.Count).End(xlUp).Row

For i = 6 To LastRow
Player = Sheets("CommonData3").Range("AM" & i).Value

For counter = 4 To 33 ' counter goes from 4 to 33 because the chart being examined runs from column D4:AG4
If Worksheets("CommonData3").Cells(4, counter).Value = Player Then
Worksheets("CommonData3").Cells(3, counter).Value = 1
End If
Next counter
Next

End Sub