Re: $$Excel-Macros$$ format a cell
Dear Noorain, I think he want 532, and I'd like to be converted to 05:32 From: NOORAIN ANSARI noorain.ans...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:55 AM Subject: Re: $$Excel-Macros$$ format a cell Dear Pawel. Please try it.. =TIME(LEFT(C4,1),RIGHT(C4,2),0) -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 7:35 PM, pawel lupinski lupins...@yahoo.com wrote: Hi All, I need your help on this simple task i don't know how to do this and for you it will be easy. I'd like to input eg 532, and I'd like to be converted to 05:32 or input 5.32 and be converted to 5:32. I've tried to change cell format like hh:mm, [hh]:mm but its not working what I've received as outcome is data and time format like 12768:00 Can you help me, please. Regards, Pawel-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ printing doubt
thank you noorain bhai On Wed, Mar 28, 2012 at 11:20 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Mothilal, You can use below code to printout all sheets of workbook Sub print_allsheetofworkbook() Dim sh As Worksheet For Each sh In ActiveWorkbook.Sheets sh.PrintOut From:=1, to:=1 Next sh End Sub or Sub print_allsheetofworkbook() Dim i As Integer For i = 1 To Sheets.Count Sheets(i).PrintOut From:=1, to:=1 Next i End Sub Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Wed, Mar 28, 2012 at 9:28 AM, jmothilal gjmothi...@gmail.com wrote: Dear Sir, How can i print all sheets ? Thanks with Mothilal.J On Tue, Mar 27, 2012 at 12:54 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Shankar, You can also use.. Sub Print_setup() Application.Dialogs(xlDialogPrint).Show End Sub On Tue, Mar 27, 2012 at 12:23 PM, Shankar Bheema shankar.n...@gmail.com wrote: Dear experts for a command button of an userform for Printing of sheets purpose I am using code as sheets(sheet1).printout whereas, i need to print 3rd page of sheet1 everytime, so what change is required in the above code to get only 3rd page. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: FW: $$Excel-Macros$$ Digest for excel-macros@googlegroups.com - 25 Messages in 15 Topics
Dear Williams, Pls post query On Thu, Mar 22, 2012 at 10:19 AM, Mike Williams mwilli...@marinaauto.comwrote: ** -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ need help:save excel worksheet data into SQLserver using VBA code
Dear Garcia, Please use below link, hope it is useful for your query. http://www.fontstuff.com/ebooks/free/fsADOConnectExcel.pdf -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 11:46 AM, Garcia yiqiang@gmail.com wrote: Hi, Dear expert, I have a daily work table, i use it every day, i want to send this excel worksheet table to SQLSERVER database, but i do not know how, can you give me VBA code to instruct me how to do it ? I use SQL 2008, thanks in advance. B.R. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
FW: $$Excel-Macros$$ Macro to sort excel data email to party
Can anyone please help me on this? Best Regards, Amit From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Amit Desai (MERU) Sent: 25 March 2012 18:33 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to sort excel data email to party Dear All, I need your expert help... Please find attached sample file. Following things are expected to be done; 1) In first sheet there are data that needs to be sorted work shop wise. 2) In the second sheet email ids are mentioned where we need to send those sorted data per workshop. Best Regards, Amit Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com RNM history for web.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
Hi Howard, So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? You are correct. As simple as that. Just to comment on the syntax a bit - A leading . in an object reference is shorthand to say that the object is a child of the object specified in the With clause. So, to indicate Rows(1) goes with Sheets(ChartData), it needed a leading .. Conversely, if you don't specify a leading . the object is not associated with the With clause and is handled as if there were no With clause. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. Ah.. that's because you have a leading . before your Application reference. It is entirely unneeded. If you got rid of Application and still left that unneeded leading ., then VBA would have interpreted your line of code as Sheets(ChartData).WorkSheetFunction.Match (etc.) which is invalid because WorksheetFunction is not a property of the Sheet class. Your current line is interpreted as Sheets(ChartData).Application.Match which is valid, but only because most objects have an Application property that refers circularly to the Application object. Also, it is not always desirable to use the shortest line of code possible. Especially be careful about using unqualified object references, such as your Rows(1) without the leading ., unless you are sure you know how it is interpreted under all circumstances. There are quite a few objects that Excel usues complicated logic to determine what to apply them to when you don't specify (and the logic isn't always documented). Similarly, be careful about using assuming you know what the active workbook or worksheet is. The best practice, unless your code is specifically relevant to WHATEVER workbook or worksheet is active, is to specify what the workbook and worksheet. If you remove the Application from Application.WorksheetFunction.Match, that's fine (I would), and many people -- even reknowned experts -- refer to worksheet functions by eliminating WorksheetFunction, as you did: Application.Match, but I prefer not to: (1) it is undocumented. Worksheet functions are not documented as methods of the Application object. Anything that's undocumented is not guarnateed to do what you think it does, and not guaranteed to work in future versions of Excel. (2) I've verified (though not the discoverer) that at least one worksheetfunction method -- RoundDown -- gives incorrect results in some circumstances when referred to as Application.RoundDown (3) Error handling is different when you abbreviate worksheetfunction methods to Application.(function). Specifically, an error is not generated, although an error code is in the return value from the function. (4) between reason number 2 and 3, my theory is that the Application version is the version used from worksheet formulas, and the WorksheetFunction version is the only version intended to be used from VBA, and behaves differently as required by an environment that has different error handling and many more datatypes than a worksheet does. So... my reccommended revision to the key lines of code is: fv = WorkSheetFunction.Match(Stopval, .Rows(1), 0) lv = WorkSheetFunction.Match(ReverseDate, .Rows(1), 0) Most significantly, notice the absence of a leading .. . . . It looks like you were declaring your variables outside the procedure so that they would be available to multiple procedures during the time that your workbook is open? If I am correct in that guess, you should be aware that various events outside of your control can cause VBA to lose state and your variables could be reset at anytime. So, doing what I think you're doing can complicate things, because if you do that, you should check that your variables are initialized at the start of entry procedures (such as event and key-assigned macro procedures) that might refer to them. Perhaps by calling the initialization routine at the beginning of those procedures, and in the initialization routine itself, check perhaps that just one value has been initialized, and if so, skip the rest of the routine, assuming that they all have... but in most cases this is unneccessarily complex. There are other methods to avoid recalculation, such as waiting to calculate values until needed and passing the calculated values as parameters to routines that require them, although that doesn't help a lot in rapid-fire events. But for that case, don't worry unless you find that performing your calculation is time consuming enough to cause a performance problem. If it is, you can consider declaring those variables locally in that procedure instead of globally, using the Static keyword so that they retain values
Re: $$Excel-Macros$$ need help:save excel worksheet data into SQLserver using VBA code
Dear Noorain, Thanks,perfect, let's me study it. Best regards Gacia On Wed, Mar 28, 2012 at 3:00 PM, Darwin Chan darwin.chankaw...@gmail.comwrote: Dear Noorain, Really thanks for your information. I am also a newbie on DB administration. Hope I can learn a lot from the tutorial you give. 2012/3/28 NOORAIN ANSARI noorain.ans...@gmail.com Dear Garcia, Please use below link, hope it is useful for your query. http://www.fontstuff.com/ebooks/free/fsADOConnectExcel.pdf -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 11:46 AM, Garcia yiqiang@gmail.com wrote: Hi, Dear expert, I have a daily work table, i use it every day, i want to send this excel worksheet table to SQLSERVER database, but i do not know how, can you give me VBA code to instruct me how to do it ? I use SQL 2008, thanks in advance. B.R. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards George -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Email Sheet with message
Talal, i´m also a beginner, i´m using some books, but i haven´t found any extensive one in VBA, i use recording methods to see what the code performs, and use other posts to see what it returns in the excel provided, it has helped me a lot improving my skills, i notice that i not still able to build code by myself, but know, I can read and understand what it means, so i can build macros but taking pieces from other macros and adapt to what i pretend, and when i have doubts this forum is 100% perfect. i hope someday i can be the one responding to solutions and not asking for them :), wish you the best in this Excel world. 2012/3/28 Darwin Chan darwin.chankaw...@gmail.com Hi all, I found this thread which is really useful, however, I couldnt find the Tools-Reference-Microsoft Outlook 12.0 Object Library as mentioned by Noorain MS Excel 2000 version How could I get access to the Library? 2012/3/20 NOORAIN ANSARI noorain.ans...@gmail.com Dear Talal, It's really simple but it takes some time. Spend 2-3 hours a day, every day, in these forums reading posts and trying the solutions provided. Experiment! Try to Create simple macro by help of record macro,Google,Books. Hope your Excel-VBA will surely improve. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Wed, Mar 14, 2012 at 12:39 PM, Muhammad Talal Akbar sh.talal.ak...@gmail.com wrote: Hi Mr. Ansari how are you? My dear i need your help can u guide me; how can i improve my skills in excel and VBA regards, On Wed, Mar 14, 2012 at 11:20 AM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Please add Outlook library before run the code Tools-Reference-Microsoft Outlook 12.0 Object Library. On Wed, Mar 14, 2012 at 11:13 AM, LearnExcel sendse...@gmail.comwrote: i'm using the following code to send email how do i add something to the body? does anyone know if its possible to add a message to the email? ThisWorkbook.Sheets(1).Copy With ActiveWorkbook .SendMail Recipients:=emailaddr...@df.com, _ Subject:=Try Me) .Close SaveChanges:=False End With *End Sub* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/*
Re: $$Excel-Macros$$ Re: Match text to a list
Kris, Sorry, you are quite right. I made the mistake of copying the previous code and replacing the prvious code with the previous codes. thank you so much for your patience. On Thu, Mar 22, 2012 at 2:49 PM, Kris krishnak...@gmail.com wrote: That's not true. MATCH(A,**L6:L27,0) is not equal to MATCH(AA,**L6:L27,0). Can you attach the workbook ? Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ REG:$$Column to table formula not working
Sir, the file is attached. From: dguillett1 dguille...@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, March 27, 2012 11:39 PM Subject: Re: $$Excel-Macros$$ REG:$$Column to table formula not working Show us your before/after data and/or send a file with explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: karunanithi ramaswamy Sent: Tuesday, March 27, 2012 12:34 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ REG:$$Column to table formula not working Sir, About 4 to 6 months back, in theis Forum Someone(may be Siti or so) has given a formula to convert a column to table. i.e., =Row(A1)*3-(3-Column(A1)) this works fine when A1 to A100 is 1 to 100. But not with 10 digit cell phone number data. Can any one suggest any formula or vba.(i am using windows 2007) Karunanithi R-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com col-table.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ REG:$$Column to table formula not working
Hi, For D1 Try, =ROW($A1)*3-(3-COLUMN($A1)) For H1 Try, =ROW($B1)*3-(3-COLUMN($B1)+1) Regards, MARIES. On Wed, Mar 28, 2012 at 3:21 PM, karunanithi ramaswamy era_...@yahoo.comwrote: Sir, the file is attached. *From:* dguillett1 dguille...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Tuesday, March 27, 2012 11:39 PM *Subject:* Re: $$Excel-Macros$$ REG:$$Column to table formula not working Show us your before/after data and/or send a file with explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* karunanithi ramaswamy era_...@yahoo.com *Sent:* Tuesday, March 27, 2012 12:34 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ REG:$$Column to table formula not working Sir, About 4 to 6 months back, in theis Forum Someone(may be Siti or so) has given a formula to convert a column to table. i.e., =Row(A1)*3-(3-Column(A1)) this works fine when A1 to A100 is 1 to 100. But not with 10 digit cell phone number data. Can any one suggest any formula or vba.(i am using windows 2007) Karunanithi R -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com col-table.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ printing doubt
Thanks to Noorain Ansari, , Abhishek Jain Mothilal On Wed, Mar 28, 2012 at 11:09 AM, Abhishek Jain abhishek@gmail.comwrote: Use below code for printing all sheets in a workbook - Option Explicit Sub PrintallSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PrintOut Next ws End Sub On Wed, Mar 28, 2012 at 9:28 AM, jmothilal gjmothi...@gmail.com wrote: Dear Sir, How can i print all sheets ? Thanks with Mothilal.J On Tue, Mar 27, 2012 at 12:54 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Shankar, You can also use.. Sub Print_setup() Application.Dialogs(xlDialogPrint).Show End Sub On Tue, Mar 27, 2012 at 12:23 PM, Shankar Bheema shankar.n...@gmail.com wrote: Dear experts for a command button of an userform for Printing of sheets purpose I am using code as sheets(sheet1).printout whereas, i need to print 3rd page of sheet1 everytime, so what change is required in the above code to get only 3rd page. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread
Re: $$Excel-Macros$$ SQL Query - Excel VBA
Hi, Please see my comments in the tag [sharma]. Regards, Sharma On Sat, Mar 24, 2012 at 1:40 AM, Asa Rossoff a...@lovetour.info wrote: Hi Sharma, I replied two weeks ago when you posted this question with a detailed response with tips and requesting more information if you still needed help. ** ** You didn't reply, but here is my post again. I can help more, bnut please review what I already said and let me know what help you still need. -- Hi Sharma, How do you define failure for the first query? 0 records returned? an error from the database server/engine? ** [Sharma] Yes you are correct. If 0 records found then we need to run the second query and so on. What do you want to do with the result of the query? Put it on a (new?) sheet in your workbook? [Sharma] Yes I want the result should be updated in a new sheet. **writing** the queries using VBA is very simple. The following assembles the first query statement in a string variable: Sub WriteSQLquery () Dim SQL As String SQL = _ SELECT _ distinct AMRCostCtr _ FROM _ [SRT1].[RequestToolDev].[dbo].[vReqsocs] a, _ [SRT1].[RequestToolDev].[dbo].[ReqMain] b _ WHERE _ a.ReqID = b.ReqID and MainRequestNumber = '365700'; End Sub ** ** Here's a robust-looking, general-purpose procedure called *SQLLoad* to open a recordset for your query and copy the data to a worksheet: Beyond Excel - Say Goodbye to QueryTableshttp://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-querytables/. The associated blog actually contains a lot of good information and code for working with databases from Excel (some of it advanced). ** ** You will need a connection string to identify your data source and (if needed) credentials. Connectionstrings.comhttp://www.connectionstrings.com/has a large index of connection string examples. You should avoid Microsoft Jet connection strings and in cases where there are alternatives, ODBC connection strings, which have been replaced by newer drivers. (ODBC is still fine and commonly used with many non-Microsoft data stores). ** ** See if the above info gets you started. I can provide more help if needed. Asa ** ** ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *amrahs k *Sent:* Friday, March 23, 2012 6:22 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ SQL Query - Excel VBA ** ** Hi Team, Here I am posting a request for running sql queries in excel macro. I have listed my queries below. Currently am running these queries manually. I want these queries needs to be run automatically. SELECT distinct AMRCostCtr FROM [SRT1].[RequestToolDev].[dbo].[vReqsocs] a,[SRT1].[RequestToolDev].[dbo].[ReqMain] b where a.ReqID = b.ReqID and MainRequestNumber = '353817' If the above query does not return a result then it should automatically run the second query and then second query as shown below. SELECT StationBranch FROM [SRT1].RequestToolDev.dbo.ReqAMROOS WHERE ReqID in (select reqid from [SRT1].RequestToolDev.dbo.ReqMain where mainrequestnumber = 353817) if the above one fails then it should run for the third one which is given below. SELECT BillableStationBranch FROM [SRT1].RequestToolDev.dbo.ReqAMRIMAC WHERE ReqID in (select reqid from [SRT1].RequestToolDev.dbo.ReqMain where mainrequestnumber = 353817) Please help me to get this done. Thanks, Sharma Krishnan -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of
Re: $$Excel-Macros$$ REG:$$Column to table formula not working
i think u want as attachment. see attachment On Wed, Mar 28, 2012 at 4:51 PM, karunanithi ramaswamy era_...@yahoo.comwrote: Sir, the file is attached. *From:* dguillett1 dguille...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Tuesday, March 27, 2012 11:39 PM *Subject:* Re: $$Excel-Macros$$ REG:$$Column to table formula not working Show us your before/after data and/or send a file with explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* karunanithi ramaswamy era_...@yahoo.com *Sent:* Tuesday, March 27, 2012 12:34 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ REG:$$Column to table formula not working Sir, About 4 to 6 months back, in theis Forum Someone(may be Siti or so) has given a formula to convert a column to table. i.e., =Row(A1)*3-(3-Column(A1)) this works fine when A1 to A100 is 1 to 100. But not with 10 digit cell phone number data. Can any one suggest any formula or vba.(i am using windows 2007) Karunanithi R -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com col-table.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ REG:$$Column to table formula not working
chk this one. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com col-table.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ vlookup with more than one value
ok thanks From: NOORAIN ANSARI noorain.ans...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:45 AM Subject: Re: $$Excel-Macros$$ vlookup with more than one value Dear Lakshman, No problem bro..but in this case we should try to keep criteria range instead of Criteria Range+Sum Range -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 3:38 PM, LAKSHMAN PRASAD lakshman_...@yahoo.com wrote: Dear Noorain, is there any prob. when i use SUMIF(A1:B4,A7,B1:B4) just for knowlege From: NOORAIN ANSARI noorain.ans...@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, March 27, 2012 7:45 AM Subject: Re: $$Excel-Macros$$ vlookup with more than one value Dear Finos, Please see attached sheet, hope it help to you. -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ On Sat, Mar 24, 2012 at 7:27 PM, finu kodokodan finux...@gmail.com wrote: Please find the attached sheet . please help to me to solve this problem-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
You should write a book. I agree with what you say where I understand it. In fact I am playing with references to learn but for final could would probably use fully qualified paths in places like initialization areas called once. Also not a fan of global vars usually but to save a lot of repitition will use them but certainly must understand the ramifications. Because of the types of operations I will be doing repeatedly on reading cell values, calculating, writing cell values I thought cell functions in loops better than trying to play with ranges. Could be just my limited understanding of the best way to use ranges and references together. I can not store calculations until the end in many cases. Some columns can be calculated after all other data present, but some require a cell to be calculated, get more data, calculate cell I think. I may be wrong about that as I have not seen the full data set yet. I would still not be sure about the best way to use ranges if what I want to do is loop quickly through a column of cells, but that answer may lie is stuff you have already sent me. The concept of losing state is staggering to me. I have never experienced such a thing. A program may crash, but it does not lose set values unless they get clobbered by a bug. Are you saying that even if the program will start running and go to completion without any interaction with it there can still be loss of state? And finally I do not understand what you mean by this: Also, if you are initializing variables that will be used later multiple times, you should initialize them to the greatest extent possible. On Wed, Mar 28, 2012 at 12:42 AM, Asa Rossoff a...@lovetour.info wrote: Hi Howard, So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? You are correct. As simple as that. Just to comment on the syntax a bit - A leading . in an object reference is shorthand to say that the object is a child of the object specified in the With clause. So, to indicate Rows(1) goes with Sheets(ChartData), it needed a leading .. Conversely, if you don't specify a leading . the object is not associated with the With clause and is handled as if there were no With clause. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. Ah.. that's because you have a leading . before your Application reference. It is entirely unneeded. If you got rid of Application and still left that unneeded leading ., then VBA would have interpreted your line of code as Sheets(ChartData).WorkSheetFunction.Match (etc.) which is invalid because WorksheetFunction is not a property of the Sheet class. Your current line is interpreted as Sheets(ChartData).Application.Match which is valid, but only because most objects have an Application property that refers circularly to the Application object. Also, it is not always desirable to use the shortest line of code possible. Especially be careful about using unqualified object references, such as your Rows(1) without the leading ., unless you are sure you know how it is interpreted under all circumstances. There are quite a few objects that Excel usues complicated logic to determine what to apply them to when you don't specify (and the logic isn't always documented). Similarly, be careful about using assuming you know what the active workbook or worksheet is. The best practice, unless your code is specifically relevant to WHATEVER workbook or worksheet is active, is to specify what the workbook and worksheet. If you remove the Application from Application.WorksheetFunction.Match, that's fine (I would), and many people -- even reknowned experts -- refer to worksheet functions by eliminating WorksheetFunction, as you did: Application.Match, but I prefer not to: (1) it is undocumented. Worksheet functions are not documented as methods of the Application object. Anything that's undocumented is not guarnateed to do what you think it does, and not guaranteed to work in future versions of Excel. (2) I've verified (though not the discoverer) that at least one worksheetfunction method -- RoundDown -- gives incorrect results in some circumstances when referred to as Application.RoundDown (3) Error handling is different when you abbreviate worksheetfunction methods to Application.(function). Specifically, an error is not generated, although an error code is in the return value from the function. (4) between reason number 2 and 3, my theory is that the Application version is the version used from worksheet formulas, and the WorksheetFunction version is the only version intended to be used from VBA, and behaves differently as required by
Re: $$Excel-Macros$$ REG:$$Column to table formula not working
Sir, It is happy to see the desired result. But after typing the formula I pressed with ctrl+shift+enter,I dont get the result. Is there any other procedure? Please help in detail. Thank you very much. -Karunanithi R 91-9486100934. From: Sourabh Salgotra rhtdmja...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 5:31 PM Subject: Re: $$Excel-Macros$$ REG:$$Column to table formula not working chk this one. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
I found references on the losing scope issue. I think I understand what I am up agains there. I think unhandled errors the most likely cause if I hit it. I will probably only use one module for what I am doing with appropriate hierarchy of procedures so really public is not needed, but from what I read can still lose scope on variables just global to all procedures? Fuzzy on another point still. If the . on rows is what says that is a child of sheets, then why is the . on worksheetfunction wrong? How does the with sheets work for the worksheetfunctions if they are not designated an children of the with? Is that just implied for worksheetfunction? Even if so it seems like either way should be ok. On Wed, Mar 28, 2012 at 8:53 AM, Domain Admin domainqu...@gmail.com wrote: You should write a book. I agree with what you say where I understand it. In fact I am playing with references to learn but for final could would probably use fully qualified paths in places like initialization areas called once. Also not a fan of global vars usually but to save a lot of repitition will use them but certainly must understand the ramifications. Because of the types of operations I will be doing repeatedly on reading cell values, calculating, writing cell values I thought cell functions in loops better than trying to play with ranges. Could be just my limited understanding of the best way to use ranges and references together. I can not store calculations until the end in many cases. Some columns can be calculated after all other data present, but some require a cell to be calculated, get more data, calculate cell I think. I may be wrong about that as I have not seen the full data set yet. I would still not be sure about the best way to use ranges if what I want to do is loop quickly through a column of cells, but that answer may lie is stuff you have already sent me. The concept of losing state is staggering to me. I have never experienced such a thing. A program may crash, but it does not lose set values unless they get clobbered by a bug. Are you saying that even if the program will start running and go to completion without any interaction with it there can still be loss of state? And finally I do not understand what you mean by this: Also, if you are initializing variables that will be used later multiple times, you should initialize them to the greatest extent possible. On Wed, Mar 28, 2012 at 12:42 AM, Asa Rossoff a...@lovetour.info wrote: Hi Howard, So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? You are correct. As simple as that. Just to comment on the syntax a bit - A leading . in an object reference is shorthand to say that the object is a child of the object specified in the With clause. So, to indicate Rows(1) goes with Sheets(ChartData), it needed a leading .. Conversely, if you don't specify a leading . the object is not associated with the With clause and is handled as if there were no With clause. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. Ah.. that's because you have a leading . before your Application reference. It is entirely unneeded. If you got rid of Application and still left that unneeded leading ., then VBA would have interpreted your line of code as Sheets(ChartData).WorkSheetFunction.Match (etc.) which is invalid because WorksheetFunction is not a property of the Sheet class. Your current line is interpreted as Sheets(ChartData).Application.Match which is valid, but only because most objects have an Application property that refers circularly to the Application object. Also, it is not always desirable to use the shortest line of code possible. Especially be careful about using unqualified object references, such as your Rows(1) without the leading ., unless you are sure you know how it is interpreted under all circumstances. There are quite a few objects that Excel usues complicated logic to determine what to apply them to when you don't specify (and the logic isn't always documented). Similarly, be careful about using assuming you know what the active workbook or worksheet is. The best practice, unless your code is specifically relevant to WHATEVER workbook or worksheet is active, is to specify what the workbook and worksheet. If you remove the Application from Application.WorksheetFunction.Match, that's fine (I would), and many people -- even reknowned experts -- refer to worksheet functions by eliminating WorksheetFunction, as you did: Application.Match, but I prefer not to: (1) it is undocumented. Worksheet functions are not documented as methods of the
$$Excel-Macros$$ Fwd:
Can you help on below. I want to convert the data in the big table below in the Following Format..Any formulas to do that? I Ozip.DestzipM 8810. 7188 33.8 8816. 7188 28.07 And so on I want to convert below big table into the format as above. Any formulas to do that? Pls look at bold ones below . They become adjacent to one another in the above format 7188 2726 6060 752 900 8810 33.8 500.02 799.95 1507.16 2739.68 8816 28.07 505.46 786.82 1512.6 2746.54 24112 489.26 62.71 706.74 1124.38 2466.52 27265 522.85 7.02 714.61 1104.09 2446.23 28139 657.92 141.86 669.02 977.94 2340.07 28645 626.99 97.11 691.54 1032.01 2374.15 28645 626.99 97.11 691.54 1032.01 2374.15 28677 586.43 68.12 715.87 1054.37 2396.5 32773 1050.3 594.93 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ REG:$$Column to table formula now working
Sir,(All forum members) Re: $$Excel-Macros$$ REG:$$Column to table formula Now working It is fine.Working well.Thank you very much for your timely help and guidence. Karunanithi R From: Sourabh Salgotra rhtdmja...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 5:31 PM Subject: Re: $$Excel-Macros$$ REG:$$Column to table formula not working chk this one. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Formula for Unique Values with Condition
Hi All, In the attached file I have names of the people who left the company. I need a formula to Extract the names of the Team Leader's and Team Manager's, where in Column C have Agent. I mean, I want to get only those Names, where they have attrition for Agent Request you to please help me with a Formula not a Macro.. -- Thanks Regards, Kiran -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Unique.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ format a cell
Now see the attached file. Aamir Shahzad On Wed, Mar 28, 2012 at 11:07 AM, LAKSHMAN PRASAD lakshman_...@yahoo.comwrote: Dear Noorain, I think he want 532, and I'd like to be converted to 05:32 *From:* NOORAIN ANSARI noorain.ans...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Wednesday, March 28, 2012 7:55 AM *Subject:* Re: $$Excel-Macros$$ format a cell Dear Pawel. Please try it.. =TIME(LEFT(C4,1),RIGHT(C4,2),0) -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 7:35 PM, pawel lupinski lupins...@yahoo.comwrote: Hi All, I need your help on this simple task i don't know how to do this and for you it will be easy. I'd like to input eg 532, and I'd like to be converted to 05:32 or input 5.32 and be converted to 5:32. I've tried to change cell format like hh:mm, [hh]:mm but its not working what I've received as outcome is data and time format like 12768:00 Can you help me, please. Regards, Pawel -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Regards, Aamir Shahzad -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Book1.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Formula for Unique Values with Condition
Why not just use datafilterautofilter see att Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Kiran Kancharla Sent: Wednesday, March 28, 2012 1:43 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Formula for Unique Values with Condition Hi All, In the attached file I have names of the people who left the company. I need a formula to Extract the names of the Team Leader's and Team Manager's, where in Column C have Agent. I mean, I want to get only those Names, where they have attrition for Agent Request you to please help me with a Formula not a Macro.. -- Thanks Regards, Kiran -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Unique.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Fwd:
more info on the AFTER look. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ram Sent: Wednesday, March 28, 2012 12:56 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: Can you help on below. I want to convert the data in the big table below in the Following Format..Any formulas to do that? I Ozip.DestzipM 8810. 7188 33.8 8816. 7188 28.07 And so on I want to convert below big table into the format as above. Any formulas to do that? Pls look at bold ones below . They become adjacent to one another in the above format 7188 2726 6060 752 900 8810 33.8 500.02 799.95 1507.16 2739.68 8816 28.07 505.46 786.82 1512.6 2746.54 24112 489.26 62.71 706.74 1124.38 2466.52 27265 522.85 7.02 714.61 1104.09 2446.23 28139 657.92 141.86 669.02 977.94 2340.07 28645 626.99 97.11 691.54 1032.01 2374.15 28645 626.99 97.11 691.54 1032.01 2374.15 28677 586.43 68.12 715.87 1054.37 2396.5 32773 1050.3 594.93 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Formula for Unique Values with Condition
Hi Don, I totally agree. I can use auto filter or advanced filter to get the out put. This report is a daily report for me and there will be lot of additions and deletions will be there. I need a formula to get the output... Thanks, Kiran Sent on my BlackBerry® from Vodafone Thanks Regards. Kiran -Original Message- From: dguillett1 dguille...@gmail.com Sender: excel-macros@googlegroups.com Date: Wed, 28 Mar 2012 14:07:13 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Formula for Unique Values with Condition Why not just use datafilterautofilter see att Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Kiran Kancharla Sent: Wednesday, March 28, 2012 1:43 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Formula for Unique Values with Condition Hi All, In the attached file I have names of the people who left the company. I need a formula to Extract the names of the Team Leader's and Team Manager's, where in Column C have Agent. I mean, I want to get only those Names, where they have attrition for Agent Request you to please help me with a Formula not a Macro.. -- Thanks Regards, Kiran -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Tab Strip or Multi page problem
Was playing around with Tab Strip and Multi Page but on each every time I add a check box on the tab it disappears the minute I take in out of design mode..Anyone know what I am doing wrong here thanks Bill -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Formula for Unique Values with Condition
Why not add a date column and still use autofilter. Why a formula when a macro would be so much better. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Kiran Kancherla Sent: Wednesday, March 28, 2012 2:12 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Formula for Unique Values with Condition Hi Don, I totally agree. I can use auto filter or advanced filter to get the out put. This report is a daily report for me and there will be lot of additions and deletions will be there. I need a formula to get the output... Thanks, Kiran Sent on my BlackBerry® from Vodafone Thanks Regards. Kiran From: dguillett1 dguille...@gmail.com Sender: excel-macros@googlegroups.com Date: Wed, 28 Mar 2012 14:07:13 -0500 To: excel-macros@googlegroups.com ReplyTo: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Formula for Unique Values with Condition Why not just use datafilterautofilter see att Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Kiran Kancharla Sent: Wednesday, March 28, 2012 1:43 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Formula for Unique Values with Condition Hi All, In the attached file I have names of the people who left the company. I need a formula to Extract the names of the Team Leader's and Team Manager's, where in Column C have Agent. I mean, I want to get only those Names, where they have attrition for Agent Request you to please help me with a Formula not a Macro.. -- Thanks Regards, Kiran -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.****** FIVE YEARS COMPLETION******THANK YOU******
congrats noorain bhai..!!Ashish Saxena (ASE) DelhiClaris Lifesciences Ltd.Mb :- 9871140113From: NOORAIN ANSARI lt;noorain.ans...@gmail.comgt;Sent: Mon, 26 Mar 2012 23:24:39 To: excel-macros@googlegroups.comSubject: Re: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.** FIVE YEARS COMPLETION**THANK YOU**Thanks Ayush... 2012/3/24 Ayush Jain lt;jainayus...@gmail.comgt; You always rock Noorain. This is really a great skill. nbsp; Thanks for writing poem and your great contribution to forum nbsp; -Ayush Jain On Thursday, 22 March 2012 18:42:08 UTC, NOORAIN ANSARI wrote: जहाँ Excel के दीवानों की, nbsp;है बहूत घनी आबादी. जहाँ Question,Answer करने की,है खूब आज़ादी. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; जहाँ के पोस्ट को follow कर-कर के,कितनो का Career खुशहाल होnbsp;nbsp;गया. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; दोस्तों उस अपने Excel-Macro ग्रुप काnbsp;उम्र,nbsp; अब 5 साल हो गया. चाहे सुबह हो या शाम, चाहे दिन हो या रात. हर समय होती है यहाँ पोस्टिंग की बरसात. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; ये उपवन सजा है दोस्तों, कई Talented फूलों से. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; यहाँ शीत युद्ध चलती है अक्सर Excel के फर्मुलों से. Logic की खेती होती है यहाँ solutions के खलिहानों में. यहाँ मदद की होड़ मची रहती है EXCEL के दिवानो में.. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; जिसने पा लिया साथ इसnbsp;ग्रुपnbsp;का, समझोnbsp;निहाल हो गया.nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; दोस्तों अपने Excel-Macro ग्रुप काnbsp;उम्र अब 5 साल हो गया. Haseeb,SAM,DON,Ashish जैसी हस्ती यहाँ पे बसती है.Man of BT Krishna के solution को दुनिया तरसती है.nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; ASA,Rajan,Dilip,Maries सब के सब कोहिनूर है.nbsp; nbsp; nbsp; nbsp; nbsp; Venkat,Sunny,Abhishekh भी कम नहीं मशहूर है. और भी यहाँ बहूत सारी शख्सियत है निराली.Ayush बाबू करते है इस बगीयाnbsp; की रखवाली.nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp;nbsp; येnbsp; forumnbsp; अब मिशाल नहीं रहा, बल्कि बेमिशाल हो गया. nbsp; nbsp; nbsp; nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; दोस्तों अपने Excel-Macro ग्रुप काnbsp;उम्र अब 5 साल हो गया. -- Thanks amp; regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ nbsp; -- FORUM RULES (986+ members already BANNED for violation)nbsp;1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.nbsp;2) Don't post a question in the thread of another member.nbsp;3) Don't post questions regarding breaking or bypassing any security measure.nbsp;4) Acknowledge the responses you receive, good or bad.nbsp;5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. nbsp;NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.nbsp;--To post to this group, send email to excel-macros@googlegroups.com-- Thanks amp; regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ nbsp; -- FORUM RULES (986+ members already BANNED for violation)nbsp;1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.nbsp;2) Don't post a question in the thread of another member.nbsp;3) Don't post questions regarding breaking or bypassing any security measure.nbsp;4) Acknowledge the responses you receive, good or bad.nbsp;5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. nbsp;NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.nbsp;--To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
$$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
Hi, I'm new to VBA and I'm posting this because I've been tearing my hair out over this all day, so I hope someone has seen this before and can point me in the right direction. I have created a worksheet change event which hides some columns according to a string that is chosen from a list in cell C3. This all works fine until I add any of the Range statements into the Select Case structure. When I select one of the three values of the cell from the Data Validation drop-down list I have in cell C3, I receive: Run-time error message: Method 'Hidden' of object 'Range' failed and then Excel crashes! I am using Excel 2007. I would be very grateful if you could look at the following code and help me. Best regards, Anil Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String DC36U = Tekelec Eagle XG 870-3040-06 (DC) DC44U = Tekelec Eagle XG 870-3068-06 (DC) AC42U = Tekelec Eagle XG 870-3042-06 (AC) ' Turn off screen updating Application.ScreenUpdating = False ' Assign an object reference to the Target variable Set Target = Range(C3) ' Test the value in Target and display the ' appropriate frame layout Select Case Target Case DC36U Columns.Hidden = False Columns(G:L).EntireColumn.Hidden = True *Range(C5) = DC* Case DC44U Columns.Hidden = False Columns(E:G).EntireColumn.Hidden = True Columns(J:L).EntireColumn.Hidden = True *Range(C5) = DC* Case AC42U Columns.Hidden = False Columns(E:J).EntireColumn.Hidden = True *Range(C5) = AC* End Select End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file
Dear Members I m new to this group, Please help me to easy way of converting DBF file to CSV file Thanks Govind Mori -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Reading custom format as it appears in a cell
I have an excel file each cell may have different custom formats such as 000 or 0940101 or 0080100 etc. I would like to count number of characters in each cell. If I use len() function it retreives the length as it appears in the formula bar and not the maked characters by custom format. Please tell me how to read masked characters by custom format. Thanks Regards, Ravi Kumar Vandavasi -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Email Sheet with message
Hi Darwin, If you are not able to see mentioned reference Microsoft Outlook 12.0 Object Library in your machine, then probably you can get it by installing VB 6.0 edition in your computer. Mangesh. On Wed, Mar 28, 2012 at 3:49 PM, Jorge Marques leote.w...@gmail.com wrote: Talal, i´m also a beginner, i´m using some books, but i haven´t found any extensive one in VBA, i use recording methods to see what the code performs, and use other posts to see what it returns in the excel provided, it has helped me a lot improving my skills, i notice that i not still able to build code by myself, but know, I can read and understand what it means, so i can build macros but taking pieces from other macros and adapt to what i pretend, and when i have doubts this forum is 100% perfect. i hope someday i can be the one responding to solutions and not asking for them :), wish you the best in this Excel world. 2012/3/28 Darwin Chan darwin.chankaw...@gmail.com Hi all, I found this thread which is really useful, however, I couldnt find the Tools-Reference-Microsoft Outlook 12.0 Object Library as mentioned by Noorain MS Excel 2000 version How could I get access to the Library? 2012/3/20 NOORAIN ANSARI noorain.ans...@gmail.com Dear Talal, It's really simple but it takes some time. Spend 2-3 hours a day, every day, in these forums reading posts and trying the solutions provided. Experiment! Try to Create simple macro by help of record macro,Google,Books. Hope your Excel-VBA will surely improve. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Wed, Mar 14, 2012 at 12:39 PM, Muhammad Talal Akbar sh.talal.ak...@gmail.com wrote: Hi Mr. Ansari how are you? My dear i need your help can u guide me; how can i improve my skills in excel and VBA regards, On Wed, Mar 14, 2012 at 11:20 AM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Please add Outlook library before run the code Tools-Reference-Microsoft Outlook 12.0 Object Library. On Wed, Mar 14, 2012 at 11:13 AM, LearnExcel sendse...@gmail.comwrote: i'm using the following code to send email how do i add something to the body? does anyone know if its possible to add a message to the email? ThisWorkbook.Sheets(1).Copy With ActiveWorkbook .SendMail Recipients:=emailaddr...@df.com, _ Subject:=Try Me) .Close SaveChanges:=False End With *End Sub* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
$$Excel-Macros$$ Test to see if cell value starts with a D
This a probably a simple question, but I am new to VBA... How do I test to find cells that contain text starting with the letter D? Thank you Pres -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ format a cell
Hi All, thanks a lot for everyone, special to Aamir yes this what I was looking for THANKS a lot again. Regards, Pawel From: Aamir Shahzad aamirshahza...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:52 PM Subject: Re: $$Excel-Macros$$ format a cell Now see the attached file. Aamir Shahzad On Wed, Mar 28, 2012 at 11:07 AM, LAKSHMAN PRASAD lakshman_...@yahoo.comwrote: Dear Noorain, I think he want 532, and I'd like to be converted to 05:32 From: NOORAIN ANSARI noorain.ans...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:55 AM Subject: Re: $$Excel-Macros$$ format a cell Dear Pawel. Please try it.. =TIME(LEFT(C4,1),RIGHT(C4,2),0) -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 7:35 PM, pawel lupinski lupins...@yahoo.com wrote: Hi All, I need your help on this simple task i don't know how to do this and for you it will be easy. I'd like to input eg 532, and I'd like to be converted to 05:32 or input 5.32 and be converted to 5:32. I've tried to change cell format like hh:mm, [hh]:mm but its not working what I've received as outcome is data and time format like 12768:00 Can you help me, please. Regards, Pawel-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Regards, Aamir Shahzad -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Re: $$Excel-Macros$$ Formula for Unique Values with Condition
Hi Kiran, the easiest way of doing it is pivot table (so you can manage as you want) both request attached. From the macro point of view I can't help you with, but I've got your piont sometimes you want to see it to be able to learn it. Regards, Pawel ps I couldn't send you attachment - limitation on your phone due to size -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Test to see if cell value starts with a D
sub findd() dim c as range for each c in range(“a2:a22”) if ucase(left(c,1))=”D” then msgbox c next c end sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Preston Moore Sent: Wednesday, March 28, 2012 12:52 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Test to see if cell value starts with a D This a probably a simple question, but I am new to VBA... How do I test to find cells that contain text starting with the letter D? Thank you Pres -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ format a cell
what about Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: pawel lupinski Sent: Wednesday, March 28, 2012 3:54 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ format a cell Hi All, thanks a lot for everyone, special to Aamir yes this what I was looking for THANKS a lot again. Regards, Pawel From: Aamir Shahzad aamirshahza...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:52 PM Subject: Re: $$Excel-Macros$$ format a cell Now see the attached file. Aamir Shahzad On Wed, Mar 28, 2012 at 11:07 AM, LAKSHMAN PRASAD lakshman_...@yahoo.com wrote: Dear Noorain, I think he want 532, and I'd like to be converted to 05:32 From: NOORAIN ANSARI noorain.ans...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, March 28, 2012 7:55 AM Subject: Re: $$Excel-Macros$$ format a cell Dear Pawel. Please try it.. =TIME(LEFT(C4,1),RIGHT(C4,2),0) -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ On Tue, Mar 27, 2012 at 7:35 PM, pawel lupinski lupins...@yahoo.com wrote: Hi All, I need your help on this simple task i don't know how to do this and for you it will be easy. I'd like to input eg 532, and I'd like to be converted to 05:32 or input 5.32 and be converted to 5:32. I've tried to change cell format like hh:mm, [hh]:mm but its not working what I've received as outcome is data and time format like 12768:00 Can you help me, please. Regards, Pawel -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Regards, Aamir Shahzad -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in
Re: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed
'Assumes this in DV list 'DC36U 'DC44U 'AC42U Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String '??DC36U = Tekelec Eagle XG 870-3040-06 (DC) '??DC44U = Tekelec Eagle XG 870-3068-06 (DC) '??AC42U = Tekelec Eagle XG 870-3042-06 (AC) Application.ScreenUpdating = False ' Test the value in Target and display the ' appropriate frame layout Columns.Hidden = False Range(C5) = DC Select Case Target Case DC36U Columns(G:L).Hidden = True Case DC44U Range(E1:G1,J1:l1).EntireColumn.Hidden = True Case AC42U Columns(E:J).EntireColumn.Hidden = True Range(C5) = AC End Select Application.ScreenUpdating = True End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Anil Pandit Sent: Wednesday, March 28, 2012 11:04 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Run-time error message: Method 'Hidden' of object 'Range' failed Hi, I'm new to VBA and I'm posting this because I've been tearing my hair out over this all day, so I hope someone has seen this before and can point me in the right direction. I have created a worksheet change event which hides some columns according to a string that is chosen from a list in cell C3. This all works fine until I add any of the Range statements into the Select Case structure. When I select one of the three values of the cell from the Data Validation drop-down list I have in cell C3, I receive: Run-time error message: Method 'Hidden' of object 'Range' failed and then Excel crashes! I am using Excel 2007. I would be very grateful if you could look at the following code and help me. Best regards, Anil Private Sub Worksheet_Change(ByVal Target As Range) Dim DC36U As String Dim DC44U As String Dim AC42U As String DC36U = Tekelec Eagle XG 870-3040-06 (DC) DC44U = Tekelec Eagle XG 870-3068-06 (DC) AC42U = Tekelec Eagle XG 870-3042-06 (AC) ' Turn off screen updating Application.ScreenUpdating = False ' Assign an object reference to the Target variable Set Target = Range(C3) ' Test the value in Target and display the ' appropriate frame layout Select Case Target Case DC36U Columns.Hidden = False Columns(G:L).EntireColumn.Hidden = True Range(C5) = DC Case DC44U Columns.Hidden = False Columns(E:G).EntireColumn.Hidden = True Columns(J:L).EntireColumn.Hidden = True Range(C5) = DC Case AC42U Columns.Hidden = False Columns(E:J).EntireColumn.Hidden = True Range(C5) = AC End Select End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Reading custom format as it appears in a cell
Do you mean values 0 such as 2 for the last example? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ravi Kumar Vandavasi Sent: Tuesday, March 27, 2012 10:39 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Reading custom format as it appears in a cell I have an excel file each cell may have different custom formats such as 000 or 0940101 or 0080100 etc. I would like to count number of characters in each cell. If I use len() function it retreives the length as it appears in the formula bar and not the maked characters by custom format. Please tell me how to read masked characters by custom format. Thanks Regards, Ravi Kumar Vandavasi -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file
https://www.google.com/#hl=ensugexp=epsugrstmags_nf=1tok=yQU8bSdwbrVg0d2-9kIpdgcp=17gs_id=25xhr=tq=excel:+dbf+to+csvpf=poutput=searchsclient=psy-aboq=excel:+dbf+to+csvaq=aqi=aql=gs_l=pbx=1bav=on.2,or.r_gc.r_pw.r_cp.r_qf.,cf.osbfp=7fb1484f2d5d4b11biw=1066bih=670 Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Govind Mori Sent: Wednesday, March 28, 2012 5:28 AM To: excel-macros Subject: $$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file Dear Members I m new to this group, Please help me to easy way of converting DBF file to CSV file Thanks Govind Mori -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file
Hi Govind, At last check, MS Access (if you have it) can still import and export both DBF and CSV files. Older Excel versions (2003 and older I believe) can import/export from DBF natively. CSV is still supported. Also, free OpenOffice and LibreOffice have that capability. It should be possible to do the same from VBA in Excel using ADO, but don't have the code for you. A search turns up some converters that seem to be available for free: http://www.alexnolan.net/software/dbf.htm - freeware http://sourceforge.net/projects/dbfconverter/ - open source http://sourceforge.net/projects/dbmt/ - open source http://www.vlsoftware.net/exportizer/index.html - no-cost version? https://www.libreoffice.org/ - open source http://www.openoffice.org/ - open source Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Govind Mori Sent: Wednesday, March 28, 2012 3:29 AM To: excel-macros Subject: $$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file Dear Members I m new to this group, Please help me to easy way of converting DBF file to CSV file Thanks Govind Mori -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN
Thanks a lot don From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Wednesday, March 28, 2012 6:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN Should do it. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Siraj Momin (BTG) mailto:smo...@ccc.ae Sent: Wednesday, March 28, 2012 2:36 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN Dear Don, Your macro did the job, need a further help if you can spare time for this, I need to make borders as shown in the sample output sheet as the data is big I want to automate the process of border.. Also in the file raw data sheet to be converted as show in the sample output sheet. It will be a big help for me... Siraj From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Tuesday, March 27, 2012 4:52 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN Although usually not maybe in this case deleting one row/column at a time from the bottom up or last col to left ? Worth a try. Sub DeleteBlankRowsAndColumnsOneAtATime() Dim i As Long Application.Calculation = xlManual For i = Cells.SpecialCells(xlLastCell).Row To 1 Step -1 If Application.CountA(Rows(i)) 1 Then Rows(i).Delete Next i For i = Cells.SpecialCells(xlLastCell).Column To 1 Step -1 If Application.CountA(Columns(i)) 1 Then Columns(i).Delete Next i Application.Calculation = xlAutomatic End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Garcia mailto:yiqiang@gmail.com Sent: Monday, March 26, 2012 9:25 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN Sub delrowcolumn() Dim Area_ As Range Dim Rng1 As Range Dim Rng2 As Range Dim Ro As Long Dim Col As Integer Dim lstRo As Long Dim lstCol As Integer If [a1] = Then [a1] = 1 Set Area_ = ActiveSheet.UsedRange [a1].ClearContents lstRo = Area_.rows.Count lstCol = Area_.Columns.Count Application.ScreenUpdating = False For Ro = 1 To lstRo If Application.CountA(Area_.rows(Ro)) = 0 Then If Rng1 Is Nothing Then Set Rng1 = rows(Ro) Else Set Rng1 = Union(Rng1, rows(Ro)) End If End If Next For Col = 1 To lstCol If Application.CountA(Area_.Columns(Col)) = 0 Then If Rng2 Is Nothing Then Set Rng2 = Columns(Col) Else Set Rng2 = Union(Rng2, Columns(Col)) End If End If Next On Error Resume Next Rng1.EntireRow.Delete Rng2.EntireColumn.Delete Set Area_ = Nothing Set Rng1 = Nothing Set Rng2 = Nothing Application.ScreenUpdating = True End Sub On Monday, March 26, 2012 8:16:10 PM UTC+8, Siraj Momin (BTG) wrote: Dear Friends I need to delete blank row and blank column from the attached file with the help of macro because data is large. Thanks Siraj -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to
Re: $$Excel-Macros$$ want to add our own custom button and Tab in Excel 2007 name in place of add-in
Dear Divakar, Please use below code to add custom button and Tab Option Explicit 'Add a new menu to Worksheet Menu Bar Public Sub AddCustomMenu() Dim cbr As CommandBar Dim ctlMenu As CommandBarControl 'Add new menu control Set cbr = Application.CommandBars(Worksheet Menu Bar) Set ctlMenu = cbr.Controls.Add(Type:=msoControlPopup) 'Add controls to new menu control With ctlMenu .Caption = Custom With .Controls.Add(Type:=msoControlButton) .Caption = Show Data Form .OnAction = ShowDataForm End With With .Controls.Add(Type:=msoControlButton) .Caption = Print Data List .OnAction = PrintDataList End With With .Controls.Add(Type:=msoControlButton) .Caption = Sort Names Ascending .BeginGroup = True .OnAction = SortList .Parameter = Asc End With With .Controls.Add(Type:=msoControlButton) .Caption = Sort Names Descending .OnAction = SortList .Parameter = Dsc End With With .Controls.Add(Type:=msoControlButton) .Caption = Show Products .OnAction = 'ShowProduct Apple, 3, 4' End With End With End Sub 'Delete Custom menu Public Sub RemoveCustomMenu() Dim cbr As CommandBar On Error Resume Next Set cbr = CommandBars(WorkSheet Menu Bar) cbr.Controls(Custom).Delete End Sub On Wed, Mar 28, 2012 at 9:22 PM, Divaker Pandey divake...@gmail.com wrote: Hi Expert, I want to add our own custom button and Tab in Excel 2007 name in place add-in. Divaker -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Tab Strip or Multi page problem
Dear Thomp, Please share your workbook with group. On Thu, Mar 29, 2012 at 1:02 AM, Thomp williamth...@gmail.com wrote: Was playing around with Tab Strip and Multi Page but on each every time I add a check box on the tab it disappears the minute I take in out of design mode..Anyone know what I am doing wrong here thanks Bill -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Formula for Unique Values with Condition
Hello Kiran, In G4 with CTRL+SHIFT+ENTER, rather than just ENTER =IFERROR(INDEX(D$2:D$498,MATCH(1,IF(ISNA(MATCH(D$2:D$498,G$3:G3,0)),IF($C$2:$C$498=Agent,1)),0)),) Then copy across down. if you have huge list, this will slow down the sheet performance. ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ how to convert dbf III (Dos Based Fox-pro 2.5) file to CSV file
Dear Govind, Please try through below link, http://www.dbf2002.com/csv-converter/ On Wed, Mar 28, 2012 at 3:58 PM, Govind Mori mori.gov...@gmail.com wrote: Dear Members I m new to this group, Please help me to easy way of converting DBF file to CSV file Thanks Govind Mori -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Reading custom format as it appears in a cell
Dear Ravi, Please use =REPT(0,15-LEN(A2))A2 instead of Custom format.. then you can easly count lenght.. See attached sheet. On Tue, Mar 27, 2012 at 9:09 PM, Ravi Kumar Vandavasi friendswrite...@gmail.com wrote: I have an excel file each cell may have different custom formats such as 000 or 0940101 or 0080100 etc. I would like to count number of characters in each cell. If I use len() function it retreives the length as it appears in the formula bar and not the maked characters by custom format. Please tell me how to read masked characters by custom format. Thanks Regards, Ravi Kumar Vandavasi -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Ravi_Example.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ want to add our own custom button and Tab in Excel 2007 name in place of add-in
Hi Noorain, Regarding the code provided from above, do the custom control button and tab include all operations too? For example, the sorting button, the operations include in the following code? I would like to learn more from this. .Caption = Sort Names Descending .OnAction = SortList .Parameter = Dsc 2012/3/29 NOORAIN ANSARI noorain.ans...@gmail.com See attached add-in's coding for reference On Wed, Mar 28, 2012 at 9:22 PM, Divaker Pandey divake...@gmail.comwrote: Hi Expert, I want to add our own custom button and Tab in Excel 2007 name in place add-in. Divaker -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Re: Formula for Unique Values with Condition
* * * * Hi Noorain/Haseeb, Perfect answers. This is what I was looking for... Thanks... Kiran On Thu, Mar 29, 2012 at 9:06 AM, Haseeb A haseeb.avarak...@gmail.comwrote: Hello Kiran, In G4 with CTRL+SHIFT+ENTER, rather than just ENTER =IFERROR(INDEX(D$2:D$498,MATCH(1,IF(ISNA(MATCH(D$2:D$498,G$3:G3,0)),IF($C$2:$C$498=Agent,1)),0)),) Then copy across down. if you have huge list, this will slow down the sheet performance. ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks Regards, Kiran 9920456606 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ checkbox query
Dear all, Sorry for asking late. I use the code from KRIS and put in SHANKAR file, however, the below error happened. Could SHANKAR please share your new file with KRIS code? Also, I found there is some output in the Intermediate window, could anyone provide some information/ resources on how to use it? 2012/3/22 Shankar Bheema shankar.n...@gmail.com Dear Noorain The query solved by Mr KRIS krishnak...@gmail.com is catering my need. Thank you for your cooperation my dear friend. On Tue, Mar 20, 2012 at 6:02 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Shankar, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 20, 2012 at 11:32 AM, Shankar Bheema shankar.n...@gmail.comwrote: Dear experts I have attached an excel file containing data of qualification. On the userform I created check boxes with similar to the datasheet data. I need action like, only the tickmarked check box data only shown on the data sheet all other have to go in hidden mode (even in print also). waiting for your reply. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Fwd: $$Excel-Macros$$ Service Count problem
-- Forwarded message -- From: Shankar Bheema shankar.n...@gmail.com Date: Sat, Mar 24, 2012 at 11:00 AM Subject: $$Excel-Macros$$ Service Count problem To: excel-macros@googlegroups.com Dear Experts In the userform of the attached workbook contains Date of Joining text box and Date of Retirement textbox. The difference of both the values should come to 23 yrs 01 mth 29 days but it is showing as 23 yrs 01 mth 01 day In my office procedure resultant date should be given 1 bonus day with this, the result should be 23 yrs 01 mnth 30 days In general, as per my office rules 30 days will be considered as 1 month for calculation of salary Final result should come like 23 yrs 02 mths 00 days Pls rectify the problem. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com total service problem.xls Description: MS-Excel spreadsheet
