convert formula to code "merge text with date" in merged cells

[abdelfattah]

hello

i have this formula

=F5&""&TEXT(F4;"yyyy/mm/dd")

the result shows in merged cell from a3:e3 i would this formula convert to code in event worksheet change

i want that because i have to hide column f where f5= text and f4=date i don't want see this formula i can hide this column but i don't find this way is practical i would this by code i try it but i failed to do that (note: if is possible to do that int merged cells from a3:e3 )

Private Sub Worksheet_Change(ByVal Target As Range)

Range("c3").Value = Text("inventory", yyyy / mm / dd)

End Sub

 

[arthurbr]

Using Merged Cells Effectively in Excel - Excel Solutions

There's one simple piece of advice for using merged cells effectively in Excel...

excel.solutions

 

[Rick Rothstein]

Using the upper left cell address for the Merged Cells, does this event code do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) Like "F[45]" Then [A3] = [F5&""&TEXT(F4," yyyy/mm/dd")]
End Sub

 

[abdelfattah]

hi rick

i would directly write text without specific cell like f4,f5 i would only show this from a3:e3 "inventory date 28/06/2020" i don't want the text depend on f4 is it possible do this

  If Target.Address(0, 0) Like "F[45]" Then [A3] = TEXT("inventory"," yyyy/mm/dd")

actually i no know what means this "F[45]"

 

[Rick Rothstein]

You say you don't want the code dependent on cell F4, but your original post said cell F4 contained the date. Are you saying the date is fixed and not variable? Also cell F5 (from your original post) contained a text value... is "Inventory Date" that text? Please clarify what is in which cells and what cell reference the code should be using.

 

[abdelfattah]

yes i just show the date in cell f4 to reach my idea so the date is depend on today about the text it is always fixed calls inventory
so the topic for instance today should be " inventory date 28/06/2020 and if tomorrow is "inventory date 29/06/2020 and so on as you see i would this topic without linked cells f4,f5 and the date based on today in short words no cells directly write the date based on today and the text is fixed
i hope this help

 

[Rick Rothstein]

Okay, given what you just wrote, you would not be using the Change event because nothing is changing... so a simple macro seems what you need (I am guessing you are still writing to cell A3 (which is part of a merged cell consisting of A3:E3) thus overwriting whatever was there previously..

Sub SetInventoryDate()
  [A3] = ["Inventory "&TEXT(TODAY(),"dd/mm/yyyy")]
End Sub

 

[abdelfattah]

thanks rick your code works so good about change event i put this code in this event workbook open it changes automatically when i open the file and change the date based on today thanks again