Converting number to text with formatting in VBA

ok, her is what I have
   E          F
14:00    15:00

I want the out put to look the same only converted to text in the l
column.  I can do it with formulas in each cell, but when I try to get
VBA to drop the formulas in the cells, it errors.  I am using:

    Range("l1:l4000").FormulaR1C1 = _
        "=text(RC[-4],"[h]:mm")"

    Range("m1:m4000").FormulaR1C1=_
        "=text(RC[-4],"[h]:mm")"

basically it is seeing the " and taking that as the end.

Any ideas?

0
12/16/2005 4:45:51 AM
excel 39879 articles. 2 followers. Follow

3 Replies
307 Views

Similar Articles

[PageSpeed] 58

You need to double up the internal double quotes eg

Range("l1:l4000").FormulaR1C1 = _
         "=text(RC[-4],""[h]:mm"")"

Hope this helps
Rowan

ldashburnwest@yahoo.com wrote:
> ok, her is what I have
>    E          F
> 14:00    15:00
> 
> I want the out put to look the same only converted to text in the l
> column.  I can do it with formulas in each cell, but when I try to get
> VBA to drop the formulas in the cells, it errors.  I am using:
> 
>     Range("l1:l4000").FormulaR1C1 = _
>         "=text(RC[-4],"[h]:mm")"
> 
>     Range("m1:m4000").FormulaR1C1=_
>         "=text(RC[-4],"[h]:mm")"
> 
> basically it is seeing the " and taking that as the end.
> 
> Any ideas?
> 
0
12/16/2005 4:51:25 AM
your a geniuse thanks

0
12/17/2005 10:48:05 PM
You're welcome.

ldashburnwest@yahoo.com wrote:
> your a geniuse thanks
> 
0
12/18/2005 10:00:32 PM
Reply:

Similar Artilces:

Text prediciton
How can I stop excel completing text when I enter data e.g. if a line above says "study day" I enter the line below "student" when I enter "s" it will automatically enter "study day", how do I stop this? Hi, Tools>Options>Edit tab - uncheck 'Enable AutoComplete for Cell Values' HTH Martin Thanks Jason "MartinW" wrote: > Hi, > > Tools>Options>Edit tab - uncheck 'Enable AutoComplete for Cell Values' > > HTH > Martin > > > Boenerge, You may know this, but when the autocomple...

Conditional Formatting #68
Is it possible to base conditional formatting on the contents of a cell other than the one being formatted? use formula instead of cell contents. i.e. you want to format cell a1 based on b1 format|conditional formatting|formula is instead of cell value is =3Db1>10=20 or=20 whatever you want it to achieve.=20 On Sat, 30 Oct 2004 08:37:05 -0700, in microsoft.public.excel.misc = falling into the bathtub with your monitor, the short circuit caused the following to mysteriously appear from your keyboard: ~>Is it possible to base conditional formatting on the contents of a cell ~>ot...

In excel 2003, the negative numbers not appearing with brackets
In my PC's excel 2003, the negative numbers are not appearing with brackets, where as its appearing in other PCs. I think something has been disturbed, how to fix? Check your cell formatting. -- JoAnn Paules MVP Microsoft [Publisher] "Anwar" <Anwar@discussions.microsoft.com> wrote in message news:3996C210-5B86-4DCF-AB6A-69332542D575@microsoft.com... > In my PC's excel 2003, the negative numbers are not appearing with > brackets, > where as its appearing in other PCs. I think something has been disturbed, > how to fix? If you just want that fo...

Formatting Cells Containing Date Fields That Are Text
Someone here at work imported into Excel a column of text data that looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could simply highlight the columns, click on Format/Cells/Custom and create mmddyyyy. I do this but the data doesn't change UNTIL I highlight each cell and press F2 or double-click in each cell or create a formula using Right, Mid, etc. When I press F2 or double-click, I notice that an X and a check mark both appear to the right of the Row/ Column box. There's got to be an easier way to do this besides 'onesie-twosie' but we can't figure it...

