Tomorrows date as a string (for searching)

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245
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



Code:
Global Tdate as string

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

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

TIA
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
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

Board Regular
Joined
Jan 24, 2005
Messages
245
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
 

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245

ADVERTISEMENT

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.


Code:
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi DaveUK:

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

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

Pericic

Board Regular
Joined
Nov 11, 2005
Messages
204
Have you tried formatting the cells without code???
Format-Cells-Date-Type
to match what you want...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,588
Members
412,537
Latest member
Mohamed_5966
Top