Re: $$Excel-Macros$$ Need a Email Macro based on the Cell time
Dear, I am not an expert, but see if the below page can give you some idea. Darwin 2012/6/5 Puttu * puttu...@gmail.com Experts, Can you have look on my requirements and provide the solution. On Mon, Jun 4, 2012 at 12:19 AM, Puttu * puttu...@gmail.com wrote: Hi Excel Experts, Need a help on the macro for automated email reminders. below is the requirements. Attached is sheet for your reference Need a macro based on the given date and time in 1st, 2nd Final reminder columns to all the email ID Subject mail should be content of cell text in the column B This Task list shouldn't be limited to only for row 6 it should be never ending (B:B) If Any Case column F status changed to Completed, after the 1st or 2 or Final Reminder, then there would be no more reminder mails Please help me on this. Appreciated all you support -- Putta -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Putta -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Need a Email Macro based on the Cell time
http://www.teachexcel.com/excel-help/excel-how-to.php?i=447580#3 2012/6/5 Darwin Chan darwin.chankaw...@gmail.com Dear, I am not an expert, but see if the below page can give you some idea. Darwin 2012/6/5 Puttu * puttu...@gmail.com Experts, Can you have look on my requirements and provide the solution. On Mon, Jun 4, 2012 at 12:19 AM, Puttu * puttu...@gmail.com wrote: Hi Excel Experts, Need a help on the macro for automated email reminders. below is the requirements. Attached is sheet for your reference Need a macro based on the given date and time in 1st, 2nd Final reminder columns to all the email ID Subject mail should be content of cell text in the column B This Task list shouldn't be limited to only for row 6 it should be never ending (B:B) If Any Case column F status changed to Completed, after the 1st or 2 or Final Reminder, then there would be no more reminder mails Please help me on this. Appreciated all you support -- Putta -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Putta -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Ask for improvement for procedure for date filling
Dear all, I have written a simple procedure to fill cells with dates, it depends on the current month and decide the range of the cells. Can anyone suggest any room for improvement for my code? Sub monthfill() Dim myrange As Range Dim mth As Integer Cells.Clear mth = month(Date) Select Case mth Case 1, 3, 5, 7, 8, 10, 12 Set myrange = Range(B1: B31) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With Case 4, 6, 9, 11 Set myrange = Range(B1: B30) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With Case 2 Set myrange = Range(B1: B28) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With End Select Set myrange = Nothing End Sub Darwin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Ask for improvement for procedure for date filling
Dear all, Thanks for your help! 2012/5/17 dguillett1 dguille...@gmail.com Sub dates() days = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) MsgBox days Set myrange = Range(b1: b” days) With myrange.Cells(1) .Value = DateSerial(Year(Date), Month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Thursday, May 17, 2012 1:15 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Ask for improvement for procedure for date filling Dear all, I have written a simple procedure to fill cells with dates, it depends on the current month and decide the range of the cells. Can anyone suggest any room for improvement for my code? Sub monthfill() Dim myrange As Range Dim mth As Integer Cells.Clear mth = month(Date) Select Case mth Case 1, 3, 5, 7, 8, 10, 12 Set myrange = Range(B1: B31) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With Case 4, 6, 9, 11 Set myrange = Range(B1: B30) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With Case 2 Set myrange = Range(B1: B28) With myrange.Cells(1) .Value = DateSerial(Year(Date), month(Date), 1) .AutoFill Destination:=myrange, Type:=xlFillDays End With End Select Set myrange = Nothing End Sub Darwin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Code for range shifting based on month
Dear all, I have a spreadsheet containing daily customer transaction data. I have used SUMIF to find out the monthly customer transaction data. However, it is tedious to copy the formula and paste for next month when next month comes. Can anyone suggest any VBA code / idea to help shifting the formula? -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Code the Range shifting.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ formula required for count or count if
Hi Sara, Seems formula COUNTIF can solve your problem simply. 2012/4/27 Sara Lee lee.sar...@gmail.com hi i have data as in the attached data. i need count of unique values of column A i.e i need output as in column AB, AC... pls advise a formula thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Sara Lee_Countif.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ formula required for count or count if
sara, the formula should workfirst highlight the range you want to implement this formula, then you should try Ctrl + Shift + Enter. 2012/4/27 Sara Lee lee.sar...@gmail.com it is not working-- anybody else can help here On Fri, Apr 27, 2012 at 4:06 AM, anil panchal anil.kan...@gmail.comwrote: you can use =SUMPRODUCT(--($A$2:$A$23=AB2)) On Fri, Apr 27, 2012 at 1:26 PM, Sara Lee lee.sar...@gmail.com wrote: yes i need count of distinct records for each load no-- sample output load 02416 load 02423 load 02452 load 02462 On Fri, Apr 27, 2012 at 3:45 AM, Abhishek Jain abhishek@gmail.comwrote: Actually count of values stored in adjacent columns of each load number is required. For Load 0241 - there are 6 values so the formula should return 6. On Fri, Apr 27, 2012 at 1:09 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Hi Sara, Seems formula COUNTIF can solve your problem simply. 2012/4/27 Sara Lee lee.sar...@gmail.com hi i have data as in the attached data. i need count of unique values of column A i.e i need output as in column AB, AC... pls advise a formula thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks Regards Anil Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
Re: $$Excel-Macros$$ formula required for count or count if
See if you understand array formula. http://www.excelfunctions.net/Excel-Array-Formulas.html 2012/4/27 Sara Lee lee.sar...@gmail.com thee are two similar rows with same load no---this is not working On Fri, Apr 27, 2012 at 4:06 AM, anil panchal anil.kan...@gmail.comwrote: you can use =SUMPRODUCT(--($A$2:$A$23=AB2)) On Fri, Apr 27, 2012 at 1:26 PM, Sara Lee lee.sar...@gmail.com wrote: yes i need count of distinct records for each load no-- sample output load 02416 load 02423 load 02452 load 02462 On Fri, Apr 27, 2012 at 3:45 AM, Abhishek Jain abhishek@gmail.comwrote: Actually count of values stored in adjacent columns of each load number is required. For Load 0241 - there are 6 values so the formula should return 6. On Fri, Apr 27, 2012 at 1:09 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Hi Sara, Seems formula COUNTIF can solve your problem simply. 2012/4/27 Sara Lee lee.sar...@gmail.com hi i have data as in the attached data. i need count of unique values of column A i.e i need output as in column AB, AC... pls advise a formula thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks Regards Anil Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may
$$Excel-Macros$$ To calculate the maximum and minimum appearance in Staff Roster Plan
Dear all, I have a roster with staff allocated on different roster plan, i.e. different working hours. I have a task to analyse the maximum and minimum of staff who would appear at concurrent period. However, volume of data is huge and formula is needed for assistance in analysis. I have attached one file for simplicity. Any help would be appreciated. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Analysis on roster plan.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ To calculate the maximum and minimum appearance in Staff Roster Plan
Dear Pawel, yes, this is what i m looking for, thanks for your help~[?] 2012/4/3 pawel lupinski lupins...@yahoo.com I realy don't know what are you looking for so check if this is what you are looking for. Regards, Pawel *From:* Darwin Chan darwin.chankaw...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Tuesday, April 3, 2012 12:02 PM *Subject:* $$Excel-Macros$$ To calculate the maximum and minimum appearance in Staff Roster Plan Dear all, I have a roster with staff allocated on different roster plan, i.e. different working hours. I have a task to analyse the maximum and minimum of staff who would appear at concurrent period. However, volume of data is huge and formula is needed for assistance in analysis. I have attached one file for simplicity. Any help would be appreciated. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com 332.gif
Re: $$Excel-Macros$$ want to add our own custom button and Tab in Excel 2007 name in place of add-in
Hi Noorain, Regarding the code provided from above, do the custom control button and tab include all operations too? For example, the sorting button, the operations include in the following code? I would like to learn more from this. .Caption = Sort Names Descending .OnAction = SortList .Parameter = Dsc 2012/3/29 NOORAIN ANSARI noorain.ans...@gmail.com See attached add-in's coding for reference On Wed, Mar 28, 2012 at 9:22 PM, Divaker Pandey divake...@gmail.comwrote: Hi Expert, I want to add our own custom button and Tab in Excel 2007 name in place add-in. Divaker -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ checkbox query
Dear all, Sorry for asking late. I use the code from KRIS and put in SHANKAR file, however, the below error happened. Could SHANKAR please share your new file with KRIS code? Also, I found there is some output in the Intermediate window, could anyone provide some information/ resources on how to use it? 2012/3/22 Shankar Bheema shankar.n...@gmail.com Dear Noorain The query solved by Mr KRIS krishnak...@gmail.com is catering my need. Thank you for your cooperation my dear friend. On Tue, Mar 20, 2012 at 6:02 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Shankar, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 20, 2012 at 11:32 AM, Shankar Bheema shankar.n...@gmail.comwrote: Dear experts I have attached an excel file containing data of qualification. On the userform I created check boxes with similar to the datasheet data. I need action like, only the tickmarked check box data only shown on the data sheet all other have to go in hidden mode (even in print also). waiting for your reply. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Best Regards, Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ VBA Integration with Lotus Notes
Dear group, After browsing the topics in this group, I found excel VBA could integrate with Lotus Notes. I found some VBA code useful for the integration, but do not know whether extra library may be needed for installation. Can anyone suggest do I need to install the library? where can find the library which is suitable for my Lotus Notes version? My Version: Lotus Notes: 8.5 MS Excel: 2000 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Synchronization on same set of data on 2 charts
@Noorain, Let me study the chart first. Thanks a lot! @Don, The fact is the excel version at my workplace is 2000, i have no choice. Thanks anyway~ 2012/2/10 dguillett1 dguille...@gmail.com I don’t have 2000 but would think you would have to develop in 2000 and hope the later versions can use it. OR, UPGRADE. Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, February 10, 2012 3:33 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Synchronization on same set of data on 2 charts Dear Noorain, I did it but results are shown like this. Are there other ways for Excel 2000 version? Because I use Excel 2000 at my office while Excel 2007 and 2010 at home. 2012/2/10 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Please see attached ScreenShot, Hope it will help to you. On Fri, Feb 10, 2012 at 2:27 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Dear group, *Synchronization on same set of data on 2 charts* 1. In my file, 2 charts connecting same set of data show different x-axis. Can anyone point out what is my fault in the file? 2. Also, can anyone can provide a better solution for attaching some kind of control like a scroll bar so that it would look like a dynamic dashboard. Please see the attachment for details with appreciation, thanks a lot! -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members
Re: $$Excel-Macros$$ Index and Match
Dear Noorain, Sorry for the late reply. What is the use of *small function* and *row function* here? How could they use together to get the desired results? Thanks so much for explanation in advance!! 2012/2/7 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Actually, It was query of one of our group member Mr. Pravesh kashyap, He wanted to extract all vertical data in horizontal way as per provided heading. In this case.Heading is repeating again by again in column A below function was used to extract column B data in horizontal way against Duplicate Column A heading * =INDEX($B$3:$B$1930,SMALL(IF($A$3:$A$1930=D$2,ROW($B$3:$B$1930),),ROW(A1))-2) *this function is array function so need to press CSE. I hope my explanation will helpful for you. Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Feb 7, 2012 at 3:14 PM, Darwin Chan darwin.chankaw...@gmail.comwrote: Thanks all, the reason is i m reviewing the use of index and match. Found when browsing this group from another thread, can anyone help to explain how is the logic? 2012/2/7 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Please try it without using helping coloun. =INDEX($A$2:$A$14,MATCH(SMALL($B$2:$B$14,ROW(A1)),$B$2:$B$14,0)) See attached sheet.. On Tue, Feb 7, 2012 at 2:09 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Hi group, Currently I m reviewing the use of Index and match. However, anyone can point out what is missing in my forumla? -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge
Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it
Dear Noorain, Thanks for your support!! 2012/2/7 NOORAIN ANSARI noorain.ans...@gmail.com Add-In Excel 2003 Version On Thu, Feb 2, 2012 at 5:24 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Darwin/ Bé Trần Văn, Recently it will available in Excel Version 2003. On Thu, Feb 2, 2012 at 5:18 PM, Bé Trần Văn betnmtdongna...@gmail.comwrote: 2012/2/2 Excel_Lover idforex...@gmail.com great, Thanks for sharing.. On Wed, Feb 1, 2012 at 12:39 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Noorain, Is there any version for Excel 97 - 2003? Thanks~ 2012/2/1 NOORAIN ANSARI noorain.ans...@gmail.com In Excel 2007 Version On Wed, Feb 1, 2012 at 2:21 PM, Abdulgani Shaikh itpabdulg...@gmail.com wrote: in which Excel version it will work ? On Wed, Feb 1, 2012 at 1:55 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Abdul, PFA -- Forwarded message -- From: ITP Abdulgani Shaikh itpabdulg...@gmail.com Date: Wed, Feb 1, 2012 at 11:28 AM Subject: Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it To: excel-macros@googlegroups.com I am unable to download the Add in, can you please email it to me. Regards On Mon, Jan 30, 2012 at 9:09 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Group, Find attached the Excel add-ins, which simply fantastic in our routine work. Just open the attachment in excel file and you can see there is an additional menu appears to your excel tab “My Menu” develop by one of our Group member Mr. Ashish Koul You can see almost all the functions we use are available in the menu http://www.excelvbamacros.com/2012/01/my-menu-functions-and-help.html -- -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan
$$Excel-Macros$$ Index and Match
Hi group, Currently I m reviewing the use of Index and match. However, anyone can point out what is missing in my forumla? -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com IndexMatch.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Fwd: Need help in building pivot using vba
. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Most Helpful Member Jan'12 - Noorain Ansari
, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com image001.jpg517.gifB68.gif
Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it
Noorain, Is there any version for Excel 97 - 2003? Thanks~ 2012/2/1 NOORAIN ANSARI noorain.ans...@gmail.com In Excel 2007 Version On Wed, Feb 1, 2012 at 2:21 PM, Abdulgani Shaikh itpabdulg...@gmail.comwrote: in which Excel version it will work ? On Wed, Feb 1, 2012 at 1:55 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Abdul, PFA -- Forwarded message -- From: ITP Abdulgani Shaikh itpabdulg...@gmail.com Date: Wed, Feb 1, 2012 at 11:28 AM Subject: Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it To: excel-macros@googlegroups.com I am unable to download the Add in, can you please email it to me. Regards On Mon, Jan 30, 2012 at 9:09 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Group, Find attached the Excel add-ins, which simply fantastic in our routine work. Just open the attachment in excel file and you can see there is an additional menu appears to your excel tab “My Menu” develop by one of our Group member Mr. Ashish Koul You can see almost all the functions we use are available in the menu http://www.excelvbamacros.com/2012/01/my-menu-functions-and-help.html -- -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums
Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Asa, First thank you very much for suggesting why there is an error. As a newbie in VBA programming, I really dont know much about OPTION EXPLICIT, only to the extent it requires us to define variables in every procedure. When i put OPTION EXPLICIT in original code, I find there is error showing internal procedure. After revising the ElseIf Not... I use the following code.however, the problem is only solved by On error resume Next only, any other way out? Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) On Error Resume Next If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If If IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = Else .Cells(r, 15).Value = VBA.Mid(.Range(E r), (InStr(.Range(E r), X) + 1), 3) End If .Cells(r, 16).Value = Cells(r, 6) Next r .UsedRange.Columns.AutoFit End With End Sub 2012/1/30 Asa Rossoff a...@lovetour.info Hi Darwin, Your ElseIf Not… lines are redundant, you can simply say Else for those lines, since that is what the ElseIf line already does. ** ** In the line .Cells(r, 14).Value = VBA.Left(.Range(E r), (*InStr(.Range(E r), X) - 1*)) If the cell at .Range(E r) does not contain an X, then Instr() will return 0, and then you subtract 1 from that, and ask for -1 characters using Left(). That is most likely the problem. ** ** I also highly recommend putting OPTION EXPLICIT at the top of every module and declaring all variables that you use with Dim, Static, etc. as appropriate. Otherwise it can make debugging much more difficult if you have a small typo in a variable name, as well as create other surprising problems caused by using variant type variables (the default) for everything. ** ** You switch between using Cells() and Range() for your references. In a case like this where both seem to be being used interchangably, I recommend sticking to one for ease of code comprehension, debugging, and code speed. I would use Cells in this case as there is less work for Excel to do to interpret it and it is probably a little faster. ** ** There is almost never any need to reference the VBA library name in your code. You can, for example, just use Left(string, length) -- no need for VBA.Left(…). ** ** All the best! Asa ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Darwin Chan *Sent:* Monday, January 30, 2012 12:11 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells ** ** Dear all, ** ** I have composed some code for this, however, there is error happened, can anyone suggest to solve? Highlight in yellow has problem. ** ** Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num ** ** For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) ** ** *If IsEmpty(.Cells(r, 5)) Then* *.Cells(r, 14).Value = * *ElseIf Not IsEmpty(.Cells(r, 5).Value) Then* *.Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1))* *End If* ** ** *If IsEmpty(.Cells
Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells
Hi Paul, Thanks a lot for your explanation first on how to use option explicit. Let me try the code when I m back to office!! 2012/1/30 Paul Schreiner schreiner_p...@att.net First of all, I'll explain Option Explicit. In ANY programming language, you have the ability to define variables to represent values. In this case, you're using r to increment the Row number. Now, in all programming, the variables have to be Declared. that is, you have to tell the language Compiler what type of variable it is. text string, integer number, floating point number (Boolean (true/false)) In VBA, the compiler allows the variable to be Declared the first time it is used. Sometimes, that's not a good thing. Let's say that the values in a list of cells is being copied to a variable. the values are supposed to be numbers which are then used in calculations. The first value is read, but since it's the column heading, it is text, so the variable is declared as a String. then, when the next value is read, it is stored in a String variable, which doesn't work well in calculations. Also.. let's say that the variable you used is for the row number is: rNumber. But in one instance, you accidently type it as: rNubmer The first time the code uses rNubmer it will automatically Declare is, and the code continues with unexpected results. So, the best course of action is to ALWAYS declare the variable, just so you can control the TYPE of variable. Using Option Explicit tells the compiler that ALL variables must be declared. So if it encounters a variable that has not been declared, it assumes that it is an error (which, if you simply failed to declare it is really more of an oversight). --- Next. The problem is with: If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If In your sample file, the values in column E are NOT blank (empty) they have a space in them. So, the script assumes that if it is not blank, then it MUST have a X in the string. The error occurs when the Instr() function runs, finds no X, returns 0. then, the Left() command results in: Left ( ,-1) which causes an error. I'd suggest using: If (InStr(.Cells(R, E).Value, X) 0) Then .Cells(R, N).Value = Left(.Cells(R, E).Value, (InStr(.Cells(R, E).Value, X) - 1)) Else .Cells(R, N).Value = End If *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* Darwin Chan darwin.chankaw...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Mon, January 30, 2012 4:25:57 AM *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells Hi Asa, First thank you very much for suggesting why there is an error. As a newbie in VBA programming, I really dont know much about OPTION EXPLICIT, only to the extent it requires us to define variables in every procedure. When i put OPTION EXPLICIT in original code, I find there is error showing internal procedure. After revising the ElseIf Not... I use the following code.however, the problem is only solved by On error resume Next only, any other way out? Private Sub CommandButton2_Click() 'Inserting columns and fill with strings, I basically get the idea from our group With Sheets(CHS EDI) .Cells(1, 2).Value = E/F .Cells(1, 7).Value = Unit Prefix .Cells(1, 8).Value = Unit No .Cells(1, 9).Value = Estimate Date .Cells(1, 10).Value = Component Code .Cells(1, 11).Value = Location Code .Cells(1, 12).Value = Damage Code .Cells(1, 13).Value = Repair Code .Cells(1, 14).Value = Length .Cells(1, 15).Value = Width .Cells(1, 16).Value = Num For r = 2 To .Cells(Rows.Count, C).End(xlUp).Row .Cells(r, 7).Value = VBA.Left(.Range(A r), 4) .Cells(r, 8).Value = VBA.Mid(.Range(A r), 5, 7) .Cells(r, 9).Value = VBA.Format(.Range(C r), dd/mm/) .Cells(r, 10).Value = VBA.Left(.Range(D r), 3) .Cells(r, 11).Value = VBA.Mid(.Range(D r), 5, 4) .Cells(r, 12).Value = VBA.Mid(.Range(D r), 10, 2) .Cells(r, 13).Value = VBA.Mid(.Range(D r), 13, 2) On Error Resume Next If IsEmpty(.Cells(r, 5)) Then .Cells(r, 14).Value = Else .Cells(r, 14).Value = VBA.Left(.Range(E r), (InStr(.Range(E r), X) - 1)) End If If IsEmpty(.Cells(r, 5).Value) Then .Cells(r, 15).Value = Else .Cells(r, 15).Value = VBA.Mid(.Range(E r), (InStr(.Range(E r), X) + 1), 3) End If .Cells(r, 16).Value = Cells(r, 6) Next r .UsedRange.Columns.AutoFit End With End Sub 2012/1/30 Asa Rossoff a...@lovetour.info Hi
Re: $$Excel-Macros$$ Save As today date
Dear Noorain, I try to use the code pasted here, however, run time error 1004. It may be due to the code highlight in red. What should the code be if I would like to have directory highlighted in red to be chosen by user? 2012/1/20 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Your code is working fine, but need small correction... Please use below code... Sub savemethod() Dim todaydate As String Dim fileSaveName As String todaydate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), dd-mmm-) fileSaveName = Application.GetSaveAsFilename(InitialFileName:=todaydate, fileFilter:=Excel Files (*.xls), *.xls, Title:=Please save the file) ActiveWorkbook.SaveAs Filename:=C:\Documents and Settings\noorain.ansari\Desktop\ todaydate .xls End Sub -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Fri, Jan 20, 2012 at 9:13 AM, Darwin Chan darwin.chankaw...@gmail.comwrote: Dear group, I have written a macro for auto saving as today date excel file, however, when i save this, it refuses to save. I think that it turns DateSerial(Year(Date), Month(Date), Day(Date)) to be 20/01/2012, however, it treats me to create directory. What functions should I use to auto saving as today date beside this one? **Code** ** Sub savemethod() Dim todaydate As String Dim fileSaveName As String todaydate = DateSerial(Year(Date), Month(Date), Day(Date)) fileSaveName = Application.GetSaveAsFilename(initialfilename:=todaydate, fileFilter:=Excel Files (*.xls), *.xls, Title:=Please save the file) ActiveWorkbook.SaveAs Filename:=todaydate End Sub ** -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Save As today date
Dear Noorain, I have found some method. See if there needs improvement. **Code** Sub savemethod2() Dim todaydate As String, fileSaveName As String, driveloc As String, mth As String mth = Format(Date, mm) todaydate = VBA.Format(DateSerial(Year(Date), month(Date), Day(Date)), dd-mm-) driveloc = P:\CMR Folder (For CMR staff only)\CMR Report\CHS EDI \ Year(Date) \ Year(Date) , mth fileSaveName = Application.GetSaveAsFilename(InitialFileName:=todaydate, fileFilter:=Excel Files (*.xls), *.xls, Title:=Please save the file) ActiveWorkbook.SaveAs Filename:=driveloc \ todaydate .xls End Sub 2012/1/20 Darwin Chan darwin.chankaw...@gmail.com Dear Noorain, I try to use the code pasted here, however, run time error 1004. It may be due to the code highlight in red. What should the code be if I would like to have directory highlighted in red to be chosen by user? 2012/1/20 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Your code is working fine, but need small correction... Please use below code... Sub savemethod() Dim todaydate As String Dim fileSaveName As String todaydate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), dd-mmm-) fileSaveName = Application.GetSaveAsFilename(InitialFileName:=todaydate, fileFilter:=Excel Files (*.xls), *.xls, Title:=Please save the file) ActiveWorkbook.SaveAs Filename:=C:\Documents and Settings\noorain.ansari\Desktop\ todaydate .xls End Sub -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Fri, Jan 20, 2012 at 9:13 AM, Darwin Chan darwin.chankaw...@gmail.com wrote: Dear group, I have written a macro for auto saving as today date excel file, however, when i save this, it refuses to save. I think that it turns DateSerial(Year(Date), Month(Date), Day(Date)) to be 20/01/2012, however, it treats me to create directory. What functions should I use to auto saving as today date beside this one? **Code** ** Sub savemethod() Dim todaydate As String Dim fileSaveName As String todaydate = DateSerial(Year(Date), Month(Date), Day(Date)) fileSaveName = Application.GetSaveAsFilename(initialfilename:=todaydate, fileFilter:=Excel Files (*.xls), *.xls, Title:=Please save the file) ActiveWorkbook.SaveAs Filename:=todaydate End Sub ** -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion
Re: $$Excel-Macros$$ formula
Thanks for Noorain reading notes, some tricks i even dun know. 2012/1/17 NOORAIN ANSARI noorain.ans...@gmail.com Dear Manoj, Please study attached material and follow this Excel-Macro group regularly. Hope it will help to u -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Jan 16, 2012 at 11:51 PM, Manoj Kumar kmrmanoj1...@gmail.comwrote: Hello Members i want grow my excel formula knowledge. Please provide help and some notes . regards Manoj -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ VBA code for pasting date, not plain text
Dear all, I would choose dd/mm/ format first, however, should I standardize all the date format first before running the macro, or simply use the code from Paul as, If IsEmpty(Cells(i, 5)) Then Cells(i, 5).Value = Cells(i - 1, 5).Value Else Cells(i, 5).Value = Cells(i, 5).Value End If Or from Rajan, Sub CopyDate() Dim rngDate As Range Set rngDate = Selection *ActiveCell.Resize(rngDate.Rows.Count, 1).Value = rngDate.Value* End Sub Thanks~ 2012/1/5 dguillett1 dguille...@gmail.com Thanks Paul, Don’t remember encountering that. However, when I changed the destination date, formulas calculated... 1/1/2012 2/1/2012 Don Guillett SalesAid Software dguille...@gmail.com *From:* Paul Schreiner schreiner_p...@att.net *Sent:* Thursday, January 05, 2012 8:12 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ VBA code for pasting date, not plain text Actually, I almost made the same mistake! What's really happening is that HIS Windows environment is configured to display dates as dd/mm/ so to HIM both date formats look the same. But since OUR environment settings use mm/dd/, then when we open the workbook, all legitimate Excel dates (which are really numbers displayed in date format) change to display as our configuration directs it. But the STRING values remain unchanged. I guess what I'm saying is that to US, the look like they're in different formats, but to HIM, they may look identical. The problem is that one is a date string while the other is a number DISPLAYED as a date. So, your reply is probably totally confusing to him! *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* dguillett1 dguille...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, January 5, 2012 9:05:16 AM *Subject:* Re: $$Excel-Macros$$ VBA code for pasting date, not plain text You may want to reconsider what you are asking for. One set dd/mm/ and the other mm/dd/ Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Thursday, January 05, 2012 3:42 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ VBA code for pasting date, not plain text Dear group, I have written a macro for recording data for customer transaction every hour within a day. First I refresh the pivot table and copy the data to another area for ease to lookup. However, I found the VBA code i use just paste the date with wrong format. This makes me cannot lookup the desire value. Could anyone help to improve my code? *Remarks: Procedure name is sp -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may
Re: $$Excel-Macros$$ Happy Birth Day
Once again, NOORAIN, happy birthday~~ 2012/1/3 NOORAIN ANSARI noorain.ans...@gmail.com Thank you very much to all of you. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Jan 2, 2012 at 8:22 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hello Group, Today we have birthday of One of our Excel Champ, He has been recognised Most Helpful member on this group many times. *Noorain *has a sound knowledge of Excel and happy to help other peoples . So after New year we have another party on 2nd January J. Lets Wish Noorain a very Very Happy Birht Day. ** ** *Many Many happy return of the day Noorain.* * * *Keep helping Peoples.* * * *Thanks and Regards* Rajan Verma ** ** -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Instead of using Isempty(), what other ways can be used for filling up the data
Dear all, I have created a report to copy the data from a sheet to another. However, i use isempty to check where to paste the data, but I found there is performance issue. Is there any other way out other than using Isempty()?? File: http://www.sendspace.com/file/64pseo Below can find the code and also the file. Sub reportdata() 'Paste the data according to the date of report Dim dt As Integer Dim i As Integer Dim datarow As Integer Dim rpdate As String 'Get the date of report need to finish (without data) Sheets(Summary).Activate datarow = Range(A Rows.Count).End(xlUp).Row For i = 166 To datarow If* IsEmpty*(Cells(i, 3)) Then rpdate = Format(Left(Cells(i, 3).Offset(0, -2).Value, 10), -mm-dd) Exit For End If Next Sheets(PivotTable).Cells(2, 10).Value = rpdate dt = Sheets(PivotTable).Cells(2, 12).Value '20', 40, RF marshalling btw CY/CFS and MR/CMR AND disc/load btw BH/V/L and MR/CMR Sheets(PivotTable).Range(L4).Resize(12, 74).Copy Worksheets(Summary).Cells(dt, 2).PasteSpecial (xlPasteValues) End Sub -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ COUNT Unique using Frequency
Dear all, I have a excel table with duplicate bill id, i want to get the unique count. However, it should also consider another criteria, which is customer. Should I use advanced filter instead? How should the advanced filter be set? -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Countif and Sumif question.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Insert rows in tables for comparison
Dear all, I have 2 tables concerning sales volume of several customers across 2 months. I want to make a comparison on the sales volume, however, some customer buy service in previous month may not buy in the next month and vice versa. This makes such a tedious task by inserting rows in both tables and fill up with zero and then to calculate the change in sales volume. Does anyone have solutions to create a macro or any other solutions? Below please find the attachment. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Insert row question.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Sumproduct for multiple criteria lookup
Dear group, I have got an excel file getting sales volume of different customers in buying different services on a daily basis. I want to use sumproduct formula to get the sales volume buying services on a monthly basis. However, the desired results are not found. Please help. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Excel Sumproduct questions.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Sumproduct for multiple criteria lookup
Thanks Noorain, I got the problem solved!! 2011/12/9 NOORAIN ANSARI noorain.ans...@gmail.com Dear Darwin, Please see attached sheet. Hope it will help you.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Fri, Dec 9, 2011 at 8:03 AM, Darwin Chan darwin.chankaw...@gmail.comwrote: Dear group, I have got an excel file getting sales volume of different customers in buying different services on a daily basis. I want to use sumproduct formula to get the sales volume buying services on a monthly basis. However, the desired results are not found. Please help. -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Help for If nesting formula
Hi Singh, Would you explain the logic for the file? If 1 value is bigger than another 1, you would like the cell to return something, am i right?? Darwin 2011/11/30 lucky singh lucky60...@gmail.com Hello Team, Require your help for If nesting formula please see the attachment. Regards, Lucky -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet
Hi Don, 1. How if I want to copy specific columns from ci file to paste on mr and cy sheet? Need to set range first? 2. What code can be added to make the location to be split into parts? just like sample shown in mr and cy sheet? 2011/10/29 dguillett1 dguille...@gmail.com I did this in the ci file after adding mr sheet and cy sheet Sub CopyMRandCY_SAS() Application.Goto Sheets(ci).Range(a1) lr = Cells(Rows.Count, 1).End(xlUp).Row With Range(A4:as lr) .AutoFilter Field:=39, Criteria1:==*mr* .SpecialCells(xlCellTypeVisible).Copy Sheets(mr).Range(a1) .AutoFilter .AutoFilter Field:=39, Criteria1:==*cy* .SpecialCells(xlCellTypeVisible).Copy Sheets(cy).Range(a1) .AutoFilter End With End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, October 28, 2011 6:27 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Don, I used your method, however, it is part of my requirement. Next will be spliting the data into 2 according to the location of the data. For example, 3MR3C14015 to be put in worksheet MR, others put in CY. 2011/10/29 dguillett1 dguille...@gmail.com What you do NOT understand that is if you do it your way, it will be a separate filter and delete rows for each item. You could use a SELECTCASE method with a loop from the bottom up to inspect each cell. Again, slow I STRONGLY suggest that you adapt my suggested method... Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, October 28, 2011 10:25 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Hi Don, it is part of the requirement. however, the MYLIST seldom change so I intently hard code into it first. For others, it is too complicate too I think. Please refer to my new file. The requirements are more concrete. Thanks. Darwin 2011/10/28 dguillett1 dguille...@gmail.com Let me see your file(s) after you redo it. Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, October 28, 2011 9:55 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Thanks Don, Your template gives me more insight on VBA code! As I found the whole code is different to reexamine, I revise the documents into one with more concrete requirement, hope that everyone can easily understand. Thanks again! Darwin Chan 2011/10/28 dguillett1 dguille...@gmail.com If you are talking about the ci file deleting rows based on filtering then make a list and NAME it with an automatic defined name such as mylist and fill in the list to delete and fire this. See attached file. I have done this in the ci file but you can modify to run from the other file. Sub DeleteRowsCol_I_SAS() With Sheets(ci) lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range(I1) = VAL .Range(I1:I lr).AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Range(MYLIST), Unique:=False .Rows(2).Resize(lr).Delete .Range(i1) = .ShowAllData End With End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Thursday, October 27, 2011 11:07 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Dear all, I get 1 task of changing the code written by predecessor to comparing the data from several sheets. 1. In the sheets it use several times, I found the original very complicated, can anyone suggest to simplify the code? 2. When execute the procedures, I found some errors, i think it may be due to predecessor partly use macro recording. Detail of how is the logic can be seen from the file. Thanks in advance! Darwin -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES
Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet
Don, I used your method, however, it is part of my requirement. Next will be spliting the data into 2 according to the location of the data. For example, 3MR3C14015 to be put in worksheet MR, others put in CY. 2011/10/29 dguillett1 dguille...@gmail.com What you do NOT understand that is if you do it your way, it will be a separate filter and delete rows for each item. You could use a SELECTCASE method with a loop from the bottom up to inspect each cell. Again, slow I STRONGLY suggest that you adapt my suggested method... Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, October 28, 2011 10:25 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Hi Don, it is part of the requirement. however, the MYLIST seldom change so I intently hard code into it first. For others, it is too complicate too I think. Please refer to my new file. The requirements are more concrete. Thanks. Darwin 2011/10/28 dguillett1 dguille...@gmail.com Let me see your file(s) after you redo it. Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Friday, October 28, 2011 9:55 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Thanks Don, Your template gives me more insight on VBA code! As I found the whole code is different to reexamine, I revise the documents into one with more concrete requirement, hope that everyone can easily understand. Thanks again! Darwin Chan 2011/10/28 dguillett1 dguille...@gmail.com If you are talking about the ci file deleting rows based on filtering then make a list and NAME it with an automatic defined name such as mylist and fill in the list to delete and fire this. See attached file. I have done this in the ci file but you can modify to run from the other file. Sub DeleteRowsCol_I_SAS() With Sheets(ci) lr = .Cells(Rows.Count, 1).End(xlUp).Row .Range(I1) = VAL .Range(I1:I lr).AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Range(MYLIST), Unique:=False .Rows(2).Resize(lr).Delete .Range(i1) = .ShowAllData End With End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Darwin Chan darwin.chankaw...@gmail.com *Sent:* Thursday, October 27, 2011 11:07 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Compare the data across different sheets and put into one sheet Dear all, I get 1 task of changing the code written by predecessor to comparing the data from several sheets. 1. In the sheets it use several times, I found the original very complicated, can anyone suggest to simplify the code? 2. When execute the procedures, I found some errors, i think it may be due to predecessor partly use macro recording. Detail of how is the logic can be seen from the file. Thanks in advance! Darwin -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code
Re: $$Excel-Macros$$ Writing Code for creating pivot table
Swapnil, Thanks so much for your help. I could see you have added few codes ** Set ptsheet = Worksheets.Add Set rng = Sheets(2011-10-26).Range(A1) ** 1. Would like to askthat means we have to define the range before creating pivot table? 2. What if i have to create this pivot table every day. What codes i wish to change Sheets(2011-10-26) as a variable? (I copy code from books and forum, I m just newbee) Thanks! Darwin 2011/10/26 Swapnil Palande palande.swapni...@gmail.com Hi, Pls find attached excel. Regards, Swapnil. On Wed, Oct 26, 2011 at 11:23 AM, Darwin Chan darwin.chankaw...@gmail.com wrote: Dear all, Pls find the attached...and I couldn't find which button i could click for placing sample file. I can only place by replying the thread in gmaildid anyone know and thanks in advance!! Darwin 2011/10/26 Swapnil Palande palande.swapni...@gmail.com Hi, It will be easy to solve issue if you provide sample data. In your code you have not defined DataField, without data field it will show you blank pivot. Share sample data so that group can provide you correct code. Regards, Swapnil. On Wed, Oct 26, 2011 at 10:07 AM, Chan Darwin darwin.chankaw...@gmail.com wrote: Dear all, I wrote the code in creating pivot table for my source of data. However, when run the code, it prompts with the message. Run-time error '438': Object doesnt support this property or method Below please find the code also. Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Application.ScreenUpdating = False 'Add a new sheet for the pivot table Worksheets.Add 'Create the cache Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range(A1).CurrentRegion.Address) 'Create the pivot table Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range(A3)) 'Specify the fields With PT .PivotFields(street).Orientation = xlColumnField .PivotFields(condition_code).Orientation = xlColumnField .PivotFields(customer_code).Orientation = xlRowField .PivotFields(liner_code).Orientation = xlRowField .PivotFields(cont_type_code).Orientation = xlRowField .DisplayFieldCaptions = False End With Application.ScreenUpdating = True End Sub Thanks in advance!! Darwin Chan -- -- 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
Re: $$Excel-Macros$$ Writing Code for creating pivot table
Dear Swapnil, I have tried itit works... Thanks a lot! Darwin 2011/10/26 Swapnil Palande palande.swapni...@gmail.com Hi, 1. It is not necessary to define range, I have define it to avoide using Sheets(2011-10-26).Range(A1) every time in the code. After defining range, instead of Sheets(2011-10-26).Range(A1) i can simply use rng object Try to give complete reference in the code for ex: If you want to insert some value in range A1 of Sheet 1 then Range(A1).value = xyz will only work if you you have focus in Sheet1 if you use Sheets(Sheet1).Range(A1).value = xyz will work whether Sheet1 has focus or not 2. Do following code in changes in the code to create pivot table every day If Sheet name format is fixed that is -mm-dd then make following changes in the code (this is one time change, you do not have to do it every day) Dim shtname as String shtname = format(now(), -mm-dd) Set rng = Sheets(shtname).Range(A1) If Sheet name format is not fixed then use following code (this code you have to change every day) Dim shtname as String shtname = 2011-10-26 Set rng = Sheets(shtname).Range(A1) Regards, Swapnil. On Wed, Oct 26, 2011 at 12:01 PM, Darwin Chan darwin.chankaw...@gmail.com wrote: Swapnil, Thanks so much for your help. I could see you have added few codes ** Set ptsheet = Worksheets.Add Set rng = Sheets(2011-10-26).Range(A1) ** 1. Would like to askthat means we have to define the range before creating pivot table? 2. What if i have to create this pivot table every day. What codes i wish to change Sheets(2011-10-26) as a variable? (I copy code from books and forum, I m just newbee) Thanks! Darwin 2011/10/26 Swapnil Palande palande.swapni...@gmail.com Hi, Pls find attached excel. Regards, Swapnil. On Wed, Oct 26, 2011 at 11:23 AM, Darwin Chan darwin.chankaw...@gmail.com wrote: Dear all, Pls find the attached...and I couldn't find which button i could click for placing sample file. I can only place by replying the thread in gmaildid anyone know and thanks in advance!! Darwin 2011/10/26 Swapnil Palande palande.swapni...@gmail.com Hi, It will be easy to solve issue if you provide sample data. In your code you have not defined DataField, without data field it will show you blank pivot. Share sample data so that group can provide you correct code. Regards, Swapnil. On Wed, Oct 26, 2011 at 10:07 AM, Chan Darwin darwin.chankaw...@gmail.com wrote: Dear all, I wrote the code in creating pivot table for my source of data. However, when run the code, it prompts with the message. Run-time error '438': Object doesnt support this property or method Below please find the code also. Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable Application.ScreenUpdating = False 'Add a new sheet for the pivot table Worksheets.Add 'Create the cache Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range(A1).CurrentRegion.Address) 'Create the pivot table Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range(A3)) 'Specify the fields With PT .PivotFields(street).Orientation = xlColumnField .PivotFields(condition_code).Orientation = xlColumnField .PivotFields(customer_code).Orientation = xlRowField .PivotFields(liner_code).Orientation = xlRowField .PivotFields(cont_type_code).Orientation = xlRowField .DisplayFieldCaptions = False End With Application.ScreenUpdating = True End Sub Thanks in advance!! Darwin Chan -- -- 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$$ Make database with user form i have made
Dear all, I am new to Excel VBA, esp for treating it database. Could hemal explain how to use it? Also, I cannot open the file from Noorain, my excel version is 2003. Thanks a lot! Darwin 2011/9/23 NOORAIN ANSARI noorain.ans...@gmail.com Dear Hemal, Please try below code, sheet is attached for ur help. Private Sub CommandButton2_Click() Dim i As Long Application.ScreenUpdating = False Application.EnableEvents = False i = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 With frmSalary Sheet1.Cells(i, 1).Value = .txtEmpcode.Value Sheet1.Cells(i, 2).Value = .txtFname.Value Sheet1.Cells(i, 3).Value = .txtMname.Value Sheet1.Cells(i, 4).Value = .txtLname.Value Sheet1.Cells(i, 5).Value = .txtFullname.Value Sheet1.Cells(i, 6).Value = .txtB.Value Sheet1.Cells(i, 7).Value = .txtDA.Value Sheet1.Cells(i, 8).Value = .txtHRA.Value Sheet1.Cells(i, 9).Value = .txtDed.Value Sheet1.Cells(i, 10).Value = .txtNet.Value Sheet1.Cells(i, 11).Value = .txtAnn End With Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Fri, Sep 23, 2011 at 1:23 PM, hemal shah hemali...@gmail.com wrote: Hello Friends, I have made user form for salary in excel vba editor. I want to generate database by using that user form. Kindly help me for this. See the attachment -- -- 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
