PDA

View Full Version : Runtime Error 1004: Attempting to Mirror Cells



shp025
02-27-2017, 09:18 AM
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!

18498

Paul_Hossler
02-27-2017, 09:55 AM
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

18500




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

18499

shp025
02-27-2017, 10:28 AM
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.

18504

Paul_Hossler
02-27-2017, 10:40 AM
I was working on a better guess when you replied

look at this version and see if it's closer

shp025
02-27-2017, 11:02 AM
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
02-27-2017, 05:08 PM
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