 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2004Excel Custom Format Not Working - Formula Problem?
Hi All, I wrote a macro that makes a cell equal to a vlookup formula. So m code looks something like this: Range("A2") = "=vlookup(A1,A3:B50,3,False)" Then I want the custom format of 000000.00 applied to cell A2. So I coded: Range("A2")..NumberFormat = "000000.00" This does not work however, the format does not change. I noticed the only way to solve this problem is to manually select th cell then click on the contents of the cell and hit ENTER. Have any of you encountered this problem? Any ideas on how to fix it? Any help would be greatly appreciated! Thank you -- Message posted from http://www.ExcelForum.com Try it with only one "." ).N Range("A2").NumberFormat = "000000.00" With only columns A and B in the lookup table, you can't return a result from column 3 so that might be a problem too. A small test: Sub test() Range("A2").Formula = "=if(isna(vlookup(A1,A3:B50,3,False)),0,vlookup(A1,A3:B50,3,False))" Range("A2").NumberFormat = "000000.00" End Sub Regards Trevor > Hi All, > [quoted text clipped - 22 lines] > --- > Message posted from http://www.ExcelForum.com/ thanks for the reply. i tried it and it still does not work. the format is not changing. the 3 and the extra . was just a typo on my part... > *Try it with only one "." ).N > [quoted text clipped - 45 lines] > > --- > > Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com i think the problem is... the number in A2 is type 2. If I divided the number by 1 or if simply select the cell and hit ente in the editing field on top.... it switches to type 1.. and th formatting kicks in. how do i change the type of a cell -- Message posted from http://www.ExcelForum.com ok! i figured it out. all i had to do was simply add a "/1" at the end of my vlookup.. and i converts it to a number. thanks -- Message posted from http://www.ExcelForum.com Hi Mazuzu, That is one way of making it work. I would fix the table. Obviously you have text. select an empty (never used cell) and copy it Ctrl+C select the column in your table that should be numbers Edit , paste special, add Then you can check what is text constants Ctrl+A (select all) Edit, Goto, Special, constants & text More infomation http://www.mvps.org/dmcritchie/excel/join.htm#debugformat HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > ok! > [quoted text clipped - 7 lines] > --- > Message posted from http://www.ExcelForum.com/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.