D
dale1627
Hoping that some one here can help me with a data logging spreadshee
that I am trying to set up. Right now I am gathering data from one o
our machines that has a Programmable Logic Controller with a networ
card in it. I am getting my data, but am having a hard time with m
logging and clear log. What happens is, when I press the button "Star
Log" my data should be saved in 15 min increments. When I am finishe
logging, I want to clear the log and stop it until I am ready to begi
agian. I have my macros set up and am currently logging, problem i
when I press my "Clear Log" button everything goes crazy. It may lo
every few seconds or every minute. Also if someone presses the "Star
Log" button more that once this occurs. Hopefully some one will b
willing to look at my macro and set me straight.
"Start Logging" macro-
Sub LoggingFunction()
Dim nextCell As Integer
If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then
nextCell = 5
Else
nextCell = ThisWorkbook.Sheets(3).Cells(1, 1)
End If
ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now()
ThisWorkbook.Sheets(2).Cells(nextCell, 2)
ThisWorkbook.Sheets(1).Cells(3, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 3)
ThisWorkbook.Sheets(1).Cells(4, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 4)
ThisWorkbook.Sheets(1).Cells(5, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 5)
ThisWorkbook.Sheets(1).Cells(6, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 6)
ThisWorkbook.Sheets(1).Cells(7, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 7)
ThisWorkbook.Sheets(1).Cells(8, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 8)
ThisWorkbook.Sheets(1).Cells(9, 2)
nextCell = nextCell + 1
ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell
Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction"
Exit Sub
End Sub
"Clear Log" macro-
Sub ClearLog()
Dim i As Integer
i = 5
For i = 5 To ThisWorkbook.Sheets(3).Cells(1, 1)
ThisWorkbook.Sheets(2).Cells(i, 1) = ""
ThisWorkbook.Sheets(2).Cells(i, 2) = ""
ThisWorkbook.Sheets(2).Cells(i, 3) = ""
ThisWorkbook.Sheets(2).Cells(i, 4) = ""
ThisWorkbook.Sheets(2).Cells(i, 5) = ""
ThisWorkbook.Sheets(2).Cells(i, 6) = ""
ThisWorkbook.Sheets(2).Cells(i, 7) = ""
ThisWorkbook.Sheets(2).Cells(i, 8) = ""
Next i
ThisWorkbook.Sheets(3).Cells(1, 1) = ""
Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction"
Schedule:=False
End Sub
Thanks for any help.
Dal
that I am trying to set up. Right now I am gathering data from one o
our machines that has a Programmable Logic Controller with a networ
card in it. I am getting my data, but am having a hard time with m
logging and clear log. What happens is, when I press the button "Star
Log" my data should be saved in 15 min increments. When I am finishe
logging, I want to clear the log and stop it until I am ready to begi
agian. I have my macros set up and am currently logging, problem i
when I press my "Clear Log" button everything goes crazy. It may lo
every few seconds or every minute. Also if someone presses the "Star
Log" button more that once this occurs. Hopefully some one will b
willing to look at my macro and set me straight.
"Start Logging" macro-
Sub LoggingFunction()
Dim nextCell As Integer
If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then
nextCell = 5
Else
nextCell = ThisWorkbook.Sheets(3).Cells(1, 1)
End If
ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now()
ThisWorkbook.Sheets(2).Cells(nextCell, 2)
ThisWorkbook.Sheets(1).Cells(3, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 3)
ThisWorkbook.Sheets(1).Cells(4, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 4)
ThisWorkbook.Sheets(1).Cells(5, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 5)
ThisWorkbook.Sheets(1).Cells(6, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 6)
ThisWorkbook.Sheets(1).Cells(7, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 7)
ThisWorkbook.Sheets(1).Cells(8, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 8)
ThisWorkbook.Sheets(1).Cells(9, 2)
nextCell = nextCell + 1
ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell
Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction"
Exit Sub
End Sub
"Clear Log" macro-
Sub ClearLog()
Dim i As Integer
i = 5
For i = 5 To ThisWorkbook.Sheets(3).Cells(1, 1)
ThisWorkbook.Sheets(2).Cells(i, 1) = ""
ThisWorkbook.Sheets(2).Cells(i, 2) = ""
ThisWorkbook.Sheets(2).Cells(i, 3) = ""
ThisWorkbook.Sheets(2).Cells(i, 4) = ""
ThisWorkbook.Sheets(2).Cells(i, 5) = ""
ThisWorkbook.Sheets(2).Cells(i, 6) = ""
ThisWorkbook.Sheets(2).Cells(i, 7) = ""
ThisWorkbook.Sheets(2).Cells(i, 8) = ""
Next i
ThisWorkbook.Sheets(3).Cells(1, 1) = ""
Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction"
Schedule:=False
End Sub
Thanks for any help.
Dal