Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Automatic update of columns based on changes in target cell value

0

Hello Everyone,

I am writing VBA code to track machine downtime (Excel file with VBA code in the attachment). My question is on how to use a target single cell to update columns continuously. Below shares what I want to achieve:

(1) PLC writes machine status to cell "B2" which is "1" or "0" meaning machine running or not running respectively. At start of production, Immediately "B2" is "1", cell "D2" is updated with start date and time. In the event of stop, "B2" value changes to "0" and cell "C2" updated with stop date and time and the 'stop reason' is also updated simultaneously on cell "K2". This has been achieved. See code below.

(2) The challenge now is when next the machine starts, and cell "B2" value changed from "0" to "1", how do I make this change to update cell "D3", and then  cell "C3" and "K3" when next machine stops. I want this to continue for cell "D4","C4" and "C5" and so on as cell "B2" value changes between "1" and "0"

Number 1 above has been achieved with below code:

Code_Goes_Here

Private Sub Worksheet_Change(ByVal Target As Range)

'Downtime tracking

Dim i As Integer

For i = 2 To 1000

If Target.Address = Range("C2").Address And Cells(i, "C").Value <> "" And Cells(i, "C").Value = "1" And Cells(i, "D").Value = "" And Cells(i, "E").Value = "" And Cells(i, "K").Value = "" Then

    Cells(i, "D").Value = Date & " " & Time

    Cells(i, "D").NumberFormat = "dd/mm/yy hh:mm:ss"

End If

If Target.Address = Range("C2").Address And Cells(i, "C").Value <> "" And Cells(i, "C").Value <> ">=1" And Cells(i, "C").Value = "0" And Cells(i, "D").Value <> "" And Cells(i, "E").Value = "" And Cells(i, "K").Value = "" Then

    Cells(i, "E").Value = Date & " " & Time

    Cells(i, "E").NumberFormat = "dd/mm/yy hh:mm:ss"

    Cells(i, "K").Value = Range("B3").Value

End If

Next

Range("D:D").EntireColumn.AutoFit

Range("E:E").EntireColumn.AutoFit

Range("K:K").EntireColumn.AutoFit

End Sub

Answer
Discuss

Discussion

Hi and welcome to the Forum.

Please clarify your question- you refer to "cell "B2" is "1" and it changing to "0" but in your sheet UVA_Stop, C2 seems to hold the value.(not B2). Does the PLC also write the reason for state change to B3 (always)? I assume column A is a reference list (containing the machine codes for the various machine states).

I don't understand the purpose of your loop starting For i = 2 To 1000 -what is your intention for that? (It seems to sweep through an empty table)
John_Ru (rep: 2867) Nov 29, '21 at 10:54 am
Hello Thank you for responding. It is actually C2 holding the value not B2. It was typo error from me.
Yes PLC writes the reason for state change to B3 e.g when PLC writes 05 to B3 it means 'door open error'
Yes your assumption is right for column A
The intention of the loop starting 'For i = 2 To 1000 is just to state the number of rows I want the code to work for. This can be ignored as I want to populate as much as the sheet can take.

I look forward to your response.

Regards,
Segun
atobseg (rep: 2) Nov 29, '21 at 9:46 pm
Add to Discussion

Answers

0
Selected Answer

Hi and welcome to the Forum

In the revised workbook attached, the code below is associated with sheet UVA_Stop.If the value of 1 is written MANUALLY to cell 2 of that sheet first, it will write the start time to D3. Then (as you change the value from 1 to 0 and back to 1), it will complete cells D and E to suit plus record the Uptime (column G) and Downtime (column F) plus stop reason (column K). Note that I've declared a Public variable LastVal so the initial tests can ignore when the same value is written to cell C2 (e.g. it's already 1 and 1 is reported again) There's a private variable called LastRec to say with row in column D contains the last recoded (start).

Public LastVal As Long

Private Sub Worksheet_Change(ByVal Target As Range)

'Downtime tracking

Dim LastRec As Long

' Get the row number of the last record
LastRec = Range("D" & Rows.Count).End(xlUp).Row

'ignore input if cell C2 not changed or several cells are changed
If Intersect(Target, Range("C2")) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
' ignore any zeroes in C2 before the first 1 is written
If Range("C2").Value = 0 And LastRec = 1 Then Exit Sub
' ignore if the same value is written to C2
If Range("C2").Value = LastVal Then Exit Sub

