Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
I need to read a certain cell containing a date from a specific worksheet and
extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
You can do it several ways directly using VB code, depending on your
needs... MonthName = MonthName(Month(Range("A1").Value)) MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True) MonthName = Format(Range("A1").Value, "mmmm") MonthNameAbbreviated = Format(Range("A1").Value, "mmm") -- Rick (MVP - Excel) "Domenick" wrote in message ... I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
Yes. More eloquent ("elegant"?) would be to give it a range name, e.g.
"myRange" and then myDate = Format(Range("myRange"),"mmmm") "Domenick" wrote: I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
Of course you would use C9 (to match your posted question) rather than my
example A1 cell reference. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can do it several ways directly using VB code, depending on your needs... MonthName = MonthName(Month(Range("A1").Value)) MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True) MonthName = Format(Range("A1").Value, "mmmm") MonthNameAbbreviated = Format(Range("A1").Value, "mmm") -- Rick (MVP - Excel) "Domenick" wrote in message ... I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
myDate = Format(Sheets("Data").Cells(1, 1).Value, "mmmm")
Regards Lars Klintholm On 17-12-2009 Domenick wrote: I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
myDate = Format(Sheets("Data").Cells(1, 1).Value, "mmmm")
Cells(9,3) of course -- Regards Lars Klintholm |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
Thanks. But how would I specify the specific worksheet using your examples?
"Rick Rothstein" wrote: Of course you would use C9 (to match your posted question) rather than my example A1 cell reference. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can do it several ways directly using VB code, depending on your needs... MonthName = MonthName(Month(Range("A1").Value)) MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True) MonthName = Format(Range("A1").Value, "mmmm") MonthNameAbbreviated = Format(Range("A1").Value, "mmm") -- Rick (MVP - Excel) "Domenick" wrote in message ... I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
Like you would do in any other situation where it is needed to be
specified... qualify the Range property call with it. For example, using just the first statement I posted as an example (you would do the same for the other statements if you decided to use them instead)... MonthName = MonthName(Month(Worksheets("Sheet1").Range("A1").V alue)) or, if you are using a With statement (note the dot in front of the Range keyword for this version).... With Worksheets("Sheet1") ' ' other statements needing to reference back to Sheet1 ' MonthName = MonthName(Month(.Range("A1").Value)) ' ' any other statements needing to reference back to Sheet1 ' End With -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can do it several ways directly using VB code, depending on your needs... MonthName = MonthName(Month(Range("A1").Value)) MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True) MonthName = Format(Range("A1").Value, "mmmm") MonthNameAbbreviated = Format(Range("A1").Value, "mmm") -- Rick (MVP - Excel) "Domenick" wrote in message ... I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using TEXT function in VBA
Like you would do in any other situation where it is needed to be
specified... qualify the Range property call with it. For example, using just the first statement I posted as an example (you would do the same for the other statements if you decided to use them instead)... MonthName = MonthName(Month(Worksheets("Sheet1").Range("A1").V alue)) or, if you are using a With statement (note the dot in front of the Range keyword for this version).... With Worksheets("Sheet1") ' ' other statements needing to reference back to Sheet1 ' MonthName = MonthName(Month(.Range("A1").Value)) ' ' any other statements needing to reference back to Sheet1 ' End With -- Rick (MVP - Excel) "Domenick" wrote in message ... Thanks. But how would I specify the specific worksheet using your examples? "Rick Rothstein" wrote: Of course you would use C9 (to match your posted question) rather than my example A1 cell reference. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can do it several ways directly using VB code, depending on your needs... MonthName = MonthName(Month(Range("A1").Value)) MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True) MonthName = Format(Range("A1").Value, "mmmm") MonthNameAbbreviated = Format(Range("A1").Value, "mmm") -- Rick (MVP - Excel) "Domenick" wrote in message ... I need to read a certain cell containing a date from a specific worksheet and extract the long month name for my macro. The Excel function would be =TEXT(Data!C9,"mmmm"). I have figured out how to write it in VBA if I am just interested in getting cell and not specifying the specific worksheet: Dim myDate as String myDate = Application.WorksheetFunction.Text(C9,"mmmm") How do I specify that I want C9 from a specific worksheet? I have been successful with the following code: myDate = Application.WorksheetFunction.Text(Worksheets("Dat a").Cells(9,3),"mmmm") Is there a more eloquent way of doing this? It seems awkward. Thanks for the help. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a conditional suffix in text function format syntax=text(value,format_text) | Excel Worksheet Functions | |||
Can Text Function change output text color? | Excel Discussion (Misc queries) | |||
Advanced text function (combining text) | Excel Worksheet Functions | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |