I am trying to automate a spreadsheet that is used with a datalogger.
Within the spreadsheet I have four cells that are linked to the
datalogger system and changes that values at the end of the test
cycle. If the values change I compare them to the last known data
values with an if statement (cell k4). From that point I update the
counter and move the data from row 3 to row 4, than move the data set
down to the table below.

The marco I wrote works find but I want to automate it so it runs
either in a loop or when the value of K4 changes. (Remembering K4 is
an if statement). Again I want to eliminate the need for the user to
activate my macro but instead just start it once and it will run till
the spreadsheet if full.

I am a new to coding so any help would be appreciated.

Sub CheckChange()
'
' CHECK FOR CHANGE BETWEEN ROW 3 & 4 (OCCURS WHEN DATA LOGGER IS
UPDATED)
' IF CELL K4 IS CHANGE, MOVE ROW 3 TO 4, INCREASE THE COUNTER
'
If Range("K4") = "Change" Then
Range("B3:J3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
If Range("E3") = "1" Then
Range("E3").Select
ActiveCell.FormulaR1C1 = "2"
ElseIf Range("E3") 1 Then
Range("E3").Select
ActiveCell.FormulaR1C1 = "=R[+1]C+1"
Range("E3").Select
End If
'
' SINCE K4 DOES EQUAL "CHANGE" THAN INSERT A ROW AT 14 AND COPY
' ROW 4 TO 14. THIS WILL PLACE THE LAST DATA COLLECTION AT THE TOP OF
THE SHEET.
'
Rows("14:14").Select
Selection.Insert Shift:=xlDown
Rows("4:4").Select
Selection.Copy
Rows("14:14").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B4").Select
End If

End Sub

Probably use the calculate event to call your sub

http://www.cpearson.com/excel/events.htm for an overview.

--
Regards,
Tom Ogilvy


"Steve" wrote:

I am trying to automate a spreadsheet that is used with a datalogger.
Within the spreadsheet I have four cells that are linked to the
datalogger system and changes that values at the end of the test
cycle. If the values change I compare them to the last known data
values with an if statement (cell k4). From that point I update the
counter and move the data from row 3 to row 4, than move the data set
down to the table below.

The marco I wrote works find but I want to automate it so it runs
either in a loop or when the value of K4 changes. (Remembering K4 is
an if statement). Again I want to eliminate the need for the user to
activate my macro but instead just start it once and it will run till
the spreadsheet if full.

I am a new to coding so any help would be appreciated.

Sub CheckChange()
'
' CHECK FOR CHANGE BETWEEN ROW 3 & 4 (OCCURS WHEN DATA LOGGER IS
UPDATED)
' IF CELL K4 IS CHANGE, MOVE ROW 3 TO 4, INCREASE THE COUNTER
'
If Range("K4") = "Change" Then
Range("B3:J3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
If Range("E3") = "1" Then
Range("E3").Select
ActiveCell.FormulaR1C1 = "2"
ElseIf Range("E3") 1 Then
Range("E3").Select
ActiveCell.FormulaR1C1 = "=R[+1]C+1"
Range("E3").Select
End If
'
' SINCE K4 DOES EQUAL "CHANGE" THAN INSERT A ROW AT 14 AND COPY
' ROW 4 TO 14. THIS WILL PLACE THE LAST DATA COLLECTION AT THE TOP OF
THE SHEET.
'
Rows("14:14").Select
Selection.Insert Shift:=xlDown
Rows("4:4").Select
Selection.Copy
Rows("14:14").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B4").Select
End If

End Sub