Re: $$Excel-Macros$$ Formula or macro to find a word that is all capitlaized
Hi Try : Use below formula in Conditional Formatting =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT(65:90))),)))=3 Reference :http://dmcritchie.mvps.org/excel/strings.htm Thanks Mahesh On Thu, Jul 28, 2011 at 3:39 AM, qcan comeonove...@gmail.com wrote: Hi, Can anyone help me with some sort of formula or Macro that would identify any word (minmum 3 letters) that is all in caps. I would like whatever is found to be highlighted in yellow. Thanks. -- -- 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$$ Recover the VB password
Where is the file? On Fri, Jul 29, 2011 at 3:14 PM, ICWAI Help icwai.answ...@gmail.com wrote: HI Ashish, i have updated the VB password for my excel file while but i am not able to open now.could you please let me knwo is there any other way to open the codes.in that. Thansk, ICWAI. -- -- 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$$ Help needed to comine VBA modules
It will be very difficult to move the code to the personal.xlsb workbook without the initial file retaining some code. Can you explain why you want to do so ? Maybe there should be alternative solutions. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : dimanche 31 juillet 2011 04:27 À : Rajan_Verma; excel macro forum Objet : Fwd: $$Excel-Macros$$ Help needed to comine VBA modules Dear rajan verma, The worksheet 'analysis' is being created during the macro run of module analysis only ,Now naming the drilled down w.sheet is possible by including the following code in w.sheet analysis,I propose to run this from the personal macro workbook,How to achieve this?. kindly help Aju v chacko Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub Cancel = True If Target.Value Then Target.ShowDetail = True ActiveSheet.Name = Target.Offset(, -1).Value End If End Sub -- Forwarded message -- From: Daniel dcolarde...@free.fr Date: Sun, Jul 31, 2011 at 12:53 AM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com I am sorry, I don’t know how to do it. Maybe it is possible to achieve it with a class module, but evenso, I’m not able to do it so far. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 17:00 À : excel macro forum Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks. Now it is working fine.But the sub 'Worksheet_BeforeDoubleClick' is included as worksheet level event routine of w.sheet analysis.How can i incorporate this in personal macro workbook.Once again thanking u for Ur help. with regards Aju v chacko On Sat, Jul 30, 2011 at 6:33 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Also add this Function to Remove Special Character From Sheet name ,Because if the Special Character include in the string it will not change the Sheet name, See the attached file.. Public Function SheetName(Shname As String) As String Dim Cod As Integer Dim ShN As String For i = 1 To Len(Shname) Cod = Asc(Mid(Shname, i, 1)) If (Cod 64 And Cod 91) Or (Cod 96 And Cod 123) Or (Cod 79 And Cod 90) Then ShN = ShN Mid(Shname, i, 1) End If Next SheetName = ShN End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Daniel Sent: Saturday, July 30, 2011 6:12 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules Here is my test file. -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 05:33 À : excel-macros@googlegroups.com Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks for ur kind reply,But when i pasted this module into the module' analysis',the following line showing compilation error(red colour).error-(sub or function not defined). Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub If Target.Value Then End If Kindly help Aju v chacko On 7/29/11, Daniel dcolarde...@free.fr wrote: Paste the macro below in the ANALYSIS module : Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub Cancel = True If Target.Value Then Target.ShowDetail = True ActiveSheet.Name = Target.Offset(, -1).Value End If End Sub Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : vendredi 29 juillet 2011 16:25 À : excel macro forum Objet : Fwd: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, As u can see in w.sheet analysis contain a pivot table created by the macro,There are two columns one is row labels (Final B Mr/S Batch Apex..) other sum of value field which is a actually a sum of numeric field,It is possible to Drill down specific numeric filed,by double clicking on it,Then a new sheet will be created with Drilled down value,But the sheet name will be 'sheet2,sheet3...'.Instead of such w.sheet name i want the name of w.sheet being created to be taken from respective row lable' field of the numeric
RE: $$Excel-Macros$$ urgent macro required pls
Hi, Do you want to add the rows of each sheet below the preceding ones, or do you intend to consolidate the sheets like you'll do it with Data, Consolidate menus ? Regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de lee Envoyé : samedi 30 juillet 2011 19:56 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ urgent macro required pls hello i have 15 files in one folder. i need to combine two sheets called as maps and shipment of all the 15 files into one consolidated file with 2 sheets( with respective data). Structure and columns are same in sheets for all the files. Can you pls give a macro.. also can you please let m e know on how to run the macro i never ran it.. like do i have to open the first spreadsheet while running macro thanks -- -- 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$$ Help with Refreshing Multiple Pivots on different sheets
Hey Thanks people.. never knew that the Refresh All option eixsted !..appreciate the help. Regards, Ruchi On Tue, Jul 26, 2011 at 8:13 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Sub allpivotsofworkbook() activeworkbook.Refreshall end sub On Mon, Jul 25, 2011 at 11:55 PM, XLS S xlst...@gmail.com wrote: Easy way if you are using excel 2007,2010 then just press ctrl+alt+F5 On Sat, Jul 23, 2011 at 10:36 PM, Ruchi B ruchi.ben...@gmail.comwrote: All, Have a excel workbook with around 15 tabs ..each of tab has multiple pivots referring to different sets of data. All the pivots refer to 4 data sets in all .What is the best way of refreshing these multiple Pivots in different tabs at one go? Regards, Ruchi -- -- 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
$$Excel-Macros$$ Use of MS access in case of SAP R/3
Dear Experts Is there any use of MS access when a company is using SAP R/3 ? Regards Jatin -- -- 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$$ urgent macro required pls
i would like to add rows of respective sheets to be added below one another. i have never run a macro. Can you please give step by step instruction. On Sun, Jul 31, 2011 at 5:27 AM, Daniel dcolarde...@free.fr wrote: Hi, Do you want to add the rows of each sheet below the preceding ones, or do you intend to consolidate the sheets like you'll do it with Data, Consolidate menus ? Regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de lee Envoyé : samedi 30 juillet 2011 19:56 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ urgent macro required pls hello i have 15 files in one folder. i need to combine two sheets called as maps and shipment of all the 15 files into one consolidated file with 2 sheets( with respective data). Structure and columns are same in sheets for all the files. Can you pls give a macro.. also can you please let m e know on how to run the macro i never ran it.. like do i have to open the first spreadsheet while running macro thanks -- -- 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$$ How to Find Last Present Date From Month in One column
Hi, In AF2 (array formula, validate with Ctrl+Shift+Enter) : =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2=P,COLUMN($B$2:$AE$2 Copy down. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de maulik desai Envoyé : dimanche 31 juillet 2011 10:26 À : excel-macros@googlegroups.com Objet : $$Excel-Macros$$ How to Find Last Present Date From Month in One column Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- 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$$ How to Find Last Present Date From Month in One column
=INDEX($A$1:$AE$1,SUMPRODUCT(MAX(($B2:$AE2=P)*COLUMN($B$1:$AE$1 will work without an array formula Dan :) On Jul 31, 5:20 pm, Daniel dcolarde...@free.fr wrote: Hi, In AF2 (array formula, validate with Ctrl+Shift+Enter) : =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2=P,COLUMN($B$2:$AE$2 Copy down. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de maulik desai Envoyé : dimanche 31 juillet 2011 10:26 À : excel-macros@googlegroups.com Objet : $$Excel-Macros$$ How to Find Last Present Date From Month in One column Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- 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 linkhttp://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$$ How to Find Last Present Date From Month in One column
HI, just copy paste the below formula till your last employee which will give you accurate results =INDEX($A$1:$AE$1,1,MAX(IF($B2:$AE2=P,COLUMN($B$2:$AE$2 as mentioned bellow with Ctr+shift+Enter On Sun, Jul 31, 2011 at 5:50 PM, Daniel dcolarde...@free.fr wrote: Hi, ** ** In AF2 (array formula, validate with Ctrl+Shift+Enter) : ** ** =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2=P,COLUMN($B$2:$AE$2 ** ** Copy down. ** ** Regards. ** ** Daniel ** ** *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] *De la part de* maulik desai *Envoyé :* dimanche 31 juillet 2011 10:26 *À :* excel-macros@googlegroups.com *Objet :* $$Excel-Macros$$ How to Find Last Present Date From Month in One column ** ** Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- 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 -- Jaysheel Bhasme. -- -- 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$$ How to Find Last Present Date From Month in One column
Sorry, it should have more correct to write SUMPRODUCT IS an array function. -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Daniel Envoyé : dimanche 31 juillet 2011 15:31 À : excel-macros@googlegroups.com Objet : RE: $$Excel-Macros$$ How to Find Last Present Date From Month in One column Yes, that's true. However, do not forget that SUMPRODUCT IS an array formula, just designed to be entered as a standard formula by MS. Best regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de GoldenLance Envoyé : dimanche 31 juillet 2011 14:31 À : MS EXCEL AND VBA MACROS Objet : Re: $$Excel-Macros$$ How to Find Last Present Date From Month in One column =INDEX($A$1:$AE$1,SUMPRODUCT(MAX(($B2:$AE2=P)*COLUMN($B$1:$AE$1 will work without an array formula Dan :) On Jul 31, 5:20 pm, Daniel dcolarde...@free.fr wrote: Hi, In AF2 (array formula, validate with Ctrl+Shift+Enter) : =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2=P,COLUMN($B$2:$AE$2 Copy down. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de maulik desai Envoyé : dimanche 31 juillet 2011 10:26 À : excel-macros@googlegroups.com Objet : $$Excel-Macros$$ How to Find Last Present Date From Month in One column Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- -- -- 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 linkhttp://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
RE: $$Excel-Macros$$ urgent macro required pls
Open the workbook where the results are to be copied in. The macro adds maps and shipment sheets so there should be no such sheets in the workbook. Press the Alt+F11 keys, this opens the VB Editor window. Now, keep in mind that I use a French version of Excel, so the words I use may not be Exactly those of an English version. In the left part of the window, look for a project that has the same name as your workbook. Click on it; click on the “Insert” menu, then click on “Module”. Paste the below macro in the right part of the screen and you are done ! Now, running the macro depends of your version of Excel; the simplest way to do it is to click into the macro and press the F5 key. Please, tell which version you use and I tell you how to run it from the Excel window. Sub Consolidate() 'this macro should be placed in the result workbook Dim inCalculationMode As Integer Application.ScreenUpdating = False inCalculationMode = Application.Calculation Application.Calculation = xlCalculationManual Const strPath As String = _ C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\ 'change the path Dim strFile As String, shMaps As Worksheet, shShipment As Worksheet Dim arrSheets arrSheets = Array(maps, shipment) Sheets.Add.Name = maps Sheets.Add.Name = shipment 'guess headers are in row 1 'guess all columns are equal length 'guess there is data in column A strFile = Dir(strPath *.xls*) Do While strFile Workbooks.Open strPath strFile For Each sh In arrSheets With Sheets(sh) If ThisWorkbook.Sheets(sh).Range(A1) = Then .Range(.[A1], .Cells(Rows.Count, 1).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(sh).Range(A1).PasteSpecial xlPasteValues Else .Range(.[A2], .Cells(Rows.Count, 1).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(sh).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues End If End With Next sh ActiveWorkbook.Close False strFile = Dir Loop Application.Calculation = inCalculationMode Application.ScreenUpdating = True End Sub Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Sara Lee Envoyé : dimanche 31 juillet 2011 14:03 À : excel-macros@googlegroups.com Objet : Re: $$Excel-Macros$$ urgent macro required pls i would like to add rows of respective sheets to be added below one another. i have never run a macro. Can you please give step by step instruction. On Sun, Jul 31, 2011 at 5:27 AM, Daniel dcolarde...@free.fr wrote: Hi, Do you want to add the rows of each sheet below the preceding ones, or do you intend to consolidate the sheets like you'll do it with Data, Consolidate menus ? Regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de lee Envoyé : samedi 30 juillet 2011 19:56 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ urgent macro required pls hello i have 15 files in one folder. i need to combine two sheets called as maps and shipment of all the 15 files into one consolidated file with 2 sheets( with respective data). Structure and columns are same in sheets for all the files. Can you pls give a macro.. also can you please let m e know on how to run the macro i never ran it.. like do i have to open the first spreadsheet while running macro thanks -- -- 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.
RE: $$Excel-Macros$$ Entered data should be locked
Hello, I protected the sheet (password : excel). In the sheet module I put the macro : Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range ActiveSheet.Unprotect excel For Each c In Target If c Then c.Locked = True Next c ActiveSheet.Protect excel End Sub Have a look at the attached workbook. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de muralidhar e Envoyé : dimanche 31 juillet 2011 14:25 À : excel-macros@googlegroups.com Objet : $$Excel-Macros$$ Entered data should be locked Dear Friends, If any one can helped can be appreciated i have data like frequently repeated data so i would like to lock the entered data and it should not be modified i.e. my file should allow empty cells should be entered, if any correction or modification needed that should ask the password.If the help is without macro is better to me. -- Thanks Regards, Muralidhar E. -- -- 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 lock entered data.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ How to Find Last Present Date From Month in One column
Dear Maulik, Please try it with ctrl+shift+Enter =OFFSET($A$1,0,LARGE(IF(B2:AE2=P,COLUMN($B$1:$AE$1),),COLUMN(1:1))-1) -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Sun, Jul 31, 2011 at 1:56 PM, maulik desai mauliksde...@gmail.comwrote: Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- 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$$ urgent macro required pls
Thank You Daniel. I really appreciate it. On Sun, Jul 31, 2011 at 10:52 AM, Daniel dcolarde...@free.fr wrote: Open the workbook where the results are to be copied in. The macro adds maps and shipment sheets so there should be no such sheets in the workbook. Press the Alt+F11 keys, this opens the VB Editor window. Now, keep in mind that I use a French version of Excel, so the words I use may not be Exactly those of an English version. In the left part of the window, look for a project that has the same name as your workbook. Click on it; click on the “Insert” menu, then click on “Module”. Paste the below macro in the right part of the screen and you are done ! Now, running the macro depends of your version of Excel; the simplest way to do it is to click into the macro and press the F5 key. Please, tell which version you use and I tell you how to run it from the Excel window. ** ** Sub Consolidate() 'this macro should be placed in the result workbook Dim inCalculationMode As Integer Application.ScreenUpdating = False inCalculationMode = Application.Calculation Application.Calculation = xlCalculationManual Const strPath As String = _ C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\ 'change the path* *** Dim strFile As String, shMaps As Worksheet, shShipment As Worksheet*** * Dim arrSheets arrSheets = Array(maps, shipment) Sheets.Add.Name = maps Sheets.Add.Name = shipment 'guess headers are in row 1 'guess all columns are equal length 'guess there is data in column A strFile = Dir(strPath *.xls*) Do While strFile Workbooks.Open strPath strFile For Each sh In arrSheets With Sheets(sh) If ThisWorkbook.Sheets(sh).Range(A1) = Then .Range(.[A1], .Cells(Rows.Count, 1).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(sh).Range(A1).PasteSpecial xlPasteValues Else .Range(.[A2], .Cells(Rows.Count, 1).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(sh).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues End If End With Next sh ActiveWorkbook.Close False strFile = Dir Loop Application.Calculation = inCalculationMode Application.ScreenUpdating = True End Sub ** ** Regards. ** ** Daniel ** ** *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] *De la part de* Sara Lee *Envoyé :* dimanche 31 juillet 2011 14:03 *À :* excel-macros@googlegroups.com *Objet :* Re: $$Excel-Macros$$ urgent macro required pls ** ** i would like to add rows of respective sheets to be added below one another. i have never run a macro. Can you please give step by step instruction. On Sun, Jul 31, 2011 at 5:27 AM, Daniel dcolarde...@free.fr wrote: Hi, Do you want to add the rows of each sheet below the preceding ones, or do you intend to consolidate the sheets like you'll do it with Data, Consolidate menus ? Regards. Daniel -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de lee Envoyé : samedi 30 juillet 2011 19:56 À : MS EXCEL AND VBA MACROS Objet : $$Excel-Macros$$ urgent macro required pls hello i have 15 files in one folder. i need to combine two sheets called as maps and shipment of all the 15 files into one consolidated file with 2 sheets( with respective data). Structure and columns are same in sheets for all the files. Can you pls give a macro.. also can you please let m e know on how to run the macro i never ran it.. like do i have to open the first spreadsheet while running macro thanks -- -- 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
Re: $$Excel-Macros$$ How to Find Last Present Date From Month in One column
Hello Maulik, If you just have single characters in cells like A,P etc... use this, AF2 copy down. =LOOKUP(10,SEARCH(p,B2:AE2),B$1:AE$1) Or, =LOOKUP(2,1/(B2:AE2=p),B$1:AE$1) The first one is more faster than second one. HTH Haseeb For tips visit; http://www.excelfox.com/forum/forum.php -- -- 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
Fwd: $$Excel-Macros$$ Help needed to comine VBA modules
Dear daniel, Actually the raw data for worksheet *ANALYSIS* was downloaded from *SAP -ERP *,With help of macro it is consolidated into a*pivot table * in worksheet *analysis*.Every day i have to do this process many times,That's why i propose to include in Personal macro workbook,so that with a single click it can done Regards aju v chacko -- Forwarded message -- From: Daniel dcolarde...@free.fr Date: Sun, Jul 31, 2011 at 1:57 PM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com It will be very difficult to move the code to the personal.xlsb workbook without the initial file retaining some code. Can you explain why you want to do so ? Maybe there should be alternative solutions. ** ** Regards. ** ** Daniel ** ** *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] *De la part de* aju chacko *Envoyé :* dimanche 31 juillet 2011 04:27 *À :* Rajan_Verma; excel macro forum *Objet :* Fwd: $$Excel-Macros$$ Help needed to comine VBA modules ** ** Dear rajan verma, The worksheet *'analysis' is being created during the macro run of module analysis only* ,Now naming the drilled down w.sheet is possible by including the following code in* w.sheet analysis*,I propose to run this from the* personal macro workbook,*How to achieve this?. kindly help Aju v chacko Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub Cancel = True If Target.Value Then Target.ShowDetail = True ActiveSheet.Name = Target.Offset(, -1).Value End If End Sub -- Forwarded message -- From: *Daniel* dcolarde...@free.fr Date: Sun, Jul 31, 2011 at 12:53 AM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com I am sorry, I don’t know how to do it. Maybe it is possible to achieve it with a class module, but evenso, I’m not able to do it so far. Regards. Daniel *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] *De la part de* aju chacko *Envoyé :* samedi 30 juillet 2011 17:00 *À :* excel macro forum *Objet :* Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks. Now it is working fine.But the sub '* Worksheet_BeforeDoubleClick'* is included as worksheet level event routine of w.sheet *analysis*.How can i incorporate this in personal macro workbook.Once again thanking u for Ur help. with regards Aju v chacko On Sat, Jul 30, 2011 at 6:33 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Also add this Function to Remove Special Character From Sheet name ,Because if the Special Character include in the string it will not change the Sheet name, See the attached file.. Public Function SheetName(Shname As String) As String Dim Cod As Integer Dim ShN As String For i = 1 To Len(Shname) Cod = Asc(Mid(Shname, i, 1)) If (Cod 64 And Cod 91) Or (Cod 96 And Cod 123) Or (Cod 79 And Cod 90) Then ShN = ShN Mid(Shname, i, 1) End If Next SheetName = ShN End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Daniel Sent: Saturday, July 30, 2011 6:12 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules Here is my test file. -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 05:33 À : excel-macros@googlegroups.com Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks for ur kind reply,But when i pasted this module into the module' analysis',the following line showing compilation error(red colour).error-(sub or function not defined). Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub If Target.Value Then End If Kindly help Aju v chacko On 7/29/11, Daniel dcolarde...@free.fr wrote: Paste the macro below in the ANALYSIS module : Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub Cancel = True If Target.Value Then Target.ShowDetail = True ActiveSheet.Name = Target.Offset(, -1).Value End If End Sub Daniel De :
$$Excel-Macros$$ Learn VBA with Videos
Hey All, Please find the below link... http://www.familycomputerclub.com/ http://www.wuala.com/subashvt/Excel%20Macro%20Training/ -- -- 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$$ Help needed to comine VBA modules
Can you attach a sample file such as issued from SAP ? The next point to discuss is the detail sheets from the pivot. Do you want all of them or only the ones you double clicked on the value field Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : dimanche 31 juillet 2011 20:33 À : excel macro forum Objet : Fwd: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Actually the raw data for worksheet ANALYSIS was downloaded from SAP -ERP ,With help of macro it is consolidated into a pivot table in worksheet analysis.Every day i have to do this process many times,That's why i propose to include in Personal macro workbook,so that with a single click it can done Regards aju v chacko -- Forwarded message -- From: Daniel dcolarde...@free.fr Date: Sun, Jul 31, 2011 at 1:57 PM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com It will be very difficult to move the code to the personal.xlsb workbook without the initial file retaining some code. Can you explain why you want to do so ? Maybe there should be alternative solutions. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : dimanche 31 juillet 2011 04:27 À : Rajan_Verma; excel macro forum Objet : Fwd: $$Excel-Macros$$ Help needed to comine VBA modules Dear rajan verma, The worksheet 'analysis' is being created during the macro run of module analysis only ,Now naming the drilled down w.sheet is possible by including the following code in w.sheet analysis,I propose to run this from the personal macro workbook,How to achieve this?. kindly help Aju v chacko Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub Cancel = True If Target.Value Then Target.ShowDetail = True ActiveSheet.Name = Target.Offset(, -1).Value End If End Sub -- Forwarded message -- From: Daniel dcolarde...@free.fr Date: Sun, Jul 31, 2011 at 12:53 AM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com I am sorry, I don’t know how to do it. Maybe it is possible to achieve it with a class module, but evenso, I’m not able to do it so far. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 17:00 À : excel macro forum Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks. Now it is working fine.But the sub 'Worksheet_BeforeDoubleClick' is included as worksheet level event routine of w.sheet analysis.How can i incorporate this in personal macro workbook.Once again thanking u for Ur help. with regards Aju v chacko On Sat, Jul 30, 2011 at 6:33 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Also add this Function to Remove Special Character From Sheet name ,Because if the Special Character include in the string it will not change the Sheet name, See the attached file.. Public Function SheetName(Shname As String) As String Dim Cod As Integer Dim ShN As String For i = 1 To Len(Shname) Cod = Asc(Mid(Shname, i, 1)) If (Cod 64 And Cod 91) Or (Cod 96 And Cod 123) Or (Cod 79 And Cod 90) Then ShN = ShN Mid(Shname, i, 1) End If Next SheetName = ShN End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Daniel Sent: Saturday, July 30, 2011 6:12 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules Here is my test file. -Message d'origine- De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 05:33 À : excel-macros@googlegroups.com Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules Dear daniel, Thanks for ur kind reply,But when i pasted this module into the module' analysis',the following line showing compilation error(red colour).error-(sub or function not defined). Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange If Intersect(Target, rg) Is Nothing Then Exit Sub If Target.Value Then End If Kindly help Aju v chacko On 7/29/11, Daniel dcolarde...@free.fr wrote: Paste the macro below in the ANALYSIS module : Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rg As Range Set rg = Sheets(ANALYSIS).PivotTables(1).DataBodyRange
$$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula
Any help in shortening or optimizing this formula would be great. Im limited by the nested if statements to match week numbers (column s) and week ended date (Column T), and continue to bog down the recalculations. It does work, but slows things down.I'm using it to determine if a date falls within a certain period in Column T =IF(A5=$T$5,$S$5,IF(AND(A5$T$5,A5=$T$6),$S$6,IF(AND(A5$T$6,A5=$T $7),$S$7,IF(AND(A5$T$7,A5=$T$8),$S$8,IF(AND(A5$T$8,A5=$T$9),$S $9,IF(AND(A5$T$9,A5=$T$10),$S$10,)) many thanks, Financeguy -- -- 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$$ How to Find Last Present Date From Month in One column
Dear Sir The formula given by Mr.Daniel is awesome but will give wrong results except for the first set. A slight modification as given below will bring out the intended result. =INDEX($A$1:$AE$1,1,MAX(IF($B2:$AE2=P,COLUMN($B2:$AE2 In the above formula, the second and the third ranges should be of mixed referencing. regards viswanathan m On Sun, Jul 31, 2011 at 5:50 PM, Daniel dcolarde...@free.fr wrote: Hi, ** ** In AF2 (array formula, validate with Ctrl+Shift+Enter) : ** ** =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2=P,COLUMN($B$2:$AE$2 ** ** Copy down. ** ** Regards. ** ** Daniel ** ** *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] *De la part de* maulik desai *Envoyé :* dimanche 31 juillet 2011 10:26 *À :* excel-macros@googlegroups.com *Objet :* $$Excel-Macros$$ How to Find Last Present Date From Month in One column ** ** Hi Group, Required Small help, I am having one excel sheet in that i have to update the Emp Attendance status for every month actul no of Emp are more then 500 I have to update their last Present date in Final column. (details given in Attached File) Currently i have to find out date manually its take long time to update. Request you to kindly provide the solution. -- Thanks Regards, Maulik Desai 9967363926 -- -- 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 -- *With warm regards* *Viswanathan M* DE(Tech) RGM TTC Chennai-600027 mviswanat...@bsnl.co.in 94861 00423 *Vande* *Mataram* -- -- 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$$ $$Excel-Macro$$ - Optimize Formula
Hi, Try this: =IF(A5=$T$5,$S$5,IF(A5=$T$6,$S$6,IF(A5=$T$7,$S$7,IF(A5=$T$8,$S$8,IF(A5=$T$9,$S$9,IF(A5=$T$10,$S$10,)) The multiple IF statement stops as soon as it finds a TRUE. So you don't need to use AND statements to create 'windows' The above asks: Is A5 greater or equal to T5? - No - Go to the next statement Is A5 greater or equal to T6? - No - Go to the next statement Is A5 greater or equal to T7? - Yes - Stop. Answer: S7 This could probably be easier done with VLOOKUP Regards - Dave. Date: Sun, 31 Jul 2011 17:50:12 -0700 Subject: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula From: velocity...@gmail.com To: excel-macros@googlegroups.com Any help in shortening or optimizing this formula would be great. Im limited by the nested if statements to match week numbers (column s) and week ended date (Column T), and continue to bog down the recalculations. It does work, but slows things down.I'm using it to determine if a date falls within a certain period in Column T =IF(A5=$T$5,$S$5,IF(AND(A5$T$5,A5=$T$6),$S$6,IF(AND(A5$T$6,A5=$T $7),$S$7,IF(AND(A5$T$7,A5=$T$8),$S$8,IF(AND(A5$T$8,A5=$T$9),$S $9,IF(AND(A5$T$9,A5=$T$10),$S$10,)) many thanks, Financeguy -- -- 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
