Create a String to Use as a Range Name - Illegal Characters

[Shiseiji]

The problem, I think, is that I'm ending up with illegal characters so can't use the string as a range name and I don't know where the characters are being inserted. I also understand a UDF can be created to clean the string, but I simply can't figure out to use the UDF with VBA.

Help.

TIA

Dim tCell                   As Range
Set tCell = Cells(1, LastCol).Offset(0, 1)
With tCell
        .NumberFormat = "mm-dd-yyyy"
        .FormulaR1C1 = "=""User_"" & TEXT(TODAY(),""mm-dd"")"
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "@"
  End With
tCell.Name = tCell

I've also tried .Value = .Value


Ron

 

[Fluff]

Try

Dim tCell                   As Range
Set tCell = Cells(1, LastCol).Offset(0, 1)
tCell.Value = "User_" & Format(Date, "mm_dd")
tCell.Name = tCell

 

[Shiseiji]

Success! KISS, got to love it. Focusing too much learning how to avoid "Activate" & "Select" that I've obviously using it where a KISS is much better.

Thanks much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Shiseiji]

I value the help too much to not show appreciation. I do my volunteering in a bicycle coop and value the thanks I get. So many here give so much more. My join date shows '09, but that's because till then I found what I needed without asking since probably 2000????? seems like that long ago anyway.

As a Soldier, what I learned here was invaluable after 9-11 for managing deployment schedules. No where near as automated as many would assume, I had to filter, sort, compare, and then report out on data dumps 2 a day for months to be sure the troops were ready when the planes were coming. You never know how far a quick post here will go. Now that I'm retired I can say that the whole time I thought, "This is insane, the country has no idea what we are getting ourselves into. Or how hard it is going to be to get out . . . . "

Ron