[odbc] What is the correct format for time in sql?
[VC++ 6.0][ODBC] I need to send sql (select id from TABLE where date>=#2001/10/20# and date<#2003/05/20# and time>#01:20# and time<#20:45#) to database. What is the correct format for time? ArtuS "ArtuS" <artu_s@.gazeta.pl> wrote in message news:bsmevt$q37$1@inews.gazeta.pl... > [VC++ 6.0][ODBC] > > I need to send sql (select id from TABLE where date>=#2001/10/20# and > date<#2003/05/20# and time>#01:20# and time<#20:45#) > to database. What is the correct format for time? > > ArtuS "... where date >= {d '2001-10-20...

Exporting to MS DOS Text file
I have an Access Report which I am using to create a text file for upload into a 3rd party application. When Access exports the text file it is creating a carriage return (i.e., empty row) in the first row of the created text file thereby making the file incompatible for upload to the 3rd party. Is there a setting I need to select to prevent this from happening? Any suggestion you may have would be appreciated. Thanks in advance for your help! Regards, ...

Delete a table using VBA
I have some code that opens a couple of make-table querys, and then executes some more code. I would like to insert the line 'DoCmd.DeleteObject acTable, "Table to Delete"' to delete those tables that are created, but it doesn't work. The tables don't delete. I'm assuming it has something to do with the loop, but I can't figure out where to put the deleteobject code to make it work. Here's the code that I have now: Private Sub btnEmailVolunteers_Click() DoCmd.OpenQuery "Community Project1" DoCmd.Close acQuery, "Community Projec...

How to find the most common numbers?
I have 300 number groups and each group has 22 different numbers (from 1 to 80). I want to find 5 (or more) numbers that occur together the most. Can I find these numbers by excel, macro, VBA or any program? for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300. Is it possible , I added my file to explain my question in detail, Thanks +-------------------------------------------------------------------+ |Filename: find common numbers1.zip | |Download: http://www.officefrustration.com/attach...

Page numbers restart with continuous page break
This happens in 2007, 2003, and 2002. My doc has 2 "next page" section breaks within the first 3 pages. After each break, I've requiested that the numbering restarts at "1" and that works fine. After several pages I need 2 "Continuous" page breaks so I can use a 2-column format within a page. I use the Insert Page Number Format command to have it CONTINUE numbering. However, it restarts the numbering. If I do this as a test with a doc that does not have regular section breaks at the beginning it seems to work. Best case, I would like to ma...

Conditional Formatting #24
Hi, I have used conditional formatting with effect but I am stumped on the following, If cells C19 thru C22 contain anything then I want the contents of cell D19 to be in a red font. Thanks in advance for any help, Rick One way: Select D19. CF1: Formula is =COUNTA(C19:C22) Format1: Font/Red In article <3l1e5kF10inc8U1@individual.net>, "Optiglaze" <optiglaze@gmail.com> wrote: > Hi, > I have used conditional formatting with effect but I am stumped on the > following, > > If cells C19 thru C22 contain anything then I want the contents of...

Can't Get Line Number on Status Bar
In Word 2003, I was able to look at the status bar at the bottom of the screen to see what line I was on in a particular document. Is it possible to do this in Word 2007? Thanks. Yes. Right click in the status bar. Line number is one of the choices. Pam mcwein wrote: >In Word 2003, I was able to look at the status bar at the bottom of the >screen to see what line I was on in a particular document. Is it possible to >do this in Word 2007? Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-docmanagement/201001/1 Yes it is...

easy way to delete all rows with no text in them?
I just took on a client who sends me a multipage spreadsheet weekly with movie times for his 10 theatres. My guess is he just deletes the information from the previous weeks row and adds a new row. His sheet really only needs about 16 columns and maybe 20 rows. The sheets he sends have upwards of 1500 rows and columns. Many of the rows are in between the actual text rows as well. I convert this to a webpage and post to his site. Problem is the sheets are so large they take forever to clean up (in GoLive) and much too long to do by hand. Is there an easy way to delete all rows with no text in...

How can I convert a number to english text in Excel
Is there a way to convert an certain number, to english text in Excel. For eg. An invoice total of $1000 shoul also appear in words (Dollars One Thousand) Hi, See the following: http://longre.free.fr/english/ http://support.microsoft.com/default.aspx?scid=KB;EN-US;140704& http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360 http://www.bygsoftware.com/examples/examples.htm http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip Also check out the following add-in by astien Mensink http://www.asap-utilities.com/ Super utility one of the functions converts numbers to text lik...

Converting Access 2002 to Access 2003 Database
Hi My company has just updated to Window Xp professional ( Operating System ) and Microsoft Office Xp professional. Previous version were Window 2000 and Microsoft Office Xp professional. I have quite few Access database design in Access 2002 ( ie Office Xp ) When I try to convert this database to Access 2003, it gave me error, saying I need to download and update " Microsoft Jet 4.0 service pack 8". I downloaded and updated my systems. When I try to open this database, I get following error " Security Warning : Unsafe expressions are not blocked. Microsoft jet 4.0 service ...

#REF! errors due to number of rows
Hello, I hope this problem description makes sense. If not, I can try again. I have a spreadsheet that imports data from a csv file. I've set up a button to refresh the data from the csv file. The spreadsheet uses columns A thru BS and then hides many of the columns to give an "Inventory Report". This report shows columns like Item, Description, Bin Location, On Hand, Cost, Last Sales, etc. The spreadsheet then creates a final column titled "Extension" which is Cost x On Hand. This final column, Extension, is a calculation and not imported from the csv file. At t...

Excel is defaulting to Number format instead of General format
The programme for 2007 has recently been installed, and my cells are all formated to Number instead of General, and Excel won't let me change it back to General. We tried to uninstall and re-install but this did not help. Maybe you have a workbook template file named book.xlt (or .xltx or .xltm) in your XLStart folder. This template file is used as the basis for starting new workbooks. You could try to edit it (and change the number format) or move it (or delete it). Then restart excel to test. Kim wrote: > > The programme for 2007 has recently been installed, a...

Word document format marks
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do I hide format marks in my Word documents. Docs show paragraph marks, tab marks, etc. They do not print when I print the document. One day I was typing and accidentally hit a key or key combo and there they were. You can simply click on the �-icon in the Standard toolbar (it looks like an inverted "P"), or, if you prefer using the keyboard, hit Cmd+8. On 30/03/10 15:52, DrJ117@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel > How do I h...

Formatting buttons
Ive now got a set of buttons that Ive assigned Macros to. How do change the format of the buttons. I can see how I change the format o the text in them, but Id really like to colour code the actual button as well. Is that possible ? Cheer -- Ainsle ----------------------------------------------------------------------- Ainsley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3196 View this thread: http://www.excelforum.com/showthread.php?threadid=52051 Hi Go to View / Toolbars / Customize. You can now right-click on the icon and select an option such as ...

Undefined function 'Format$' in expression.
Dear all, I've converted my db from 97 to 2k. When I tried to run a Query, above mentioned message pops up. It seems like there's no such function of Formst$ (change date format) in Access2k? Or it's about conversion error? Also, I can view my VB codes after conversion. Appreciate someone to help. Thanks. Hi Mike, This normally means something has gone with references in the VBA project. See http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html for details and how to fix it. On Wed, 1 Oct 2003 03:24:26 -0700, "Mike Oh" <mike_oh@memwire.com> ...

The spacing between lines of text is not uniform; how can I fix it
Occasionally, lines of text within a paragraph end up unevenly spaced (this has happened in Word and in Publisher). I've checked to make sure the defaults were set to even spacing (e.g., 1 space between lines) and they were. What else can I do to fix this problem? As it stands right now, I think the document looks somewhat sloppy because within just one paragraph I think I have 3 differently-sized spaces between lines. ...

Excel Cell Formatting, multiple lines
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to force a line wrap within a cell in Excel, similar to the way Shift^Return forces a line wrap in a table cell in Word? Control+Command+Return as well as Control+Option+Return. Have a look in Excel Help for others: Keyboard shortcuts used in Excel. HTH |:>) Bob Jones [MVP] Office:Mac On 5/22/10 1:51 PM, in article 59bb8db7.-1@webcrossing.JaKIaxP2ac0, "IHS@officeformac.com" <IHS@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (S...

Left align '$' and right align numbers?
Hi, I tried searching for an answer to this myself, but "left righ align $" yields nothing useful to me. So I have seen spreadsheets where the $ is left aligned in the cell an the dollar amount is right aligned. How do I do this? I trie formatting the cell in all types of different coutnries currencies bu that didn't do the trick. I assume it is somewhere in the cel formatting, but I can't find it. Also, I want to put 1-99 in a cell. It keeps going to January 1999. changed the cell to 'text' and it keeps 1-99 in there. Is this the bes way? Many, many thanks...

Help! Extract the subject of all mails in outlook to a text file
How can I extract the information, such as subject, sender, time of all the mails in my Inbox into a text file? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Switch to a table view that contains all the fields you need the information from, select all items, and press CTRL+C to copy. You should be able to paste the data in columns into a text file. Note that not all available fields will copy in this manner, but the ones you mentioned should work. -- Jocelyn Fior...

AutoNumber versus form record number
Using Office 2003 and Windows XP; I have a bound form. The table the form is bound to contains an AutoNumber column named Num which is also a primary key for the table. As the form is scrolled, the record number of course, changes. 1) Is the record number shown on the form ALWAYS equal to the AutoNumber generated for that record in the table? 2) Is there a way to obtain the record number (shown in the form)? If so, what would the code look like to return this number in a variable? Thanks much in advance. (1) If you have the control bound to the autonumber field. (2) variable = contro...

Progamatically limit the number of certain records
I manage a camper registration database and we recently have come across a couple situations where it would be great if we could limit how many campers are registered. For example, we allow 10 day campers and 100 traditional campers. When a camper is registered they are given a code (DayCamp10 for day campers, Camp10 for traditional campers) and having that code is what the form uses to display the appropriate camp registration form for more detailed data. My thoughts where if I could control the number of DayCamp10 and Camp10 records that are created in my tblCodes to 10 and 100...