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

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
128
Office Version
  1. 2019
Platform
  1. Windows
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.

1673716744010.png


Any help or direction would be appreciated.

Thanks,
Don
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top