Cell is equal to another cell until you write something in it

[Paul_Hossler]

I added a field that is paired with fields on both Sheet2 and Sheet3

The aryPair() rows has to be dimensioned to hold the pairs (was 6 not it's 8)

It holds Ranges so aryPair (7,1) and aryPair (7,2) have to be Set,

 
Set aryPair(7, 1) = ws1.Range("F10")
Set aryPair(7, 2) = ws2.Range("E12")

The pairs can be Set in any order in the aryPair array


Capture.JPG


It's necessary somehow to 'link' pairs of cells between sheets so that they can be updated

The Workbook_SheetChange event uses aryPair() pairs and the sheet that was changed (Sheet1 or Sheet2/Sheet3)) to see if something needs to be updated


I can do some code polishing that might make it a little more straight forward if you want
If you do, then please attach a SMALL realistic sample of real data

Option Explicit


Public aryPair(1 To 8, 1 To 2) As Range     '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


Public ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Public rName As Range, rTown As Range, rOrder As Range, rPart As Range


'called in Thisworkbook Open
Sub Init()
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    
    Set rName = ws1.Range("D3")     '   name
    Set rTown = ws1.Range("H3")     '   town
    Set rOrder = ws1.Range("D6")    '   order
    Set rPart = ws1.Range("H6")     '   part
    
    
    '------------------------------------------------------------ Sheet1 to Sheet2 / Sheet2 to Sheet1
    Set aryPair(1, 1) = rName
    Set aryPair(1, 2) = ws2.Range("E2")


    Set aryPair(2, 1) = rTown
    Set aryPair(2, 2) = ws2.Range("E5")


    Set aryPair(3, 1) = rOrder
    Set aryPair(3, 2) = ws2.Range("I2")
    
    Set aryPair(7, 1) = ws1.Range("F10")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Set aryPair(7, 2) = ws2.Range("E12")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
       
    
    
    '------------------------------------------------------------ Sheet1 to Sheet3 / Sheet3 to Sheet1
    Set aryPair(4, 1) = rPart
    Set aryPair(4, 2) = ws3.Range("F2")


    Set aryPair(5, 1) = rTown
    Set aryPair(5, 2) = ws3.Range("F5")


    Set aryPair(6, 1) = rOrder
    Set aryPair(6, 2) = ws3.Range("F7")


    Set aryPair(8, 1) = ws1.Range("F10")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Set aryPair(8, 2) = ws3.Range("J11")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


    Application.EnableEvents = True
    
End Sub

[hrzagi]

But how do you count it, what (7,1) and (7,2) mean in

Set aryPair(7, 1) = ws1.Range("F10") 
    Set aryPair(7, 2) = ws2.Range("E12")

How (7,2) refer to cell E12? E12 is column 6 and row 10
and what (1 to 8, 1 to 2) mean in

Public aryPair(1 To 8, 1 To 2) As Range

Is that some table that I dont see?
I think it would be much easier fot me to understand if I know how you count it.

[Paul_Hossler]

Read about arrays

http://www.snb-vba.eu/VBA_Arrays_en.html

That's what aryPair is



There's really no counting, except for knowing how many pairs of cells are linked (8 in the latest example)

If there are more than 8, then the dimensions for aryPair needs to be updated

The aryPair (x, y) have NOTHING to do with the cell location, address, row, column, or value

The 'x' is a pair counter (1 to 8), and the 'y' (1 to 2) is the two cells that are linked

The linked cells are Set (since aryPair is storing Ranges) so that the Worksheet_Change logic knows what to check

So the 7th pair entry links these two cells on Sheet1 and Sheet2

Set aryPair(7, 1) = ws1.Range("F10") 
Set aryPair(7, 2) = ws2.Range("E12")

[hrzagi]

Finally I get it and I managed to add more pairs Its not so complicated once I figured out how it works. Thank you very much for all your help and patience

[Paul_Hossler]

Glad you got it -- it's a little compliated because of the logic you wanted

There is a little bit that can be added to possibly simplify it a little more if you want

[hrzagi]

There is a little bit that can be added to possibly simplify it a little more if you want

If it is not broken dont fix it

[Paul_Hossler]

If it is not broken, don't fix it

Words to the wise

[hrzagi]

Code works fine 99% time but sometimes "I get error Run-time error 91
Object variable or With block not set " and when I go to debug the problem seems to be in this line of code

Function SameCell(r1 As Range, r2 As Range) As Boolean
    SameCell = False
    
    If r1.Parent.Name <> r2.Parent.Name Then Exit Function
    If r1.Address <> r2.Address Then Exit Function
    
    SameCell = True
End Function

The problem occur after I enter text in first cell of pair and then when I click on other sheet. And if I exit excell and run it again, everything works fine. Do you know what it could be?

[hrzagi]

Actually now I see that problem sometimes occur even if I dont write anything but just change sheets

[Paul_Hossler]

I could not get it to fail, but I'm not sure I was exactly copying your steps

Comment out or remove the marked line in the sub below and see if that fixes it

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim rCell As Range
    Dim i As Long


    Application.EnableEvents = False
    
    Select Case Sh.Name
        Case "Sheet2", "Sheet3"
            For i = LBound(aryPair, 1) To UBound(aryPair, 1)
                If Len(aryPair(i, 2).Value) = 0 Then
                    aryPair(i, 2).Value = aryPair(i, 1).Value
'***********                    Exit For  *********************************************
                End If
            Next i
        End Select
    
    Application.EnableEvents = True


End Sub

If that doesn't resolve the issue, then let me have all of the steps you do to get it to fail

[hrzagi]

Ok, I tried this and its working for now but I will test it for few days. Also if it fail I will try to figure out what have I done before it failed because for now its seems like it occur randomly.