Converting a date to a String AND concatenating to a "real" text

Officer_Bierschnitt · #1 11-17-2015, 08:35 AM

Hi,

I have to construct the name of a worksheet that I want to open - I guess I can access the sheet using the index_number, but I want to make sure in case someone should have the idea of inserting some new sheet. You never know ...
Up to now I have this code in my VBA script.
It all works fine but for the last line which is seemingly not executed - nor does the script throw an error, it just skips that or whatever.

Code:

Dim v_date As Date
Dim v_datestr As String
Dim v_datestr2 As String
Dim v_Sheetname_pre As String
Dim v_Sheetname As String


    Range("A1802").Select
    Selection.NumberFormat = "dd/mm/yy;@"
    ActiveCell.FormulaR1C1 = "= TODAY()"
    v_date = ActiveCell
    v_datestr = CStr(ActiveCell)
    v_datestr2 = Left(v_datestr, 6) & Right(v_datestr, 2)
    Range("A1803").Select
    ActiveCell.FormulaR1C1 = "= ""Data Input """
    v_Sheetname_pre = ActiveCell
    v_Sheetname = v_Sheetname_pre & v_datestr2

Can anybody tell me what is going wrong here?

Thanks a lot!

Best regards,

Officer_Bierschnitt

Officer_Bierschnitt · #2 11-17-2015, 09:11 AM

Okay,

I have figured out that one, so that's solved.

Graham Griggs · #3 12-01-2015, 06:18 PM

In general you are trying to do things in VBA that are a bit tricky and difficult to test

Do things like formulae and formatting in Excel - that is what it is good at

once you do that the solution is simple - see attached

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I get auto-updated "number of days since specified date" on MS word?	terry795	Word	16	01-07-2016 04:33 PM
Fixing/stabilizing/setting/securing/converting markup of tracked changes into "hard" formatting	paulkaye	Word	6	08-20-2015 02:36 AM
When Outlook starts, a warning box "String was not recognized as a valide datetime"	twormington	Outlook	4	02-03-2014 11:01 PM
Macro VBA "Save as" with bookmarks in file name string	Dom37	Word VBA	2	10-31-2011 03:28 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM