Runtime Error 1004: Attempting to Mirror Cells

[shp025]

I am trying to create a spreadsheet in which you can input data into a database and for it to make the same changes in individual sheets. You should also be able to go to individual sheets and for the spreadsheet to automatically make changes in the database. Like a two way input and output. While googling for answers, I've seen it be called "Mirroring Cells".

So I've gotten individual pairs of cells to mirror, but it takes about 9 lines of code to achieve it.

If Target = wksData.Range("C1") ThenApplication.EnableEvents = False
wksC.Range("B9").Value = wksData.Range("C1")
Application.EnableEvents = True
ElseIf Target = wksC.Range("B9") Then
Application.EnableEvents = False
wksData.Range("C1").Value = wksC.Range("B9").Value
Application.EnableEvents = True
End If

I couldn't figure out a way to loop the code in part because the cell location in the database and in the individual spreadsheets are a little random. I need them to be in that specific format I am told because of readability and printability. So I thought I would use a lookup table that would help me pull up the corresponding cell location from the database to the sheet and vice versa.

However, I've been getting a Runtime Error on two things in my code.

One for "If Not Intersect(Target,wksData.Range("A1:E225")) is Nothing Then"
And for when I set the corresponding cell with the target cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Dim wksData As Worksheet, wksCurrent As Worksheet, wksLocRef As Worksheet


'Active sheet cell row and column
Dim irownum As Long, icolnum As Long


'Database sheet cell row and column
Dim drownum As Long, dcolnum As Long


'Reference sheet tab location cell row and column
Dim trownum As Long, tcolnum As Long, tsheetnum As Long


Set wksData = Worksheets("Database")
Set wksLocRef = Worksheets("Location Reference")
Set wksCurrent = Worksheets(ActiveSheet.Name)


irownum = Target.Row
icolnum = Target.Column


'If change in Database...
If Not Intersect(Target, wksData.Range("A1:E225")) Is Nothing Then
    
    'Place row and column numbers in cells from the Location Reference sheet
    Application.EnableEvents = False
    wksLocRef.Range("G3") = irownum
    wksLocRef.Range("G4") = icolnum
    Application.EnableEvents = True
    
    'Index and Match formula in the sheet will determine the corresponding cell location
    
    'Receive new values into macro
    trownum = wksLocRef.Range("G6").Value
    tcolnum = wksLocRef.Range("G7").Value
    tsheetnum = wksLocRef.Range("G8").Value
    
    'Set cells equal to each other
    Worksheets(tsheetnum).Range(Cells(tcolnum, trownum)).Value = Target.Value
    
Else
    Application.EnableEvents = False
    wksLocRef.Range("G12").Value = icolnum
    wksLocRef.Range("G13").Value = irownum
    Application.EnableEvents = True


    drownum = wksLocRef.Range("G15").Value
    dcolnum = wksLocRef.Range("G16").Value


    wksData.Range(Cells(drownum, dcolnum)).Value = Target.Value
    
End If
End Sub

I'm still new to VBA and I don't know what prompted these errors, if this is syntax or a fundamentally wrong way of going about the problem. I would appreciate any help and assistance. Thank you!

Experiment_LocationReference(1).xls

[Paul_Hossler]

Not 100% clear on what you're trying to do but

1. The VBA error is because you're trying to assign an Error value from the worksheet to a Double

Capture1.JPG




2. The worksheet error is (I think) because 3 and 1 do not exist in columns C and D

Capture.JPG

[shp025]

Not 100% clear on what you're trying to do but

Let me try and clarify what I want the sheet to do. When there's a change in one worksheet, then I want the sheet to look up a corresponding cell in the Location References sheet and then make the same change in the corresponding cell. I need to use this look up method because I cannot manage to do this individually cell by cell or with a loop because of the random relationship between one cell location to another.

1. The VBA error is because you're trying to assign an Error value from the worksheet to a Double

2. The worksheet error is (I think) because 3 and 1 do not exist in columns C and D

I'm sorry, I think I got the columns and rows mixed up and I tried fixing both the Index/Match function and the VBA so that nothing is #N/A anymore. However, even though I've done away with the error values, the worksheet still does not work.

Experiment_LocationReference(1).xls

[Paul_Hossler]

I was working on a better guess when you replied

look at this version and see if it's closer

[shp025]

This is exactly what I was thinking about. And it helps a lot that you were able to write the lookup in the VBA. However, I'm getting a mismatch error when I input on both the wksData and the Sheet# side.

        For i = LBound(vLocation, 1) To UBound(vLocation, 1)

Edit: So I pulled out what you wrote for subroutine Init and placed it at the top of the Workbook_SheetChange sub and got the above line to work. But now, I get a mismatch error on the following line on both ends.

If vLocation(i, 2) = iRowNum And vLocation(i, 1) = iColNum Then

Do you have any idea what might be causing it?

[Paul_Hossler]

I took some liberties with your formats, the database and cross reference tables seemed to have redundant and/or ambiguous data

I'm still not sure about some things: the cross ref table goes to 225 or something, but there is no place on the database sheet or the data sheets to put some data