Insert current date into cell based on another cell true / false across workbook sheets

camerong · May 10, 2023

Hi guys,

Need help with the formula below.

What I need it to do is search through the workbook sheets (2-101) and check on each sheet if cell "D1" shows a result of TRUE or FALSE. If it is TRUE, then I need the formula to change the contents of cell "E6" on that sheet to read the current date (DD/MM/YYYY format). If it is FALSE, then I need the formula to go onto the next sheet and search for TRUE or FALSE and so on, all the way to sheet 101.

Thanks guys.

Here's the code I currently have:

VBA Code:

Public Sub Update_date_on_selected_RFIs()

  Dim i As Long
 
  For i = 2 To 101
    If Sheets(i).Range("D1").Value = True Then
    Range("E6").Value = Format(Now, "DD/MM/YYYY")
    Else
    End If
  Next
DefaultMsgBox

End Sub

Sub DefaultMsgBox()

MsgBox "Process complete"

End Sub

Sub Date_Change()

Range("E6").Value = Date

End Sub

DanteAmor · May 10, 2023

Hi @camerong .

The macro works fine.
Which problem you have?

Verify that in cell D1 you have TRUE without quotes instead of "TRUE" with quotes.

Dante Amor

D

1

TRUE

Hoja2

camerong · May 10, 2023

Hi Dante,

I've managed to find an answer to this on another forum, see working code below:

VBA Code:

Option Explicit
Public Sub Update_date_on_selected_RFIs()
  Dim i As Long
  For i = 2 To 101
    If Sheets(i).Range("D1").Value = True Then
        Sheets(i).Range("E6").Value = Format(Now, "DD/MM/YYYY")
    End If
  Next i
    DefaultMsgBox
End Sub

Thanks

DanteAmor · May 10, 2023

DanteAmor said:
Which problem you have?

You didn't answer my question.

The macro you posted does the same thing.

By the way, if you are going to repeat the same question in other forums, check the following:
Cross-posting rule #13 of the Forum Rules.

camerong · May 10, 2023

Hi Dante,

The problem I was having is that it wasn't updating the date values in the cells on each worksheet.

Here is a link to the answered question in the other forum to comply with Forum rule #13: