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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,019
Messages
5,834,980
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top