$$Excel-Macros$$ VBA Code against VLOOKUP function
Hi Masters, Needed the VBA code which work similar to VLOOKUP function where I can call data from master worksheet to fill my report Attached file will explain you my requirement Master worksheet contains - GL Code master where each GL code has details and grouping While putting GL code in Report worksheet it should call the data from master worksheet and give me Group 1 and Group 2 Please help Regards, Anil Bhange -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel VBA Code for VLOOKUP.xlsx Description: VBA Code for VLOOKUP.xlsx
$$Excel-Macros$$ Need a Macro
Hello Friends, I need your help to make macro. I need a macro which work like format painter for different format not only one format. * --- Kαяαη* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ VBA Code against VLOOKUP function
Dear Anil, Please see attached Sheet, -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Fri, Jul 15, 2011 at 11:32 AM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Masters, ** ** Needed the VBA code which work similar to VLOOKUP function where I can call data from master worksheet to fill my report ** ** Attached file will explain you my requirement ** ** Master worksheet contains – GL Code master where each GL code has details and grouping While putting GL code in Report worksheet it should call the data from master worksheet and give me Group 1 and Group 2 ** ** ** ** Please help ** ** Regards, Anil Bhange ** ** ** ** -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel VBA_Code_for_VLOOKUP(Solved).xlsm Description: Binary data
Re: $$Excel-Macros$$ Need a Macro
http://www.excelforum.com/excel-programming/353482-macro-to-create-format-painter.html On Fri, Jul 15, 2011 at 12:29 PM, karan 1237 karan1...@gmail.com wrote: Hello Friends, I need your help to make macro. I need a macro which work like format painter for different format not only one format. * --- Kαяαη* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need a Macro
It's working...Thank you so much... * Kαяαη* On Fri, Jul 15, 2011 at 12:33 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: http://www.excelforum.com/excel-programming/353482-macro-to-create-format-painter.html On Fri, Jul 15, 2011 at 12:29 PM, karan 1237 karan1...@gmail.com wrote: Hello Friends, I need your help to make macro. I need a macro which work like format painter for different format not only one format. * --- Kαяαη* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Merge Vertically
Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Merge Vertically.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ VBA Code against VLOOKUP function
Thanks Noorain, One more help please, If the GL code doesn't match can I able to color these cell in Red color Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Friday, July 15, 2011 12:31 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ VBA Code against VLOOKUP function Dear Anil, Please see attached Sheet, -- Thanks regards, Noorain Ansari http://noorain-ansari.blogspot.com/ On Fri, Jul 15, 2011 at 11:32 AM, Anil Bhange anil.bha...@tatacommunications.commailto:anil.bha...@tatacommunications.com wrote: Hi Masters, Needed the VBA code which work similar to VLOOKUP function where I can call data from master worksheet to fill my report Attached file will explain you my requirement Master worksheet contains - GL Code master where each GL code has details and grouping While putting GL code in Report worksheet it should call the data from master worksheet and give me Group 1 and Group 2 Please help Regards, Anil Bhange -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.comhttp://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: RE: $$Excel-Macros$$ Find and replace in all open Excel Workbook
Thank you Daniel.. It Worked.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Merge Vertically
Dear Karan, Please try it Sub mergecell() With Selection .HorizontalAlignment = xlGeneral .MergeCells = True End With End Sub On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.com wrote: Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Merge_Vertically(Solved).xlsm Description: Binary data
Re: $$Excel-Macros$$ Merge Vertically
PFI On Fri, Jul 15, 2011 at 2:56 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Karan, Please try it Sub mergecell() With Selection .VerticalAlignment = xlBottom .MergeCells = True End With End Sub On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.com wrote: Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Merge Vertically
* Hey friend, I facing this problem. I want to merge all columns in one time. Kαяαη* On Fri, Jul 15, 2011 at 2:56 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Karan, Please try it Sub mergecell() With Selection .HorizontalAlignment = xlGeneral .MergeCells = True End With End Sub On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.com wrote: Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ VBA Code against VLOOKUP function
Dear Anil, Please see attached Sheet.. -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Fri, Jul 15, 2011 at 12:54 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Thanks Noorain, ** ** One more help please, If the GL code doesn’t match can I able to color these cell in Red color ** ** Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI *Sent:* Friday, July 15, 2011 12:31 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ VBA Code against VLOOKUP function ** ** Dear Anil, Please see attached Sheet, -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ ** ** On Fri, Jul 15, 2011 at 11:32 AM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Masters, Needed the VBA code which work similar to VLOOKUP function where I can call data from master worksheet to fill my report Attached file will explain you my requirement Master worksheet contains – GL Code master where each GL code has details and grouping While putting GL code in Report worksheet it should call the data from master worksheet and give me Group 1 and Group 2 Please help Regards, Anil Bhange -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel VBA_Code_for_VLOOKUP(Solved)(Colored).xlsm Description: Binary data
Re: $$Excel-Macros$$ Merge Vertically
Sub mergeall() Worksheets(Sheet4).Rows.Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom End With Selection.Merge End Sub On Fri, Jul 15, 2011 at 3:03 PM, karan 1237 karan1...@gmail.com wrote: * Hey friend, I facing this problem. I want to merge all columns in one time. Kαяαη* On Fri, Jul 15, 2011 at 2:56 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Karan, Please try it Sub mergecell() With Selection .HorizontalAlignment = xlGeneral .MergeCells = True End With End Sub On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.comwrote: Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: sumproduct and pivot table on same data ranges - different results - any reasons
Sorry every who read this post, The mistake is not located on the productsum table or on the pivot table. It's simply located in the table below the productsum table which as an incoherent formula (it takes twice a value) which makes the difference...sorry On 14 juil, 20:11, Pascal Baro bpascal...@googlemail.com wrote: Please find a file attached for more on this query. Thanks sumproduct_pivot_table.xlsx 668KAfficherTélécharger -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: sumproduct and pivot table on same data ranges - different results - any reasons
I forgot to update values as i tried to make the size of the file smaller with less rows and columns ...on sheet4, line 28 should be the same as line 34 on sheet trt_june On 14 juil, 20:11, Pascal Baro bpascal...@googlemail.com wrote: Please find a file attached for more on this query. Thanks sumproduct_pivot_table.xlsx 668KAfficherTélécharger -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: sumproduct and pivot table on same data ranges - different results - any reasons
Sorry every who read this post, The mistake is not located on the productsum table or on the pivot table. It's simply located in the table below the productsum table which as an incoherent formula (it takes twice a value) which makes the difference...sorry On 14 juil, 20:11, Pascal Baro bpascal...@googlemail.com wrote: Please find a file attached for more on this query. Thanks sumproduct_pivot_table.xlsx 668KAfficherTélécharger -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ RE: Want to copy two rows in all sheets except few
Dear All, I want to copy paste special rows 5 6 of sheet “Heading” to all the sheets starting from “Balance Sheet” to “Note 31” Can someone suggest code for the same. शुभकामनाओं सहित, अजय मित्तल, वित्त प्रबन्धक पाइपलाइंस मुख्यालय टेलीफोन:0120-2448312 ई-मेल : amit...@iocl.co.in This Message was sent from Indian Oil Messaging Gateway, Refineries HQ, New Delhi, India. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Merge Vertically
Hi Karan/Norain, what is use of merging all the cells on a sheet. After merging what are you going to do with the sheet as it will become like a Notepad page and you cant use any other formula/function. I am asking this just out of my curiousity, would appreciate your answer. Thanks, Anish On Fri, Jul 15, 2011 at 3:43 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Sub mergeall() Worksheets(Sheet4).Rows.Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom End With Selection.Merge End Sub On Fri, Jul 15, 2011 at 3:03 PM, karan 1237 karan1...@gmail.com wrote: * Hey friend, I facing this problem. I want to merge all columns in one time. Kαяαη* On Fri, Jul 15, 2011 at 2:56 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Karan, Please try it Sub mergecell() With Selection .HorizontalAlignment = xlGeneral .MergeCells = True End With End Sub On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.comwrote: Hello Friends, Once again I need your help. There is option for merge across in excel but is there any macro to merge vertically. It will merge selected range in vertically. Thnx Karan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ RE: Want to copy two rows in all sheets except few
Hello Sir, I guess, I answered this query yesterday with attachment. Please let me know if it didnt work or you want a different solution. This was the code.. Sub copy_rows() Dim sht As Integer For sht = 5 To Worksheets.Count Sheets(4).Select Rows(5:6).Copy Sheets(sht).Select Rows(5).Select ActiveSheet.Paste Next sht End Sub thanks On Fri, Jul 15, 2011 at 1:25 PM, MITTAL AJAY amit...@iocl.co.in wrote: Dear All, ** ** I want to copy paste special rows 5 6 of sheet “Heading” to all the sheets starting from “Balance Sheet” to “Note 31” ** ** Can someone suggest code for the same. ** ** *शुभकामनाओं सहित,* * * *अजय मित्तल,*** *वित्त प्रबन्धक *** *पाइपलाइंस मुख्यालय * *टेलीफोन:0120-2448312* *ई-मेल** :** **amit...@iocl.co.in** *** ** ** *This Message was sent from Indian Oil Messaging Gateway, Refineries HQ, New Delhi, India. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need Formula
Hi Venkat, Thanks for the update... But this is not what I was looking for... I need a formula in the table above that.. The ones already show the result that I desire to achive. Thanks On Fri, Jul 15, 2011 at 10:57 AM, Venkatesan c venkat1@gmail.comwrote: Hi Rahim, Look attached file . *Best Regards,* *Venkat* On Thu, Jul 14, 2011 at 10:35 PM, Rahim Sharieff rahim.shari...@gmail.com wrote: Hi Experts, Please refer the attached workbook. The first sheet here Schedule gives the training requirements and Training batches, and the second sheet Training Planner needs to be updated based on this. I need a formula to be entered in D4 that will give me the results as show in the Training Planner sheet. The formula should be such that we should copy across and down to give the results as shown. Thanks in advance. -- Thanks Regards, Rahim Sharieff -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ** * ** * -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, Rahim Sharieff -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Multiple excel files
Hi Shrinivas, Attached please find the Dump file with macro. You will have to copy and paste your unique account numbers in Cell *M1 *before running the macro. Data will be splitted based on the account numbers and new files will be saved with account names. Also note that I have done it with Excel 2007, if you dont have this version, please let me know so that I can make it for 2003. Let me know if you face any issues with this macro. Below is the code for your ref. - Sub split_data() Dim lst_row, lst_col, i, j As Integer Dim ws_name As String Dim nwkb As Workbook Dim ac_num As String Dim uname As String Sheets(1).Rows(1).Select Selection.Insert shift:=xlDown lst_row = Sheets(1).Range(A2).End(xlDown).Row lst_col = Sheets(1).Range(A3).End(xlToRight).Column Range(A1, Cells(1, lst_col)).Select Selection.AutoFilter For j = 2 To Sheets(1).Range(M2).End(xlDown).Row ac_num = Sheets(1).Cells(j, M).Value uname = Environ(Username) ActiveSheet.Range(A1, Cells(lst_row, lst_col)).AutoFilter Field:=1, Criteria1:=ac_num For i = 2 To 1000 ' change to your ranges If Not Rows(i).Hidden Then theFirstUnhiddenRowinArea = i Range(B i).Select Exit For End If Next Range(Selection, Cells(lst_row, lst_col)).Select Selection.Copy Set nwkb = Workbooks.Add nwkb.Activate ActiveSheet.Paste nwkb.SaveAs C:\Users\ uname \Desktop\ ac_num .xlsx, FileFormat:=xlOpenXMLWorkbook nwkb.Close Next j Sheets(1).Rows(1).Delete shift:=xlUp End Sub -- Thanks, Anish On Fri, Jul 15, 2011 at 4:12 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Dear Friends, Please help me create macro for splitting the dump file into multiple excel files as per account number in *column A* and file name should be account no. I have using the attached code for splitting the different dump file but it is not working in this dump file. Please advise on the same. Find attached sample of dump file , required format and code. ***Dump file R**equired **file* Thanking in advance. Regards, Shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Dump_Report_Anish.xlsm Description: Binary data
Re: $$Excel-Macros$$ Multiple excel files
Hello Group, It would be an add-in for my learing, if you can suggest the code for finding unique account values from Column A using the code so that Shrinivas doesnt have to copy and paste the unique account values manually in cell M1. Thanks, Anish On Fri, Jul 15, 2011 at 5:27 PM, Anish Shrivastava anish@gmail.comwrote: Hi Shrinivas, Attached please find the Dump file with macro. You will have to copy and paste your unique account numbers in Cell *M1 *before running the macro. Data will be splitted based on the account numbers and new files will be saved with account names. Also note that I have done it with Excel 2007, if you dont have this version, please let me know so that I can make it for 2003. Let me know if you face any issues with this macro. Below is the code for your ref. - Sub split_data() Dim lst_row, lst_col, i, j As Integer Dim ws_name As String Dim nwkb As Workbook Dim ac_num As String Dim uname As String Sheets(1).Rows(1).Select Selection.Insert shift:=xlDown lst_row = Sheets(1).Range(A2).End(xlDown).Row lst_col = Sheets(1).Range(A3).End(xlToRight).Column Range(A1, Cells(1, lst_col)).Select Selection.AutoFilter For j = 2 To Sheets(1).Range(M2).End(xlDown).Row ac_num = Sheets(1).Cells(j, M).Value uname = Environ(Username) ActiveSheet.Range(A1, Cells(lst_row, lst_col)).AutoFilter Field:=1, Criteria1:=ac_num For i = 2 To 1000 ' change to your ranges If Not Rows(i).Hidden Then theFirstUnhiddenRowinArea = i Range(B i).Select Exit For End If Next Range(Selection, Cells(lst_row, lst_col)).Select Selection.Copy Set nwkb = Workbooks.Add nwkb.Activate ActiveSheet.Paste nwkb.SaveAs C:\Users\ uname \Desktop\ ac_num .xlsx, FileFormat:=xlOpenXMLWorkbook nwkb.Close Next j Sheets(1).Rows(1).Delete shift:=xlUp End Sub -- Thanks, Anish On Fri, Jul 15, 2011 at 4:12 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Dear Friends, Please help me create macro for splitting the dump file into multiple excel files as per account number in *column A* and file name should be account no. I have using the attached code for splitting the different dump file but it is not working in this dump file. Please advise on the same. Find attached sample of dump file , required format and code. ***Dump file R**equired **file* Thanking in advance. Regards, Shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: Total displayed as -1.776E-13 .. why not Zero????
Hi Markkim, I have checked that the answer of the formula =IF(A54=0,1,0) is 1 because A54 is blank in your sample sheet. If I assume that you will have some value in A54, then the answer will be 0 and then your below code is working fine :- If range(A54) 0 then Else . End If Please check if this helps and let us know if you are revisiting your explanation. -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India On Jul 14, 1:06 pm, Markkim mark@gmail.com wrote: Hi I have an issue with suming up numbers.. when I sum up, it shows as -1.776E-13 ... not as Zero. I know I can fix it by simply changing the format cell to number. But the issue is that excel doesn't think it is Zero. in the sample worksheet, Range C54 contains a following formular =IF(A54=0,1,0) And the result is 0 meaning it thinks A54 is not zero. I need Excel to think it is zero otherwise the macro I'm writing will not work. How can I make Excel think it's Zero.? .. because I need to write like If range(A54) 0 then Else . End If Please help me!! Thanks untitled.xls 18KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ sumproduct and pivot table on same data ranges - different results - (part 2)
Hi U have consider G1 = 2527 in pivot table not in sumproduct sheet. Please check Thanks Mahesh On Fri, Jul 15, 2011 at 6:20 PM, Pascal Baro bpascal...@googlemail.comwrote: Hi, This time there really is a difference between the pivot table and productsum function. It seems it's all about decimal values. Productsum seems to round up numbers in a different way than the pivot table. For large numbers we then get a difference. Please look a the file for more ... Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Hi I need your help , Please explain it with example.
Hi Sir, Can you please tell me what is difference between these two codes, Please guide me. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ there are two code , please explain this code.
Hi, I need your help . I have query in VBA. Please help me . Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets(ProductsList).Range(I3).Calculate Worksheets(ProductsList).Range(Database) _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets(ProductsList).Range(I2:I3), _ CopyToRange:=Range(A6:G6), Unique:=False End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 7 And Target.Row 6 Then If Cells(Target.Row, 1).Value Then Target.Value = X MoveRow 'MsgBox Row has been copied End If End If End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Hi I need your help , Please explain it with example.
Hi, Private Sub Worksheet_Change(ByVal Target As Range) is fired when you enter a value into a cell Private Sub Worksheet_SelectionChange(ByVal Target As Range) is fired when you select another cell or if you change the current selection. In both cases, “Target” represents the activecell or current selection. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de neil johnson Envoyé : vendredi 15 juillet 2011 13:45 À : excel-macros Objet : $$Excel-Macros$$ Hi I need your help , Please explain it with example. Hi Sir, Can you please tell me what is difference between these two codes, Please guide me. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Hi I need your help , Please explain it with example.
Note that in some cases, there is a difference between “Target” and “ActiveCell”, when the active cell moves downwards or sidewards after you hit the “Enter” key. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Daniel Envoyé : vendredi 15 juillet 2011 18:07 À : excel-macros@googlegroups.com Objet : RE: $$Excel-Macros$$ Hi I need your help , Please explain it with example. Hi, Private Sub Worksheet_Change(ByVal Target As Range) is fired when you enter a value into a cell Private Sub Worksheet_SelectionChange(ByVal Target As Range) is fired when you select another cell or if you change the current selection. In both cases, “Target” represents the activecell or current selection. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de neil johnson Envoyé : vendredi 15 juillet 2011 13:45 À : excel-macros Objet : $$Excel-Macros$$ Hi I need your help , Please explain it with example. Hi Sir, Can you please tell me what is difference between these two codes, Please guide me. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Hi I need your help , Please explain it with example.
*1. **Worksheet.Change Event* * *Occurs when cells on the worksheet are changed by the user or by an external link. This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation. This example changes the color of changed cells to blue. Private Sub Worksheet_Change(ByVal Target as Range) Target.Font.ColorIndex = 5 End Sub *2 Worksheet.SelectionChange Event * Occurs when* the selection changes* on a worksheet. e.g You move cell pointer from A1 to A2 The parameter Target is Range(A2) This example scrolls through the workbook window until* the selection is in the upper-left corner of the window*. Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveWindow .ScrollRow = Target.Row .ScrollColumn = Target.Column End With End Sub CMIIW On Fri, Jul 15, 2011 at 6:45 PM, neil johnson neil.jh...@googlemail.com wrote: Hi Sir, Can you please tell me what is difference between these two codes, Please guide me. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ there are two code , please explain this code.
Hello, Here are some comments : ‘executes when a value is entered in a cell (or the cell contents are deleted) Private Sub Worksheet_Change(ByVal Target As Range) ‘only if the value is entered in cell C3 If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets(ProductsList).Range(I3).Calculate ‘uses advanced filter, results in columns A:G, from row 6 downward Worksheets(ProductsList).Range(Database) _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets(ProductsList).Range(I2:I3), _ CopyToRange:=Range(A6:G6), Unique:=False End If End Sub ‘if another range is selected (maybe the filter result ?) Private Sub Worksheet_SelectionChange(ByVal Target As Range) ‘if column is G and row 6 If Target.Column = 7 And Target.Row 6 Then ‘if cell being on the same row in column A ”” If Cells(Target.Row, 1).Value Then ‘change target value to “X” Target.Value = X ‘macro ? MoveRow 'MsgBox Row has been copied End If End If End Sub Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de neil johnson Envoyé : vendredi 15 juillet 2011 13:50 À : excel-macros; Dave Bonallack; Dilip Pandey Objet : $$Excel-Macros$$ there are two code , please explain this code. Hi, I need your help . I have query in VBA. Please help me . Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets(ProductsList).Range(I3).Calculate Worksheets(ProductsList).Range(Database) _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets(ProductsList).Range(I2:I3), _ CopyToRange:=Range(A6:G6), Unique:=False End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 7 And Target.Row 6 Then If Cells(Target.Row, 1).Value Then Target.Value = X MoveRow 'MsgBox Row has been copied End If End If End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ sumproduct and pivot table on same data ranges - different results - (part 2)
thanks Mahesh, btw, I guess it would be better to use getpivotdata function instead of a sumproduct function that requires the entire excel application to update data if any modification to the data or the function is made... thanks again On 15 juil, 15:08, Mahesh parab mahes...@gmail.com wrote: Hi U have consider G1 = 2527 in pivot table not in sumproduct sheet. Please check Thanks Mahesh On Fri, Jul 15, 2011 at 6:20 PM, Pascal Baro bpascal...@googlemail.comwrote: Hi, This time there really is a difference between the pivot table and productsum function. It seems it's all about decimal values. Productsum seems to round up numbers in a different way than the pivot table. For large numbers we then get a difference. Please look a the file for more ... Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ $$Excel VBA$$ Sorting worksheet with grouped rows and/or validation lists
I am working on a code that has a series of metrics listed in rows. Each metric is uniquely specified by several headings listed in the header row. The (relevant) categories in the header are as follows: |ID|Category|Sub-Category|Metric|Sub-Metric|Unit|Priority|etc... (irrelevant)| The entire worksheet needs to be sorted. Because the heading labeled Metric can have multiple row entries of the same value (string), I want to write a macro in which the rows with all the same entries under the metric header are grouped together, and sorted until each of the categories individually are sorted. Then, I want the entire sheet to be sorted. However, by doing this, I do not want the groups with the same Metric category labels to become unsorted. I want them to be glued together, and sorted as if the group were one row. The highest Priority of the group will determine where it is placed on the worksheet (Priority is determined by the labels 1,2,3,and ?. It is sorted in descending order - question mark last) Here is some code I have been working on: Dim ws As Worksheet Dim rowCount, subRowCount, idCount, letterCount, totCount, marker As Integer Dim letString, idLabel, curCell As String Dim rowSelect As Long 'Predefine necessary variables rowCount = 2 'Start at first row, excluding header idCount = 1 letterCount = 0 'Determine Name of label If ActiveSheet.Name = Project Description Then idLabel = ID- ElseIf ActiveSheet.Name = Real Estate-Financial Then idLabel = RF- ElseIf ActiveSheet.Name = Building Features Then idLabel = BF- ElseIf ActiveSheet.Name = Green Features Then idLabel = GF- ElseIf ActiveSheet.Name = Operational Then idLabel = OP- ElseIf ActiveSheet.Name = Occupants Then idLabel = OC- Else idLabel = Application.InputBox(Please specify ID Label., ID LABEL) + - End If 'Find the last row with data in the sheet. Number of last row = totCount totCount = ActiveSheet.Range(A).End(xlUp).Row 'Loop through each cell in column D to determine if consecutive metrics have the same value 'If true, while loop sorts each series of consecutive metrics individually 'LATER, CONVER THIS INTO FIND ALL Do curCell = ActiveSheet.Cells(rowCount, 4) 'Selects cell in 'metric' column at row rowCount subRowCount = rowCount + 1 'Makes a cell selection while cell values in column 4 are equal If curCell = ActiveSheet.Cells(subRowCount, 4) Then 'NECESSARY? ActiveSheet.Cells(rowCount, L) = marker Do 'Resize selection to include cell with same metric name Selction.Resize(1).Select 'increment compare next two subesquent cells in sequence rowCount = rowCount + 1 subRowCount = rowCount + 1 curCell = ActiveSheet.Cells(rowCount, 4) 'Establish place holder in col L (empty) to track groups ActiveSheet.Cells(rowCount, L) = marker Loop While curCell = ActiveSheet.Cells(subRowCount, 4) 'to prevent subsequent groups of metrics from being sorted together marker = marker + 1 'Sort the existing selection. Should this be selection.col()? Selection.EntireRow.Sort Key1:=col(G), Order1:=xlDescending, Key2:=col(B), _ Order2:=xlDescending, key3:=col(C), Order3:=xlDescending, key4:=col(D), _ order4:=xlDescending, key5:=col(E), Order5:=xlDescending '^^^In the future we will want the preceeding code to find all similar cells Else rowCount = rowCount + 1 End If Loop Until rowCount = totCount 'Sort through remaining worksheet while keep cell groups intact 'Should treat cells a ActiveSheet.EntireRow.Sort Key1:=col(G), Order1:=xlDescending, Key2:=col(B), _ Order2:=xlDescending, key3:=col(C), Order3:=xlDescending, key4:=col(D), _ order4:=xlDescending, key5:=col(E), Order5:=xlDescending 'resets rowCount to start at the first row (excluding header) rowCount = 2 'Loops through each cell, looks at column L, if marker is present, 'id labels loop through letterCount to find appropriate ID label Do If ActiveSheet.Cells(rowCount, L) = ActiveSheet.Cells(rowCount + 1, L) Then 'Labels sub-metirc idLabel-rowCountA,B,C,etc. for as long as marker is present in column L Do ActiveSheet.Cells(rowCount, A) = idLabel rowCount.String letString(letterCount) 'trying to index string letterCount = letterCount + 1 rowCount = rowCount + 1 Loop While ActiveSheet.Cells(rowCount, L) Empty _ And ActiveSheet.Cells(rowCount, L) = ActiveSheet.Cells(rowCount + 1, L) Else: ActiveSheet.Cells(rowCount, A) = idLabel rowCount.String End If letterCount = 1 rowCount = rowCount + 1 Loop While rowCount totCount 'EVENTUALLY WE WILL WANT TO COMBINE TWO LOOPS SO PROGRAM RUNS MORE EFFICIENTLY A reference to the file can be found here From the file you can see that the cells are sorted by priority, yet the previously sorted groups will be sorted as
Re: $$Excel-Macros$$ Need Formula
Hi Mahesh, Thanks so much for the formula... I really appreciate your work..!!! Have a Great Weekend..!! Warm Regards, Rahim Sharieff. On Fri, Jul 15, 2011 at 9:55 PM, Mahesh parab mahes...@gmail.com wrote: Hi Rahim find attach Thanks Mahesh On Thu, Jul 14, 2011 at 10:35 PM, Rahim Sharieff rahim.shari...@gmail.com wrote: Hi Experts, Please refer the attached workbook. The first sheet here Schedule gives the training requirements and Training batches, and the second sheet Training Planner needs to be updated based on this. I need a formula to be entered in D4 that will give me the results as show in the Training Planner sheet. The formula should be such that we should copy across and down to give the results as shown. Thanks in advance. -- Thanks Regards, Rahim Sharieff -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, Rahim Sharieff -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Excel 2007 Quick Access Toolbar- Take it with me...
Is there a way to have my Quick Access Toolbar portable (like on a jump drive) so any computer with Excel 2007 can have my customized Quick Access Toolbar when I use it? Thank you. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
