If(len(trim(b10))=0,"",today())

[Ashfaque]

Hi,

I am trying to place current date in a particular cell in the excel sheet using the below vba code. The code will activate once I made changes in B10 Cell.

=IF(LEN(TRIM(B10))=0,"",TODAY())

It is working fine.

My problem is when I am opening the same sheet at next day, the date is displaying as CURRENT date because I used TODAY() function in the code.

I need once it is placed the transaction date, should keep the same date later on.

So what could be the funcion other that TODAY() in the above code?

Please advise.

Regards,
Ashfaque

 

[ghudson]

Are you using this as a formula in the cell? If so, then you should use VBA and run it off of an event or command button to test the value of B10 and only update the cell if it meets the criteria and only run when you run it.

 

[HaHoBe]

Hi, Ashfaque,

if it´s entered by the user you could use the following code which will check for any change in B10 (code goes behind the sheet where the action should take place):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B10")) Is Nothing Then
  Application.EnableEvents = False
  If Len(Trim(Range("B10"))) > 0 Then
    Range("C10").Value = Date
  Else
    Range("C10").Value = vbNullString
  End If
  Application.EnableEvents = True
End If
End Sub

If a formula is in cell B10 the Worksheet_Calculate-event must be used instead.

Ciao,
Holger

 

[Ashfaque]

Hi Gents,

The subjected formula is in let us say C10 cell and the user is updating in B10. Then what I need is the date in C10 should be the date when action taken. And when the sheet opened next day or any other day, the date in C10 should display the old date that was stored. And this I need to place in many cells downwords like.

B10 -----C10
B11 -----C11
B12 -----C12
likewise...

Your code may be right but how can I use that?

Please advise.

With kind regards,
Ashfaque

 

[HaHoBe]

Hi, Ashfaque,

set the security level for Excel to medium and allow macros when opening the workbook.

Please use this altered code which should do fair enough for checking in column B from row 10 on. Right-Click the WorkbookTab and paste the code in there, it will only be available in the sheet where it´s placed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 10 Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
  Application.EnableEvents = False
  If Len(Trim(Target)) > 0 Then
    Target.Offset(0, 1).Value = Date
  Else
    Target.Offset(0, 1).Value = vbNullString
  End If
  Application.EnableEvents = True
End If
End Sub

Ciao,
Holger

 

[Ashfaque]

Excellent.....

Thanks Ciao,

Interesting code....

Thanks a lot.

WIth kind regards,
Ashfaque Hussain