' for a valid change, record as LastVal
LastVal = Target.Value

'### stop the macro being re-triggered by its own actions
Application.EnableEvents = False

' act on value of C2
Select Case Target.Value
    Case 1
        Cells(LastRec + 1, "D").Value = Now
        If LastRec > 1 Then
            'write the downtime
            Cells(LastRec + 1, "F").Value = Cells(LastRec + 1, "D").Value - Cells(LastRec, "E").Value
        End If
    Case 0
        Cells(LastRec, "E").Value = Now
        'write the uptime
        Cells(LastRec, "G").Value = Cells(LastRec, "E").Value - Cells(LastRec, "D").Value
        'write reason for state 0
        Cells(LastRec, "K").Value = Cells(3, "B").Value
        Case Else
        'Do nothing
End Select


Range("D:D").EntireColumn.AutoFit
Range("E:E").EntireColumn.AutoFit
Range("K:K").EntireColumn.AutoFit

'### re-enable events
Application.EnableEvents = True

End Sub
Note that I've used a series of tests at the start oif the macro to get only a change in the value of C2. I've also added the line 
'### stop the macro being re-triggered by its own actions
Application.EnableEvents = False
for the commented reason (i.e. without it, when you write values, the Worksheet_Change event macro is triggered several times) and a line at the end to resttore it. Note that if the macro fails from  some reason, you can paste Application.EnableEvents = True into the Immediate window of VB Explorer (so the Worksheer_Change event and others are recognised).

Importantly (in my opinion) I've manually formatted cells in sheet UVA_Stop and deleted lines like:

Cells(i, "D").Value = Date & " " & Time
Cells(i, "D").NumberFormat = "dd/mm/yy hh:mm:ss"
(Using the ampersand & and the space makes the value a string (Text) -which doesn't get formatted as a number and prevents uptime and downtime being calculated by simple subtraction). That means the VBA function Now (system date and time) can be used and the assigned (numerical) values can be used in calculations.

Note however that while this macro should work as you manually alter the worksheet, it will not work if changes to C2 and B3 are triggered by an Excel spreadsheet updated via a remote link to the PLC machine.There is however another (workbook) event you might be able to use (WorkbookAfterRemoteChange).

Hope this helps.

Discuss

Discussion

Hello John,
I have manually tested the code and it works! Thank you so much. However, I have three questions:
(1) F2 (downtime) was not updated but G2 (uptime) was updated. F3, F4 etc were updated. Why was F2 (the first downtime cell) left blank
(2) PLC writes directly to C2 and B3. Does your last statement means it will not work when the sheet is linked to PLC via RSLinx?
(3) Only start Time was updating when I copied the code to the excel sheet on my computer. Is this because of the formatting you did? Can you show me what was done?

I look forward to your response.

Regards,
Segun
atobseg (rep: 2) Nov 29, '21 at 10:35 pm
Segun

In response to your questions:

(1) I figured that in row 2 uptime could be calculated once the machine status became 0 (so updated G2) but that downtime could not be calculated until machine status 1 was restored (in row 3) so assigned the downtime to row 3 rather than row 2.

(2) I have no experience of RSLinx but suspect that the code won't work when C2 and B3 are updated using it- you should try it.

(3) Yes, I needed to format columns as numbers to do calculations. Columns F and G for example have the Custom format hh:mm:ss ; column D and E have dd/mm/yy hh:mm:ss . Your code used statements like Cells(i, "D").Value = Date & " " & Time  so the values, became strings (Text).

Hope this helps. I don't think I can help further but hopefully I did enough to guide your efforts and you can mark my Answer as Selected (your choice)
John_Ru (rep: 2867) Nov 30, '21 at 2:14 am
Hi John,
Thank you so much. You have tried. Yes I will mark answered and try it with PLC later. If possible, I will let you know if it works after linking it to PLC.

Regards,
Segun
atobseg (rep: 2) Nov 30, '21 at 7:17 am
Thanks Segun. Hope it works (or you can modify my code to work under a remote event macro).

If not, perhaps someone else knows RSLinx and will respond to a new question (including RSLinx in the title, I'd suggest.)
John_Ru (rep: 2867) Nov 30, '21 at 7:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login