Tomorrows date as a string (for searching)

[DaveUK]

Please can someone teel me how to assign tomorrows date as a string (formatted as "03-Jan-2005" for example to a variable "Tdate".

So

Global Tdate as string

Tdate = ????????????

Where ????? is the code i do not know.

TIA

 

[Banker1]

This should do it:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Tomorrow()
<SPAN style="color:#00007F">Dim</SPAN> Tdate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

Tdate = Format(<SPAN style="color:#00007F">Date</SPAN> + 1, "DD-MMM-YYYY")

MsgBox Prompt:=Tdate

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[DaveUK]

Many thanks although i have encountered a problem related to the same task.

The cell i am searching shows "27-Nov-2005" but if i click the cell then the value on the formula bar is shown as "27/11/2005".
Is there a way to have both cell contents and formula bar = "27-Nov-2005"?

TIA

 

[Yogi Anand]

Hi DaveUK:

Try ...

=TEXT(TODAY()+1,"dd-mmm-yyyy")

 

[DaveUK]

Mmmm not understandiing this!!!

I have a date format ("d-mmm-yyyy") in a cell. showing 26-Nov-2005

The same cell shows 26/11/05" in the formula bar.

Obviously the cell is formatted as a Date.

Can i programmatically change the cell value to read "26-Nov-2005" but have it formatted as Text. So the date, although it is a date, is text and not a date format (Hope that makes sense).
It is not essential that the format is of a Date but purely that the value shown is looking like a date eg. "26-Nov-2005".

This is the code i am using to populate my worksheet.

Private Sub CommandButton1_Click()
Dim c As Variant
Dim pt As Variant


CloseDate = Format(DateValue(EndDateTextBox.Value), "d/mmm/yyyy")


RefNo = cbListWorkOrderNumbers.Value ' From TextBox
   
With Workbooks("BB Closure prep.xls").Worksheets("Schedules").Columns("B:B")
Set c = .Find(RefNo, LookIn:=xlValues, after:=Range("B3"), SearchDirection:=xlPrevious)
If Not c Is Nothing Then
c.Offset(0, -1).Value = NoticeType
c.Offset(0, 2).Value = JobCodeComboBox.Value
c.Offset(0, 4).HorizontalAlignment = xlLeft


c.Offset(0, 4).Value = CloseDate


c.Offset(0, 14).Value = AreaCodeComboBox.Value
Else
pt = MsgBox("NO SUCH REF No EXISTS", vbCritical, "ERROR")
End If
End With

End Sub

NB: Initially the date is entered in format "dd-mm-yy" and converted to "d-mmm-yyyy". But i would like to use this line:

c.Offset(0, 4).Value = CloseDate

to put a text value on the worksheet but the text being whatever the date was eg. "26-Nov-2005".

Please could anybody help.

TIA

 

[DominicB]

Good morning DaveUK

Does this suit your needs?

Sub Test()
ActiveCell.FormulaR1C1 = "=NOW()+1"
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

HTH

DominicB

 

[Yogi Anand]

Hi DaveUK:

Let us see if the following works for you ...

tdate.Value = "=Text(today()+1,""d-mmm-yyyy"")"

 

[Pericic]

Have you tried formatting the cells without code???
Format-Cells-Date-Type
to match what you want...