I am looking to add data from an opc server into cell A3, everytime data
comes in I want the old data to drop down one.A4, A5, A6 and so on.

Hi Mike,

this worked for me when ever I entered a value into A3...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Range("A:A").Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4").Value = Range("A3").Value
Case Else
Set rgOldValues = Me.Range("A4:A" & iLastRow)
rgOldValues.Cut _
Destination:=Me.Range("A5:A" & iLastRow + 1)
Range("A4").Value = Range("A3").Value
End Select
Application.EnableEvents = True
End If
End Sub


Could be a horse of a different colour though when the change is from
an opc server.

To get the code in place...

1. Copy it
2. Right click the worksheet's sheet tab then select "View Code" from
the popup menu.
3. Paste the code into the worksheet's code module that appears.
4. Press Alt + F11 to get back to the worksheet.

Ken Johnson

Everything worked great. I truly appreciate that. Is there something i
could use that would let me do multiple columns. B3, C3, D3, etc?:)


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=546673

Hi Mike,

try this, which extends the effect from A3 up to G3. If that's not
enough columns then just edit the address "A3:G3" in the first line "If
Not Intersect(Range(Target.Address), Me.Range("A3:G3")) Is Nothing
Then" to suit your needs, eg changing that address to "A3:J3" will give
you 10 columns..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Me.Range("A3:G3")) _
Is Nothing Then
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
Set rgOldValues = Me.Range(Cells(Target.Row + 1, Target.Column), _
Cells(iLastRow, Target.Column))
rgOldValues.Cut _
Destination:=Me.Range(Cells(Target.Row + 2, Target.Column), _
Cells(iLastRow + 1, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If
End Sub
Ken Johnson.

Hi Mike,

two things..

1. There's a mistake in my last reply, which I have now fixed up.

2. To cover the possibility of the data reaching the bottom of the
sheet I have included a line that clears the bottom-most row of the
target column so that there is room for moving all the old data down
one row....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Me.Range("A3:G3")) _
Is Nothing Then
Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
Set rgOldValues = Me.Range(Cells(Target.Row + 1, Target.Column), _
Cells(iLastRow, Target.Column))
rgOldValues.Cut _
Destination:=Me.Range(Cells(Target.Row + 2, Target.Column), _
Cells(iLastRow + 1, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If
End Sub


Ken Johnson

Thanks Ken I appreciate all of your help! Everything works great.
Thanks again,
Mike


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=546673

You're welcome Mike, thanks for the feedback.

Ken Johnson

Ken,
Could I bother you one more time, I hate to pester but I'm looking
to have the code you wrote do a few other things....
I would like column A4 and down to look for duplicates and then put
a 1 in cell A2 if there is a duplicate, otherwise the value would be 0.

In column B cell 4 and down show the current date and time.
Also sometimes my data remains the same and when it does I still
need it to drop down through the cells when I ask it to.
I do appreciate your help, After this my project would be
complete..
Thanks again,
Mike


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=546673

Hi Mike,

I would like column A4 and down to look for duplicates and then put
a 1 in cell A2 if there is a duplicate, otherwise the value would be 0.

A worksheet function in A2 is the simplest way...

=IF(COUNTIF(A$5:A$65536,A$3)0,1,0)

(NB when you change to a future version of Excel with more than 65536
rows you will need to edit this formula - if that's important)

When I first tried the above formula the Cut method, used by the code
that moves the old values down, screwed up the function's address so
I have changed the code so that Cut is no longer used.


In column B cell 4 and down show the current date and time.

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then
Dim vaOldValues As Variant
Dim iLastRow As Long
iLastRow = Me.Cells(Range("A:A").Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Me.Cells(Range("A:A").Rows.Count, 1).Clear
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4").Value = Range("A3").Value
Range("B4").Value = Now
Case Else
vaOldValues = Me.Range("A4:B" & _
IIf(iLastRow = 4, 5, iLastRow))
Range("A5:B5").Resize(UBound(vaOldValues, 1), 2).Value = _
vaOldValues
Range("A4").Value = Range("A3").Value
Range("B4").Value = Now
End Select
Application.EnableEvents = True
End If
End Sub


Also sometimes my data remains the same and when it does I still
need it to drop down through the cells when I ask it to.

In my first reply I remarked...

"Could be a horse of a different colour though when the change is
from
an opc server."

I've encountered this problem before and I don't know of any
satisfactory solution. I'm also unable to experiment since I don't
have access to a server for adding data to an Excel cell.

The problem seems to be that when the server sends two consecutive
equal values, the arrival of the second value doesn't trigger
Excel's Worksheet_Change Event Procedure so nothing happens so it
doesn't get fed in to column A with the rest of the other old values.

The only "solution" I have seen relies on the Calculate Event to
trigger the code. I believe that any value sent to the workbook by the
server will force a Calculation of the workbook if the RAND() function
is in any of its cells. One problem with this solution is the Calculate
Event Procedure doesn't carry with it the Target argument, which my
code needs. A way around that problem is to use the Calculate Event
procedure to trigger a Worksheet_SelectionChange by having it select
cell A3, but of course this requires that A3 not already be the
selected cell. So the VBA code has to deselect A3 before it finishes
and the user has to be kept out of A3 otherwise they will trigger the
code even though a new server value has not been received. I don't
think you can lock A3 and have the sheet protected, I'm guessing
that would also stop the server entering its values. Maybe just hiding
row 3 would be enough, I'm not sure.

The main problem with this solution is it renders the workbook
absolutely useless for any other sort of activity. Any time you do
something that triggers a worksheet calculation, the code is triggered.
Most things trigger the calculation of a sheet set up for automatic
calculation (you can't turn automatic calculation off - absolutely
nothing would happen if you did that).

If you want to try out this "solution" try the following

1. =RAND() into A1 or anywhere else (I then hid row 1 and row 3)

2. paste these two event procedures into the appropriate worksheet
module...

Private Sub Worksheet_Calculate()
Me.Range("A3").Select
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$3" Then
Dim vaOldValues As Variant
Dim iLastRow As Long
iLastRow = Me.Cells(Range("A:A").Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Me.Cells(Range("A:A").Rows.Count, 1).Clear
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4").Value = Range("A3").Value
Range("B4").Value = Now
Case Else
vaOldValues = Me.Range("A4:B" & IIf(iLastRow = 4, 5, iLastRow))
Range("A5:B5").Resize(UBound(vaOldValues, 1), 2).Value = _
vaOldValues
Range("A4").Value = Range("A3").Value
Range("B4").Value = Now
End Select
Range("B4").Select
Application.EnableEvents = True
End If
End Sub

Just to summarise the sequence of events...

Server enters new value in A3 = sheet has RAND() function forcing
calculation = Worksheet_Calculate causes selection of A3 =
Worksheet_SelectionChange does all the things you requested then
selects B4 (could be any cell, just not A3).


Does the server get values into the workbook when it is closed?

If yes, then you could have all the other parts of your project in a
separate workbook then when the saved server data is needed, open the
workbook with the saved data, turn off automatic calculation, copy the
data, turn automatic calculation back on, close the workbook with the
saved data, paste the copied data into the other workbook.
This could all be coded.

Again, these are all guesses, since I am unable to experiment.

Let me know how you go.

Ken Johnson