Phelony
07-07-2009, 02:26 AM
Hi Guys
I'm trying to fix a duplicated spreadsheet where users have been entering data into both, potentially for months.
We're working with the assumption that the original (sheet1 in the code below) is the correct and formal version, but have to import from the duplicate (sheet2 in the code below) any changes not reflected in the original.
The problem lies in the fact that the spreadsheet is not linear, in that there is not particular start or end date that could be used as a marker to determine where things went wrong as it is updated through a convoluted process. :banghead:
What I'm trying to do is write some code that can compare the two and take updates from sheet2 while preserving the existing data in sheet1. : pray2:
Sub seekanddestroy2()
'once the program has run and reached a predetermined ending point, it stops running
Do Until ActiveCell = Sheets("sheet1").Range("$A$10")
Loop
'if the active cell is in column G then go back to the first column
'of the next row
If ActiveCell.Address = Columns("G") Then
ActiveCell.Offset(1, -7).Select
Else
'if the active cell has the same content as the cell at the same address on
'sheet 2 then move to the next cell on the same row
If ActiveCell = Sheets("sheet2").ActiveCell.Address Then
ActiveCell.Offset(0, 1).Select
Else
'if the active cell does not have the same content as the same cell on
'sheet2 then copy across that data but add the date in and preserve the
'original entry
ActiveCell = ActiveCell & " - " & Chr(10) _
& Sheets("Sheet2").Range.ActiveCell.Address.Value & Chr(10) & Date & Chr(10)
Application.CutCopyMode = False
End If
End If
End Sub
I'm sure you've seen the activecell.address that I've used and winced as I'm sure I've got it wrong, but I don't know how else to get it to use the current cell location as a reference? :bug:
Basically, if anyone knows of a command or function that can be used to say:
Look at the cell on sheet X in the same location as the current active cell
I know it's an ugly piece of code, but if someone could give me a hand with finding the right function it would be greatly appreciated.:think:
Thanks
Phel x
I'm trying to fix a duplicated spreadsheet where users have been entering data into both, potentially for months.
We're working with the assumption that the original (sheet1 in the code below) is the correct and formal version, but have to import from the duplicate (sheet2 in the code below) any changes not reflected in the original.
The problem lies in the fact that the spreadsheet is not linear, in that there is not particular start or end date that could be used as a marker to determine where things went wrong as it is updated through a convoluted process. :banghead:
What I'm trying to do is write some code that can compare the two and take updates from sheet2 while preserving the existing data in sheet1. : pray2:
Sub seekanddestroy2()
'once the program has run and reached a predetermined ending point, it stops running
Do Until ActiveCell = Sheets("sheet1").Range("$A$10")
Loop
'if the active cell is in column G then go back to the first column
'of the next row
If ActiveCell.Address = Columns("G") Then
ActiveCell.Offset(1, -7).Select
Else
'if the active cell has the same content as the cell at the same address on
'sheet 2 then move to the next cell on the same row
If ActiveCell = Sheets("sheet2").ActiveCell.Address Then
ActiveCell.Offset(0, 1).Select
Else
'if the active cell does not have the same content as the same cell on
'sheet2 then copy across that data but add the date in and preserve the
'original entry
ActiveCell = ActiveCell & " - " & Chr(10) _
& Sheets("Sheet2").Range.ActiveCell.Address.Value & Chr(10) & Date & Chr(10)
Application.CutCopyMode = False
End If
End If
End Sub
I'm sure you've seen the activecell.address that I've used and winced as I'm sure I've got it wrong, but I don't know how else to get it to use the current cell location as a reference? :bug:
Basically, if anyone knows of a command or function that can be used to say:
Look at the cell on sheet X in the same location as the current active cell
I know it's an ugly piece of code, but if someone could give me a hand with finding the right function it would be greatly appreciated.:think:
Thanks
Phel x