[RESOLVED] Excel, how to find the cells value of a cell on an intersect.

[Fizziii]

I think this should be fairly simple. But lets say I have a named range in the middle of the sheet and if a cell in that range is changed, I want to run some code.

I have the Worksheet_Change event, and I set a variable = to the Intersect of the target and my named range.
Assuming the intersect is not nothing, I need to find the Cells(x,y) value of that cell in the named range. There should be a way to do that, right? I'm just not sure how to get the x,y for it.

Basically I have the same categories along the top and side and filling in one half of the intersections should be able to flip the other half automatically along the diagonal. So if in (Cells(x,y) x>y, then y,x needs to be the inverse of the value. I can get that, once I figure out how to get the cells reference. I suppose I can always find the overall row/column and adjust based on where the upper corner of the named range is, but I was hoping for more dynamic code.

Best regards,
Fizziii

[Zvoni]

Can you post your code of your procedure of what you have so far?

EDIT: You do know that the Change-Event has a Target-Argument?

Private Sub Worksheet_Change(ByVal Target As Range)
 
   Debug.Print Target.Column
   Debug.Print Target.Row
 
End Sub

[Fizziii]

Here's my code. The commented out lines are testing lines that aren't yielding the expected results.

"Season2012" is the range (D184:AI215). If I change the value in T199 (Row16, Column 17 in the named range), the results I'm getting for the debug.print lines is:
184, Actual: 199
8, Actual: 20
If I change the value in S200 (Row17, Column 16 in the named range), the results I'm getting for the debug.print lines is:
184, Actual: 200
8, Actual: 19

So the actual Row/Column numbers are coming out correctly, but when I use the Cells(Target), the Row/Column is coming out off. I think what it comes down to is I don't understand exactly what is getting returned by "Target" and how to reference it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim InX As Byte
Dim InY As Byte
Dim OutX As Byte
Dim OutY As Byte
Dim r As Range
If Target.Cells.Count <> 1 Then
Else
Set r = Range("Season2012")
    Set isect = Application.Intersect(Target, r)
    If isect Is Nothing Then
    Else
    'Test1 = r.Cells(Target)
    'Debug.Print r.Cells(Target).Row & ", Actual: " & Target.Row
    'Debug.Print r.Cells(Target).Column & ", Actual: " & Target.Column
    'Debug.Print Test1
    End If
End If
End Sub

As a follow up, the Column Number will change depending on the value input into the cell. Testing the upper left most cell in the Range (D184), the Column Value seems to come in at 3+the value of the cell. Once the cell passes 32, then the Row value gets bumped by 1 and it starts over at 3. So 32 yields 184 and 35, and 33 yields 185 and 4.

[Zvoni]

And what results are you expecting? Just to keep in your example.

EDIT: I'm off for today. tomorrow again.
btw: What i could find is the Item-Property for the Range-Object. Maybe have a look at it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim InX As Byte
Dim InY As Byte
Dim OutX As Byte
Dim OutY As Byte
Dim r As Range
Dim OffsetRow As Long
Dim OffsetCol As Long
 
If Target.Cells.Count <> 1 Then
Else
Set r = Range("Season2012")
    Set isect = Application.Intersect(Target, r)
    If isect Is Nothing Then
    Else
    
    OffsetRow = Target.Row - r.Row + 1
    OffsetCol = Target.Column - r.Column + 1
    
    Set Test1 = r.Item(OffsetRow, OffsetCol)
    Debug.Print Test1.Row & ", Actual: " & Target.Row
    Debug.Print Test1.Column & ", Actual: " & Target.Column
    Debug.Print Test1
    End If
End If
End Sub
'Result
12, Actual: 12
5, Actual: 5

[Fizziii]

If I change the value in D184 (which is my first cell in the Range), I was hoping to pull the Row and Column number within the range. So 1 and 1. For D185, I would hope to get 2 and 1 (row 2, column 1)... which I believe would be r.Cells(2,1). If I can pull the row/col numbers, then I can update a value in r.Cells(1,2). I have created a simple sample sheet with a named range, as well as the code, results I'm getting, and what I'd like to get.

TestWorksheet.zip

[Fizziii]

zvoni,

Thank you. I copied your code into the test worksheet I had uploaded and plugged in a value to G9. It gives me 9, Actual: 9, and 7, Actual 7.
The values I want are actually OffsetRow and OffsetCol, so I can workaround the issue using this. I was hoping there was a way to directly pull the values, but your lines 17 and 18 will allow me to use this code elsewhere. Thank you very much.

Best regards,
Fizziii

P.S. Here's my resulting code. I still want to tweak it so I can edit values either above or below the line, but based on the change event, if I'm not careful I'll create a terminal loop as each update would result in another update.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim InX As Byte
Dim InY As Byte
Dim sVal As String
Dim r As Range
Dim OffsetRow As Long
Dim OffsetCol As Long
 
sVal = Target.Value
If Target.Cells.Count <> 1 Then
Else
Set r = Range("Season2012")
    Set isect = Application.Intersect(Target, r)
    If isect Is Nothing Then 'Cell changed is not in this range
    Else 'Value changed in range
   
    InX = Target.Row - r.Row + 1 'Find Cell Row in range
    InY = Target.Column - r.Column + 1 'Find Cell Col in range
   
    If InX < InY Then
        If UCase(sVal) = "W" Then 'Lost Game
            r.Cells(InY, InX).Value = "L"
        ElseIf UCase(sVal) = "L" Then 'Won game
            r.Cells(InY, InX).Value = "W"
        ElseIf UCase(sVal) = "S" Then 'Series Split
            r.Cells(InY, InX).Value = "S"
        Else ' Error
        'Set Test1 = r.Item(OffsetRow, OffsetCol)
        'Debug.Print Test1.Row & ", Actual: " & Target.Row
        'Debug.Print Test1.Column & ", Actual: " & Target.Column
        'Debug.Print Test1
        End If
    End If
End If
End If
End Sub