Re: $$Excel-Macros$$ In OnAction macro caller check or parameter
Hello, I tried this Application.Caller function but it returned not a string it returned a variant object. So I don't know how to get the button from the variant. I create the button with Set MenuItem = Menu2.Controls.Add(Type:=msoControlButton) MenuItem.Caption = aSheet.Cells(row, col_level3).text MenuItem.OnAction = "FilterButtonClick" & CStr(row) And the onAction macro is then Sub FilterButtonClick9() Dim aObject As Variant Dim hString As String aObject = Application.Caller hString = TypeName(aObject) ' FilterButtonClick (9) End Sub It's somehow strange that according on-line documentation the function never returns a variant. Regards Erwin -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ MS Excel 2003: odd sheet protection/cell locking behavior
Yes, that's true. Can you clear the data from the file and send me the BLANK sheet? I'm really only trying to see if the lock/unlock is being set properly. Paul From: Jeff To: MS EXCEL AND VBA MACROS Sent: Wed, March 17, 2010 7:11:26 PM Subject: Re: $$Excel-Macros$$ MS Excel 2003: odd sheet protection/cell locking behavior On Mar 17, 11:57 am, Paul Schreiner wrote: > Can you send me the file? No, sorry I can't. It's got confidential info. So, stupid question, but just wanna make sure ... with Locking checked and Sheet Protection on, those cells should definitely not be accepting user input, right? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Excel Formula Required
Dear All, Any one can Assist me attached file query It's something like reverse Lookup -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Query.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Share Tips and Tricks
Hello Ayush You have started a good topic,I think in this topic we can share our some tips and tricks.I am starting a topic ... '' Diffrence between VBA & .NET '' ''' The short answer is that it would be very difficult to do VBA programming currently within Visual Studio.Net. Each MS Office product, however, supplies its own VBA development and debugging facility (though typically the VBA help file is not installed under the default options). Up to now VBA (Visual Basic for Applications) has been an "interpreted" version of Visual Basic used in Microsoft's Office suite products, closely related to the Visual Basic "script" that is used in ASP and Windows Scripting Host. As such VBA is highly dependent on the Variant datatype (all variables are variants) and on COM. VB.Net eliminates the Variant datatype and moves the Visual Basic "theater" away from COM (however without burning bridges "behind" at this point). Microsoft has slated new tools for Excel and Word automation based on the .Net infrastructure to roll out with Office 2003 in "late summer". See here for the product announcement: [Visual Studio .NET and Office Development] http://msdn.microsoft.com/vstudio/office/ [Visual Studio Tools for Office] http://msdn.microsoft.com/library/en-us/odc_vsto2003_ta/html/VSTOIntro.asp One thing that VS.Net could be used for, related to VBA development, is the creation of OLE automation/ActiveX components using native C++. Provided that you build the automation interfaces (which is made fairly painless by the C++ wizards), these components can then be called in VBA once the appropriate references are added to the VBA "project". Admittedly that would not leverage either the simplicity of the VB syntax nor the extensive .Net runtime library, so it's a rather silly suggestion. It is also possible (but nontrivial) to call .Net libraries from within compiled VB6 code, but this is even further afield from what you want to do. Please post a request for clarification if one is needed. ''' On Thu, Mar 18, 2010 at 9:56 AM, Ayush wrote: > Hello Everyone, > > I request you all to share any tips or tricks that you have about MS > Excel in the group. It really helps. > > The tip can be anything like formula, most used calculation, excel > trick, hidden truth or could be anything which you think can be done > in an easier way. > > It can be any bug of excel also. Anything about excel that you > knowanything > > Sharing just increases the knowledge. > > Keep Posting... > > > Best regards, > Ayush Jain > Group Owner > > -- > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- Thanks Regards Shyam Software Engineer '' When one door of happiness closes, another opens; but often we look so long at the closed door that we do not see the one which has been opened for us. - Helen Keller '' Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres, aber oft schauen wir so lange auf die geschlossene Tür, dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller ''
$$Excel-Macros$$ Biometric reader in excel
hello friends, I have a biometric reader from Microsoft (Microsoft fingerprint reader) I can use it to identify people via Excel VBA? Att, Ulisses Flores Baruch Ben Avraham לוהים הוא האש מכלה -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Share Tips and Tricks
Hello All Some of bug i have remember.trying to share with you. On Thu, Mar 18, 2010 at 9:56 AM, Ayush wrote: > Hello Everyone, > > I request you all to share any tips or tricks that you have about MS > Excel in the group. It really helps. > > The tip can be anything like formula, most used calculation, excel > trick, hidden truth or could be anything which you think can be done > in an easier way. > > It can be any bug of excel also. Anything about excel that you > knowanything > > Sharing just increases the knowledge. > > Keep Posting... > > > Best regards, > Ayush Jain > Group Owner > > -- > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- Thanks Regards Shyam Software Engineer '' When one door of happiness closes, another opens; but often we look so long at the closed door that we do not see the one which has been opened for us. - Helen Keller '' Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres, aber oft schauen wir so lange auf die geschlossene Tür, dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller '' -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe 1:Try to Write this in Ms excel 5123412341234 You will get Corresponding 51234123412349900 2:Try to write this and check out the answer =65536-2^(-37) 3:A2 = 0.833035291402691 B2==(A2^-2)*(2)--->ANSWER=2.882061172 A3 = 0.0721260089985885 B3==(A3^-2)*(2)--->ANSWER=384.4556009
Re: $$Excel-Macros$$ Share Tips and Tricks
Hi Ayush It is better idea. Please add an common file which will used by every one and time to time add their tips and then we have single file with lots of formula's and idea's Please everybody download common file, add your suggetion and formula and upload as reply on the our group mail. Thankx On 3/18/10, Ayush wrote: > > Hello Everyone, > > I request you all to share any tips or tricks that you have about MS > Excel in the group. It really helps. > > The tip can be anything like formula, most used calculation, excel > trick, hidden truth or could be anything which you think can be done > in an easier way. > > It can be any bug of excel also. Anything about excel that you > knowanything > > Sharing just increases the knowledge. > > Keep Posting... > > > Best regards, > Ayush Jain > Group Owner > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- Sudhir Verma Asstt. Mngr. A/c's 9211219649 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Formula Required
Try the following attached file This is a left lookup On Thu, Mar 18, 2010 at 5:01 AM, Venkatesan c wrote: > Dear All, > > Any one can Assist me attached file query It's something like reverse > Lookup > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Query(2).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Share Tips and Tricks
Personally, whenever I start a new job, or install a new version of Excel, the first thing I do is record the following macro. Sub CopyPasteSpecialValues() ' ' CopyPasteSpecialValues Macro ' Macro recorded 27/11/2008 ' Keyboard Shortcut: Ctrl+Shift+V ' Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub Most of my job involves data manipulation/extraction. To do this, I often write temporary functions which take existing raw data and turn it into more easily-readable formats. Since I share this data with sub-contractors and clients, I'm not permitted to release what is in effect, my employer's intellectual property. The macro allows me to highlight a range of cells, and with one keystroke, replace the existing functions with the values. On 18 Mar, 04:26, Ayush wrote: > Hello Everyone, > > I request you all to share any tips or tricks that you have about MS > Excel in the group. It really helps. > > The tip can be anything like formula, most used calculation, excel > trick, hidden truth or could be anything which you think can be done > in an easier way. > > It can be any bug of excel also. Anything about excel that you > knowanything > > Sharing just increases the knowledge. > > Keep Posting... > > Best regards, > Ayush Jain > Group Owner -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Pivot table on horizontal data
Hi, Is there any way of creating pivot table on horizontal data? Means the headings are in the rows (instead of columns) and data is in horizontal form instead of vertical. I can't use copy and transpose option because of macros and functions in the workbook. -- Regards, Ajay Varshney -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Share Tips and Tricks
Thanks group for your overwhelmed response on this topic. @Shyam : Thanks for sharing tips and bugs. you are excellent member of group. I am proud that you are part of this group. Keep Posting. @Sudhir : Thanks for idea but it appears tedious to edit the file and upload in the group. There will be two many copies. Very difficult to consolidate all tips into one. If you want consolidated output then we I can upload these tips on our group blog @ exceldailytip.blogspot.com . Here you will already find more than 50 tips published. @draco664 : Thanks for sharing Best Practice. I loved it. Best regards, Ayush Jain Group Owner. On Mar 18, 7:28 pm, draco664 wrote: > Personally, whenever I start a new job, or install a new version of > Excel, the first thing I do is record the following macro. > > Sub CopyPasteSpecialValues() > ' > ' CopyPasteSpecialValues Macro > ' Macro recorded 27/11/2008 > ' Keyboard Shortcut: Ctrl+Shift+V > ' > Selection.Copy > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > SkipBlanks _ > :=False, Transpose:=False > Application.CutCopyMode = False > End Sub > > Most of my job involves data manipulation/extraction. To do this, I > often write temporary functions which take existing raw data and turn > it into more easily-readable formats. > > Since I share this data with sub-contractors and clients, I'm not > permitted to release what is in effect, my employer's intellectual > property. The macro allows me to highlight a range of cells, and with > one keystroke, replace the existing functions with the values. > > On 18 Mar, 04:26, Ayush wrote: > > > Hello Everyone, > > > I request you all to share any tips or tricks that you have about MS > > Excel in the group. It really helps. > > > The tip can be anything like formula, most used calculation, excel > > trick, hidden truth or could be anything which you think can be done > > in an easier way. > > > It can be any bug of excel also. Anything about excel that you > > knowanything > > > Sharing just increases the knowledge. > > > Keep Posting... > > > Best regards, > > Ayush Jain > > Group Owner -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Formula Required
just applt vlook and get the data On 3/18/10, Venkatesan c wrote: > > Dear All, > > Any one can Assist me attached file query It's something like reverse > Lookup > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- Pankaj kumar choudhary Financial analyst PORTECK INDIA 9911103235 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Formula Required
Dear Venkatesan, Attached file has two solutions for your query. Let me know in case of any challenge(s). -- Thanks & Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India On Thu, Mar 18, 2010 at 2:31 PM, Venkatesan c wrote: > Dear All, > > Any one can Assist me attached file query It's something like reverse > Lookup > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Query-Reverse Vlookup by DILipandey.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Biometric reader in excel
hello friends, I have a biometric reader from Microsoft (Microsoft fingerprint reader) I can use it to identify people via Excel VBA? Att, Ulisses Flores Baruch Ben Avraham לוהים הוא האש מכלה -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ Re: Share Tips and Tricks
with Excel 2007 the Help options seem to be much better. Along with this a pretty extensive list of shortcut keys http://office.microsoft.com/client/helppreview.aspx?AssetID=HP100738481033&QueryID=2M8Hx7LZd&respos=5&rt=2&ns=EXCEL&lcid=1033&pid=CH100947761033 some of my favorites: CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar. CTRL+ENTER fills the selected cell range with the current entry and keeps that cell selected. CTRL+; Enters the current date. On Mar 18, 7:48 am, Ayush wrote: > Thanks group for your overwhelmed response on this topic. > > @Shyam : Thanks for sharing tips and bugs. you are excellent member of > group. I am proud that you are part of this group. Keep Posting. > > @Sudhir : Thanks for idea but it appears tedious to edit the file and > upload in the group. There will be two many copies. Very difficult to > consolidate all tips into one. > If you want consolidated output then we I can upload > these tips on our group blog @ exceldailytip.blogspot.com . Here you > will already find more than 50 tips published. > > @draco664 : Thanks for sharing Best Practice. I loved it. > > Best regards, > Ayush Jain > Group Owner. > > On Mar 18, 7:28 pm, draco664 wrote: > > > > > Personally, whenever I start a new job, or install a new version of > > Excel, the first thing I do is record the following macro. > > > Sub CopyPasteSpecialValues() > > ' > > ' CopyPasteSpecialValues Macro > > ' Macro recorded 27/11/2008 > > ' Keyboard Shortcut: Ctrl+Shift+V > > ' > > Selection.Copy > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > > SkipBlanks _ > > :=False, Transpose:=False > > Application.CutCopyMode = False > > End Sub > > > Most of my job involves data manipulation/extraction. To do this, I > > often write temporary functions which take existing raw data and turn > > it into more easily-readable formats. > > > Since I share this data with sub-contractors and clients, I'm not > > permitted to release what is in effect, my employer's intellectual > > property. The macro allows me to highlight a range of cells, and with > > one keystroke, replace the existing functions with the values. > > > On 18 Mar, 04:26, Ayush wrote: > > > > Hello Everyone, > > > > I request you all to share any tips or tricks that you have about MS > > > Excel in the group. It really helps. > > > > The tip can be anything like formula, most used calculation, excel > > > trick, hidden truth or could be anything which you think can be done > > > in an easier way. > > > > It can be any bug of excel also. Anything about excel that you > > > knowanything > > > > Sharing just increases the knowledge. > > > > Keep Posting... > > > > Best regards, > > > Ayush Jain > > > Group Owner- Hide quoted text - > > - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ Re: Cannot 'Break' broken link
It should tell you which cell contains the link. Go to it and delete it. You will need to hard code the data. On Mar 17, 3:31 pm, "~slacker~" wrote: > I need help in breaking data links to other excel workbooks. The excel > workbook I am using (excel 2007) has links to other workbooks that can > been seen by going to Data -> Edit Links > One of the links listed here does not point to a valid workbook and I > would like to break the link to get rid of the "Continue or Edit > Links" prompt that occurs when opening this workbook. > Using the "Break Link" button does not work as the link does not > appear to have a valid location. > > Is there another way to break links in a workbook? > > thanks in advance -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ Real popup without command bar
Hello, it seems not to be possible to program a real popup menu without the usage of a command bar. Is this true? I would like to create a button and when someone click on it a popup (like on the right click on items) is shown at the position of the button. With the command bars I have to create a bar and this is (normally) always visible and on a fixed position, may be even in the higher menu area together with the other bars. Does anyone know if it's possible to make "real popups" and if yes, give me a hint how to do? Thanks Erwin -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ Excel - sort with VBA problems
Hi everyone,
I have a program that generates Excel workbooks with multiple sheets
of data , and a function that create a chart each time it's called,
which happens 2 times per workbook. In order to make the chart, I have
to sort the data first, which I've tried a few different ways. No
matter which way I do it, get a "Run-time error '1004': Method 'Range'
of object '_Global' failed" error on the line that does the sorting
when this function is called the 3rd time (after the 2nd workbook is
created).
I think this is a result of having multiple workbooks and not checking
to make sure which one the data in question is coming from, but the
newest workbook is always the active one so I'm not sure why this
matters. Any suggestions for how I can resolve this?
Here is the code:
Private Function CreateChart(sheet As excel.Worksheet, chartsize As
Integer, _
sheet_name As String, chart_name As String, xaxis_label As String,
yaxis_label As String)
Dim data_range As excel.Range
Dim i As Integer
Dim num_srs As Integer
Dim num_jrs As Integer
num_srs = tracker.NumSeniors
num_jrs = tracker.NumJuniors
'sort the data
Range("A2:B" & chartsize).Sort Key1:=Range("B2"),
Order1:=xlAscending, Key2:=Range("A2"), _
Order2:=xlYes, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
'calculate average and standard deviation
sheet.Cells(1, 6).value = "Average"
sheet.Cells(1, 7).value = "=average($B$2:$B$" & chartsize & ")"
sheet.Cells(2, 6).value = "Standard Deviation"
sheet.Cells(2, 7).value = "=stdev($B$2:$B$" & chartsize & ")"
'calculate CDF
For i = 1 To chartsize - 1
sheet.Cells(i + 1, 3).value = "=NORMDIST($B" & i + 1 & ", $G
$1, $G$2, TRUE)"
Next
sheet.Columns.AutoFit
'choose the data range for the chart and add chart to new sheet
Set data_range = sheet.Range("B2:B" & chartsize, "C2:C" &
chartsize)
m_workbook.Charts.Add
'format the chart
With m_workbook.ActiveChart
.ChartType = xlXYScatterLines
.SetSourceData data_range, xlColumns
.Location xlLocationAsNewSheet, sheet_name
.HasTitle = True
.ChartTitle.Text = chart_name & vbNewLine & "(" & num_jrs & "
Junior Engineer(s) and " & num_srs & " Senior Engineer(s))"
.HasLegend = False
.HasDataTable = False
.Axes(xlValue, xlCategory).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
yaxis_label
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
xaxis_label
.Axes(xlCategory).MinimumScale = Round(sheet.Cells(2, 4), 0)
.Axes(xlValue).MaximumScale = 1
End With
End Function
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
$$Excel-Macros$$ macro efficiency
I have a fairly large VBA project for work - it runs a Monte Carlo simulation on some data that is grabbed from MS Project. It takes longer than I'd like to run - about 4 minutes for 1000 iterations, not including writing out to Excel. I have done everything I can think of to make it efficient: using For Each loops, declaring variables by type, using early binding and Option Explicit... I know that it's not going to be super-speedy given the nature of the project (lots of nested loops, 20x1000 arrays, etc.), but I feel like it should run faster than it does. I've tried timing sections of it, and with a small number of iterations, the speed is not always bad (subsections can take .3 and 1.1 seconds to run), but as I increase the number of iterations, it always seems to run at the worst case (e.g., the aforementioned subsections always take 1.1 seconds to run). Without looking at the code, does anyone have any suggestions for things I can look into to speed up runtime? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ create email when value in cell changes
I want to create an email when the value in column A changes. In column A I have the email addresses of managers. When the email address changes I want the VBA to compose the email and get the employees names in column B assoicated to that email address. I don't want it 1 for 1. I want to take only that one email address and bring in that range of employees in to the email. So in the example below I want to email manag...@domain.com and have employees 1,2,and 3 in the body of the email. I don't want seperate emails for each employee going to the manager. Thanks Example: Column A Column B manag...@domain.com employee1 manag...@domain.com employee2 manag...@domain.com employee3 manag...@domain.com employee4 manag...@domain.com employee5 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ Re: macro efficiency
Hi, Did you try "Exit for" in FOR loop where the condition is met and further looping is not required. Give it a try...It should help. Best regards, Ayush Jain On Mar 19, 7:00 am, scubagirl wrote: > I have a fairly large VBA project for work - it runs a Monte Carlo > simulation on some data that is grabbed from MS Project. It takes > longer than I'd like to run - about 4 minutes for 1000 iterations, not > including writing out to Excel. I have done everything I can think of > to make it efficient: using For Each loops, declaring variables by > type, using early binding and Option Explicit... > > I know that it's not going to be super-speedy given the nature of the > project (lots of nested loops, 20x1000 arrays, etc.), but I feel like > it should run faster than it does. I've tried timing sections of it, > and with a small number of iterations, the speed is not always bad > (subsections can take .3 and 1.1 seconds to run), but as I increase > the number of iterations, it always seems to run at the worst case > (e.g., the aforementioned subsections always take 1.1 seconds to run). > Without looking at the code, does anyone have any suggestions for > things I can look into to speed up runtime? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
Re: $$Excel-Macros$$ Excel Formula Required
Simplest possible. But needed unique list (no duplication). Regards Sandeep On Thu, Mar 18, 2010 at 8:37 PM, Dilip Pandey wrote: > Dear Venkatesan, > > Attached file has two solutions for your query. > Let me know in case of any challenge(s). > > > -- > Thanks & Regards, > > DILIP KUMAR PANDEY > MBA-HR,B.Com(Hons),BCA > Mobile: +91 9810929744 > dilipan...@gmail.com > dilipan...@yahoo.com > New Delhi - 62, India > > > On Thu, Mar 18, 2010 at 2:31 PM, Venkatesan c wrote: > >> Dear All, >> >> Any one can Assist me attached file query It's something like reverse >> Lookup >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our Facebook Group @ >> http://www.facebook.com/group.php?gid=287779555678 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 6,800 subscribers worldwide and receive many nice notes >> about the learning and support from the group.Let friends and co-workers >> know they can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe > > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject. Query-Reverse Vlookup by DILipandey.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ selecting a range and printing it with column title and print headings - vba
dear expert,
im new in vba but my work is truly repetitive. I want to select a range and
print preview it.
example my row heading is row 1:5, and my column heading is col A:D, I want
to select the range starting row 6 until last column with data and last row.
Any VBA please. Microsoft helps provides as follows.
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1:" & _
ActiveSheet.Cells(lastRow, lastCol).Address).Select
how to set the selected range to variable range to be use as print range.
Thanks a lot.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
$$Excel-Macros$$ Excel Daily Tip : Faster access to worksheets
Ayush Jain has sent you a link to a blog: Dear Group, Here is a tip for you to faster access of worksheets in Excel Best regards, Ayush Jain Group Owner Blog: Excel Daily Tip Post: Faster access to worksheets Link: http://exceldailytip.blogspot.com/2010/03/faster-access-to-worksheets.html -- Powered by Blogger http://www.blogger.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
$$Excel-Macros$$ VBA - dynamic range A copy formula in range B in vba
dear all, i have a dynamic column A which i used as a lookup value formula in column B, but since the column A is dynamic, how to create a vba code to copy the formula in say B1, down to the last row equal to column A. im using excel2003 . thanks a lot. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
