$$Excel-Macros$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
Hi, Try this. Evaluate(=Text(Day(Sheets(Sheet1).Cells(count, BarDate).value), )) Regards, Lalit Mohan On Wednesday, 27 June 2012 10:13:06 UTC+5:30, tangledweb wrote: I believe this is the equivalent of your second new solution? Evaluate(=Text(Day(Sheets(RawData).Cells(count, BarDate).Text), )) I tried it with and without the .text part. Both cases returned error 2015 though I have a 4 digit year and the form is month/day/year which is supposed to be what it wants. On Tuesday, June 26, 2012 9:30:29 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this Evaluate(=TEXT(DAY(A1),)) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:45:46 UTC+5:30, tangledweb wrote: This seems to be designed to work directly in excel instead of a VB program. I tried to use it without the = but it said the function TEXT was not defined. I need a method that works in a VB program with the value 6/26/2012 stored in the array previously listed. On Tuesday, June 26, 2012 9:01:08 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this =TEXT(DAY(A1),) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:17:02 UTC+5:30, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
Hi, Try this revised formula as my last post contain some error Evaluate(=Text(Day( Sheets(Sheet1).Cells(count, BarDate).Text ), )) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 11:59:36 UTC+5:30, Lalit_Mohan wrote: Hi, Try this. Evaluate(=Text(Day(Sheets(Sheet1).Cells(count, BarDate).value), )) Regards, Lalit Mohan On Wednesday, 27 June 2012 10:13:06 UTC+5:30, tangledweb wrote: I believe this is the equivalent of your second new solution? Evaluate(=Text(Day(Sheets(RawData).Cells(count, BarDate).Text), )) I tried it with and without the .text part. Both cases returned error 2015 though I have a 4 digit year and the form is month/day/year which is supposed to be what it wants. On Tuesday, June 26, 2012 9:30:29 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this Evaluate(=TEXT(DAY(A1),)) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:45:46 UTC+5:30, tangledweb wrote: This seems to be designed to work directly in excel instead of a VB program. I tried to use it without the = but it said the function TEXT was not defined. I need a method that works in a VB program with the value 6/26/2012 stored in the array previously listed. On Tuesday, June 26, 2012 9:01:08 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this =TEXT(DAY(A1),) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:17:02 UTC+5:30, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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$$ Label record sheet
Dear all experts I've some questions about functions on the sheet, I made the sheet, So it use for record the label for production I tried and tried to do with many fomular, but the results are not OK :( I posted that sheet for all, Please discipline for me Very thanks Rdgs Attapan Chainarongboon Production control Dept (Compressor) Calsonic Kansei (Thailand) E-mail : attapan_chainarongb...@ck-mail.com Phone : (+66) 038-210-129 #101 Mobile phone: (+66) 086-824-5045 -- -- 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 record_label.xlsx Description: record_label.xlsx
Re: $$Excel-Macros$$ Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
Hi, You can use below formula if you don't want to use vba. =TEXT(C2, ) and if you want to use vba code then use below function format(cdate(02/05/2012), ) Regards, Swapnil. On Wed, Jun 27, 2012 at 9:17 AM, tangledweb domainqu...@gmail.com wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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 -- -- 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$$ Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
You can also change custom format . Regards, Swapnil. On Wed, Jun 27, 2012 at 2:14 PM, Swapnil Palande palande.swapni...@gmail.com wrote: Hi, You can use below formula if you don't want to use vba. =TEXT(C2, ) and if you want to use vba code then use below function format(cdate(02/05/2012), ) Regards, Swapnil. On Wed, Jun 27, 2012 at 9:17 AM, tangledweb domainqu...@gmail.comwrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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 -- -- 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$$ Remove Punctuation
Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Enable Disable Ctrl+D with passward
Dear, I think using Ctrl + Z will allow you to take back the action you performed i.e Undo the last action ;-). This should help. If not, check other experts advice. Thanks, On Wed, Jun 27, 2012 at 12:33 PM, Muralidhar E emuralidha...@gmail.comwrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- *Thanks Regards,* ** *Muralidhar E.* -- -- 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 -- Ahmed Bawazir *احمد باوزير* -- -- 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$$ Remove Punctuation
pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.com wrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ MY NEW EXCEL DASHBOARD PROJECTS
Thanks Hilary On Tue, Jun 26, 2012 at 3:37 PM, hilary lomotey resp...@gmail.com wrote: Hi Amit Click on the column header you want to hide, in my case column H, which will highlight the whole column and hold ctrl + shift + forward arrow key , right click and select hide, same is true for the row headers. thanks On Tue, Jun 26, 2012 at 9:54 AM, Amit Gandhi silkyro...@gmail.com wrote: Nice Creativity Hilary As I am new to these things, can I know how you Freezed Columns A to G and Rows 1 to 18 (and others Column Row headers are not showing)? How you freezed scrolling thereafter? Regards Amit On Mon, Jun 25, 2012 at 7:41 PM, hilary lomotey resp...@gmail.comwrote: Hello Guys i just completed my two excel projects and want to share. The first one is like an accounting system where you can enter clients information as well as transactions and post it to a database and also able to retrieve the information. Since i am not good with VBA , i used macro recording techniques to achieve these as well as of course formula's. Try it out and have fun and hope someone can improve upon it and give feedback. the pass word on the file is 'kiss The second is a fixed income dashboard, its easy to understand when you test how it works, keep excelling cos excel is fun, have a great day. -- -- 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 -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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 -- -- 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 -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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$$ Enable Disable Ctrl+D with passward
Hi, But it will not come to the notice at the same time, it was accidently going on, iam not observing it later it was effecting to the data. On Wed, Jun 27, 2012 at 3:09 PM, Ahmed Honest ahmedhon...@gmail.com wrote: Dear, I think using Ctrl + Z will allow you to take back the action you performed i.e Undo the last action ;-). This should help. If not, check other experts advice. Thanks, On Wed, Jun 27, 2012 at 12:33 PM, Muralidhar E emuralidha...@gmail.comwrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- *Thanks Regards,* ** *Muralidhar E.* -- -- 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 -- Ahmed Bawazir *احمد باوزير* -- *Thanks Regards,* ** *Muralidhar E.* -- -- 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$$ Remove Punctuation
Anil Ji, That is working fine , Thanks.. Ok is there any coding for direct remove without user form. --- i mean writing coding and run. Thanks again. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:15 PM, Anil Gawli gawlianil8...@gmail.com wrote: pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.com wrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd -- -- 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
$$Excel-Macros$$ Need a VBA code
Hello Experts, I need a quick VBA code. We have column where we enter a name in First Name Last Name format. When someone enters a name in that cell, it should automatically display the name in the same cell in the Last Name, First Name format (which is Outlook format). Please let me know if anything is unclear. Thank you, Sharath -- -- 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 VBA code
dear Sharath Pl share u r data with us... Regards, Gawli Anil On Wed, Jun 27, 2012 at 3:21 PM, sharath chandra sharath.c.sambr...@gmail.com wrote: Hello Experts, I need a quick VBA code. We have column where we enter a name in First Name Last Name format. When someone enters a name in that cell, it should automatically display the name in the same cell in the Last Name, First Name format (which is Outlook format). Please let me know if anything is unclear. Thank you, Sharath -- -- 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 -- Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd -- -- 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$$ Re: Need a VBA code
Hi, Try this on sheet selection change event Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value Then Target.Value = BreakName(Target.Value) End If End Sub Function BreakName(ByVal strName As String) BreakName = Split(strName, )(1) Split(strName, )(0) End Function Regards, Lalit Mohan On Wednesday, 27 June 2012 15:21:32 UTC+5:30, sharath chandra wrote: Hello Experts, I need a quick VBA code. We have column where we enter a name in First Name Last Name format. When someone enters a name in that cell, it should automatically display the name in the same cell in the Last Name, First Name format (which is Outlook format). Please let me know if anything is unclear. Thank you, Sharath -- -- 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$$ Remove Punctuation
Thanks Anil Ji, its superb working. Anil Ji, Is there any option for removing all the punctuation mark at a time with code and without userform help ? Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:36 PM, Anil Gawli gawlianil8...@gmail.com wrote: sorry for wrong attachment just see this attachment On Wed, Jun 27, 2012 at 3:30 PM, Anil Gawli gawlianil8...@gmail.comwrote: just press ctrl+shift+A it works now. Regards, Gawli Anil. On Wed, Jun 27, 2012 at 3:20 PM, Deba Ranjan drdeva...@gmail.comwrote: Anil Ji, That is working fine , Thanks.. Ok is there any coding for direct remove without user form. --- i mean writing coding and run. Thanks again. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:15 PM, Anil Gawli gawlianil8...@gmail.comwrote: pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.comwrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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 To unsubscribe, send a blank email to
Re: $$Excel-Macros$$ Enable Disable Ctrl+D with passward
Dear Murali, Please try it.. Private Sub Workbook_Open() Application.OnKey ^d, End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey ^d End Sub -- Thanks regards, Noorain Ansari www.noorainansari.com www.excelmacroworld.blogspot.com On Wed, Jun 27, 2012 at 3:03 PM, Muralidhar E emuralidha...@gmail.com wrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- Thanks Regards, Muralidhar E. -- -- 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 -- -- 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$$ Enable Disable Ctrl+D with passward
Dear Murali, See attached sheet for reference. On Wed, Jun 27, 2012 at 3:56 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Murali, Please try it.. Private Sub Workbook_Open() Application.OnKey ^d, End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey ^d End Sub -- Thanks regards, Noorain Ansari www.noorainansari.com www.excelmacroworld.blogspot.com On Wed, Jun 27, 2012 at 3:03 PM, Muralidhar E emuralidha...@gmail.com wrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- Thanks Regards, Muralidhar E. -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Murali(Please Check).xlsm Description: Binary data
Re: $$Excel-Macros$$ Re: extract list of values based on criteria using formula
FANTASTIC ITS WORKING % HURRAY Thanks Lalit and Raj for the assistance On Wed, Jun 27, 2012 at 2:46 AM, Lalit_Mohan mohan.pande...@gmail.comwrote: Dear Hilary Try this in column O4 =IF(LARGE(($L$3:$L$21=IF($K$23 = ,$L$23,$K$23))*(IF($K$23=,TRUE,$L$3:$L$21 =$L$23))*($L$3:$L$21),ROW($A1)) = 0, , LARGE(($L$3:$L$21=IF($K$23 = ,$L$23,$K$23))*(IF($K$23=,TRUE,$L$3:$L$21 =$L$23))*($L$3:$L$21),ROW($A1))) it's an array formula so apply with CTRL + SHIFT + ENTER and drag down Hope it will help you Regards, Lalit Mohan On Wednesday, 27 June 2012 00:17:15 UTC+5:30, hilary lomotey wrote: Hello Again i tried extracting a unique list based on criteria such that if i choose a criteria such as list between 5000 to 1, i want to see that list alone and if i choose a list between 10001 to 4 i want to see that list alone, i am struggling with the condition that create the array, kindly help with 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Remove Punctuation
it is possible but how u hold the values and replace it with others, for that u have to save the value as temp and then replace it. Regards, Gawli Anil On Wed, Jun 27, 2012 at 3:43 PM, Deba Ranjan drdeva...@gmail.com wrote: Thanks Anil Ji, its superb working. Anil Ji, Is there any option for removing all the punctuation mark at a time with code and without userform help ? Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:36 PM, Anil Gawli gawlianil8...@gmail.comwrote: sorry for wrong attachment just see this attachment On Wed, Jun 27, 2012 at 3:30 PM, Anil Gawli gawlianil8...@gmail.comwrote: just press ctrl+shift+A it works now. Regards, Gawli Anil. On Wed, Jun 27, 2012 at 3:20 PM, Deba Ranjan drdeva...@gmail.comwrote: Anil Ji, That is working fine , Thanks.. Ok is there any coding for direct remove without user form. --- i mean writing coding and run. Thanks again. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:15 PM, Anil Gawli gawlianil8...@gmail.comwrote: pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.comwrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ Remove Punctuation
ok ok. thats true, any thanks Anil Ji, Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 4:02 PM, Anil Gawli gawlianil8...@gmail.com wrote: it is possible but how u hold the values and replace it with others, for that u have to save the value as temp and then replace it. Regards, Gawli Anil On Wed, Jun 27, 2012 at 3:43 PM, Deba Ranjan drdeva...@gmail.com wrote: Thanks Anil Ji, its superb working. Anil Ji, Is there any option for removing all the punctuation mark at a time with code and without userform help ? Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:36 PM, Anil Gawli gawlianil8...@gmail.comwrote: sorry for wrong attachment just see this attachment On Wed, Jun 27, 2012 at 3:30 PM, Anil Gawli gawlianil8...@gmail.comwrote: just press ctrl+shift+A it works now. Regards, Gawli Anil. On Wed, Jun 27, 2012 at 3:20 PM, Deba Ranjan drdeva...@gmail.comwrote: Anil Ji, That is working fine , Thanks.. Ok is there any coding for direct remove without user form. --- i mean writing coding and run. Thanks again. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 3:15 PM, Anil Gawli gawlianil8...@gmail.comwrote: pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.comwrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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
$$Excel-Macros$$ Re: Remove Punctuation
Hi Ranjan, Please try this. Sub RemovePunctuation() Dim arrPunc As Variant Dim lngLoop As Long Dim rngCol As Range Dim rngRangeAs Range arrPunc = Array(., !, :, ;, -, #, (, ), [, ], ', , /, ,) Set rngRange = ActiveSheet.Range(A1:L1) For Each rngCol In rngRange If Not LCase(rngCol.Value) Like *date* Then For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next lngLoop Else For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 If arrPunc(lngLoop) / Then rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If Next lngLoop End If Next rngCol End Sub Regards, Lalit Mohan On Wednesday, 27 June 2012 14:38:38 UTC+5:30, Bhaity wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, *Deba Ranjan P* -- -- 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$$ Re: Need a VBA code
Hi Lalit, Thank you for your quick response. I think we are almost there. When I enter a value say my first name and last name, it is displaying last name and first name. But, immediately it shows a run-time error 13, type mismatch. I need this in column F. So if I add an additional statement If Target.Column=6 and then your code will it work? Thanks again. Sharath On Wednesday, June 27, 2012 3:38:12 PM UTC+5:30, Lalit_Mohan wrote: Hi, Try this on sheet selection change event Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value Then Target.Value = BreakName(Target.Value) End If End Sub Function BreakName(ByVal strName As String) BreakName = Split(strName, )(1) Split(strName, )(0) End Function Regards, Lalit Mohan On Wednesday, 27 June 2012 15:21:32 UTC+5:30, sharath chandra wrote: Hello Experts, I need a quick VBA code. We have column where we enter a name in First Name Last Name format. When someone enters a name in that cell, it should automatically display the name in the same cell in the Last Name, First Name format (which is Outlook format). Please let me know if anything is unclear. Thank you, Sharath -- -- 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$$ Re: Need a VBA code
Hi, Try this.. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value and Target.Column=6 Then Target.Value = BreakName(Target.Value) End If End Sub Function BreakName(ByVal strName As String) BreakName = Split(strName, )(1) Split(strName, )(0) End Function Regards, Lalit Mohan On Wednesday, 27 June 2012 16:26:09 UTC+5:30, sharath chandra wrote: Hi Lalit, Thank you for your quick response. I think we are almost there. When I enter a value say my first name and last name, it is displaying last name and first name. But, immediately it shows a run-time error 13, type mismatch. I need this in column F. So if I add an additional statement If Target.Column=6 and then your code will it work? Thanks again. Sharath On Wednesday, June 27, 2012 3:38:12 PM UTC+5:30, Lalit_Mohan wrote: Hi, Try this on sheet selection change event Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Value Then Target.Value = BreakName(Target.Value) End If End Sub Function BreakName(ByVal strName As String) BreakName = Split(strName, )(1) Split(strName, )(0) End Function Regards, Lalit Mohan On Wednesday, 27 June 2012 15:21:32 UTC+5:30, sharath chandra wrote: Hello Experts, I need a quick VBA code. We have column where we enter a name in First Name Last Name format. When someone enters a name in that cell, it should automatically display the name in the same cell in the Last Name, First Name format (which is Outlook format). Please let me know if anything is unclear. Thank you, Sharath -- -- 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$$ Enable Disable Ctrl+D with passward
The suggested method is changing the ctrl-D keystroke for the Excel Application. Not just the single workbook. To do it for a single workbook, but not other open workbooks, you would need to put the commands in Workbook_Activate/deactivate events. As for password protecting it... There are several ways, some more secure than others. However, if you put the enable/disable commands in the Activate/Deactivate events, the user will have to enter the password whenever he/she switches from one workbook to another! although, it MIGHT be possible to store the user entered password in a Public variable and test it during the event macro. As for how to prompt for the password: How critical is this password protection? For instance: if it is just a formality, and you don't care if the password is displayed while typing, you could use an input box, have the user enter the password, then compare the returned value to your stored password. If you want the password to appear as: then that takes a bit more work. also, where is the correct value stored? within the VBA code? on a hidden worksheet? in a file on the network? If you don't want the users to be able to read the password in the VBA code, you'll have to password protect the VBA project also... let me know how you would like this to work... 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: Muralidhar E emuralidha...@gmail.com To: excel-macros@googlegroups.com Sent: Wed, June 27, 2012 7:02:54 AM Subject: Re: $$Excel-Macros$$ Enable Disable Ctrl+D with passward Dear all, At the same opened other excel files also not working the Ctrl+D option. I want to disable this option for the same sheet with a passward for enabling and disbling.It will works for other files as usually. Thanks in advance. On Wed, Jun 27, 2012 at 4:24 PM, Muralidhar E emuralidha...@gmail.com wrote: Hi, Thank u, But i am also need the passward to enbale and disable Ctrl+D option. Thanks in advance. Thanks Regards, Muralidhar E. Think different Do Different Make different On Wed, Jun 27, 2012 at 3:58 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Murali, See attached sheet for reference. On Wed, Jun 27, 2012 at 3:56 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Murali, Please try it.. Private Sub Workbook_Open() Application.OnKey ^d, End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey ^d End Sub -- Thanks regards, Noorain Ansari www.noorainansari.com www.excelmacroworld.blogspot.com On Wed, Jun 27, 2012 at 3:03 PM, Muralidhar E emuralidha...@gmail.com wrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- Thanks Regards, Muralidhar E. -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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
Re: $$Excel-Macros$$ Re: Remove Punctuation
Thanks Lait Ji, Working fine. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 4:20 PM, Lalit_Mohan mohan.pande...@gmail.comwrote: Sub RemovePunctuation() Dim arrPunc As Variant Dim lngLoop As Long Dim rngCol As Range Dim rngRangeAs Range arrPunc = Array(., !, :, ;, -, #, (, ), [, ], ', , /, ,) Set rngRange = ActiveSheet.Range(A1:L1) For Each rngCol In rngRange If Not LCase(rngCol.Value) Like *date* Then For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next lngLoop Else For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 If arrPunc(lngLoop) / Then rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If Next lngLoop End If Next rngCol End Sub -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
RE: $$Excel-Macros$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
No formula needed : Just format the cell as Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Lalit_Mohan Sent: 27 June 2012 12:52 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string? Hi, Try this revised formula as my last post contain some error Evaluate(=Text(Day( Sheets(Sheet1).Cells(count, BarDate).Text ), )) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 11:59:36 UTC+5:30, Lalit_Mohan wrote: Hi, Try this. Evaluate(=Text(Day(Sheets(Sheet1).Cells(count, BarDate).value), )) Regards, Lalit Mohan On Wednesday, 27 June 2012 10:13:06 UTC+5:30, tangledweb wrote: I believe this is the equivalent of your second new solution? Evaluate(=Text(Day(Sheets(RawData).Cells(count, BarDate).Text), )) I tried it with and without the .text part. Both cases returned error 2015 though I have a 4 digit year and the form is month/day/year which is supposed to be what it wants. On Tuesday, June 26, 2012 9:30:29 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this Evaluate(=TEXT(DAY(A1),)) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:45:46 UTC+5:30, tangledweb wrote: This seems to be designed to work directly in excel instead of a VB program. I tried to use it without the = but it said the function TEXT was not defined. I need a method that works in a VB program with the value 6/26/2012 stored in the array previously listed. On Tuesday, June 26, 2012 9:01:08 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this =TEXT(DAY(A1),) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:17:02 UTC+5:30, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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 -- -- 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$$ Remove Punctuation
I think find and replace is best approach.. Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Anil Gawli Sent: 27 June 2012 3:15 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Remove Punctuation pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.com wrote: Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! Thanks Regards, Deba Ranjan P On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 mailto:excel-macros%2bunsubscr...@googlegroups.com -- Thanks regards, Noorain Ansari www.noorainansari.com http://www.noorainansari.com/ www.excelmacroworld.blogspot.com http://www.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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com -- Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd -- -- 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)
RE: $$Excel-Macros$$ Enable Disable Ctrl+D with passward
Do you have any add-in or macro in personal.xlsb which have any macro assigned Ctrl+D?? Because excel default Ctrl+D just fill down the data. Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Muralidhar E Sent: 27 June 2012 3:18 To: excel-macros@googlegroups.com Cc: ahmedhon...@gmail.com Subject: Re: $$Excel-Macros$$ Enable Disable Ctrl+D with passward Hi, But it will not come to the notice at the same time, it was accidently going on, iam not observing it later it was effecting to the data. On Wed, Jun 27, 2012 at 3:09 PM, Ahmed Honest ahmedhon...@gmail.com wrote: Dear, I think using Ctrl + Z will allow you to take back the action you performed i.e Undo the last action ;-). This should help. If not, check other experts advice. Thanks, On Wed, Jun 27, 2012 at 12:33 PM, Muralidhar E emuralidha...@gmail.com wrote: Dear all, Iam using Office 2007, when iam need to use Crl+F accidently iam hitting Ctrl+D, iam loosing some data and iam unable to find the changes happening to the data file. Iam want to disable Ctrl+D option with a passward and the same enable with passward, it should for that worksheet only. Thanks in advance. -- Thanks Regards, Muralidhar E. -- -- 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 mailto:excel-macros%2bunsubscr...@googlegroups.com -- Ahmed Bawazir احمد باوزير -- Thanks Regards, Muralidhar E. -- -- 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 -- -- 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$$ Remove Punctuation
Yes hope show... i have tried the coding provided by Lalit Ji, there i just made a very small change in selection range, its fine working. i have pasted the code in the desire sheet in VBA application and made run , all the almost punctuation got removed. hence easily it was done. Below the code:- Remove Punction Sub RemovePunctuation() Dim arrPunc As Variant Dim lngLoop As Long Dim rngCol As Range Dim rngRangeAs Range arrPunc = Array(., !, :, ;, -, #, (, ), [, ], ', , /, ,) Set rngRange = *Selection.SpecialCells(xlCellTypeConstants, 23)** * For Each rngCol In rngRange If Not LCase(rngCol.Value) Like *date* Then For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next lngLoop Else For lngLoop = LBound(arrPunc) To UBound(arrPunc) - 1 If arrPunc(lngLoop) / Then rngCol.EntireColumn.Replace What:=arrPunc(lngLoop), Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If Next lngLoop End If Next rngCol MsgBox Heelo its done End Sub Thanks Experts. Thanks Regards, *Deba Ranjan P* On Wed, Jun 27, 2012 at 6:35 PM, Rajan_Verma rajanverma1...@gmail.comwrote: I think find and replace is best approach.. ** ** * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Anil Gawli *Sent:* 27 June 2012 3:15 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Remove Punctuation ** ** pls find attached sheet On Wed, Jun 27, 2012 at 2:52 PM, Deba Ranjan drdeva...@gmail.com wrote:* *** Sir, PFAand there are lots of punctuation which need to be clean. For cleaning and trimming the sheets i have used the code below mention and it is fine working:- Sub TrimCleanCells() Dim Rng As Range, myCell As Range Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23) For Each myCell In Rng myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value)) Next msgBox you have cleaned and trimmed everything End Sub But i unable to clean for Punctuation !! ** ** Thanks Regards, *Deba Ranjan P* ** ** On Wed, Jun 27, 2012 at 2:46 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Deba Please share your workbook On Wed, Jun 27, 2012 at 2:38 PM, Deba Ranjan drdeva...@gmail.com wrote: Dear experts, Is there any VBA code to remove punctuation in a sheets ? -- i am doing it manually by ctr + H (replacing the desire punctuation with ) and it takes many times to remove huge data. so is there any coding for removing punctuation at a time ? Thanks Regards, Deba Ranjan P -- -- 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 -- Thanks regards, Noorain Ansari www.noorainansari.com www.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
Re: $$Excel-Macros$$ Need Formula !!
Hi Iqbal, Try this *=IF(E2=Qualify,-,C2-D2)* -- *Best Regards,* *Venkat * *Chennai* On Wed, Jun 27, 2012 at 6:46 PM, Shaikh Iqbal iqbal.hp...@gmail.com wrote: Dear Exports, Please find attached a file and help for formula as per required. Regards, Iqbal Shaikh -- -- 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 * * * * * * -- -- 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 Need Formula Venkat.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Need Formula !!
See the attached File : Formula Used =MAX(0,C2-D2) Use this Format _(* #,##0.00_);_(* (#,##0.00);_(* -??_);_(@_) Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Shaikh Iqbal Sent: 27 June 2012 6:47 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need Formula !! Dear Exports, Please find attached a file and help for formula as per required. Regards, Iqbal Shaikh -- -- 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 -- -- 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 Copy of Need Formula.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Need Formula !!
PFA Deepak Rawat On Wed, Jun 27, 2012 at 6:46 PM, Shaikh Iqbal iqbal.hp...@gmail.com wrote: Dear Exports, Please find attached a file and help for formula as per required. Regards, Iqbal Shaikh -- -- 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 -- -- 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 Need Formula.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Need Formula !!
Dear ALL, Thanks to all, all are working fine. Once again thanks, Iqbal Shaikh On 27 June 2012 19:04, Swapnil Palande palande.swapni...@gmail.com wrote: Hi, use following formula =IF(E2=Not-Qualify, C2-D2, -) Regards, Swapnil On Wed, Jun 27, 2012 at 6:46 PM, Shaikh Iqbal iqbal.hp...@gmail.comwrote: Dear Exports, Please find attached a file and help for formula as per required. Regards, Iqbal Shaikh -- -- 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 -- -- 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 -- -- 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$$ Label record sheet
HI, Can you please explain it more actually the requirement is not clear. Regards, Lalit Mohan On Wed, Jun 27, 2012 at 1:33 PM, attapan_chainarongb...@ck-mail.com wrote: Dear all experts I’ve some questions about functions on the sheet, I made the sheet, So it use for record the label for production I tried and tried to do with many fomular, but the results are not OK L I posted that sheet for all, Please discipline for me Very thanks Rdgs Attapan Chainarongboon Production control Dept (Compressor) Calsonic Kansei (Thailand) E-mail : attapan_chainarongb...@ck-mail.com Phone : (+66) 038-210-129 #101 Mobile phone: (+66) 086-824-5045 -- -- 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 -- -- 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$$ Re: Delete Blank Cells.xlsx
Hi Prince, Sorry for asking but could you please explain the deference in both of your solution. Regards, Lalit Mohan On Wed, Jun 27, 2012 at 8:32 AM, Prince Dubey prince141...@gmail.com wrote: Hi Vishwa, Try This Public Sub DeleteWeekends() Dim rng As Range Dim rngData As Range With ThisWorkbook.Worksheets(Sheet1) Set rngData = .Range(A1:A .Range(A .Rows.Count).End(xlUp).Row) For Each rng In rngData If Application.WorksheetFunction.Text(rng.Value, DDD) = Sat Or Application.WorksheetFunction.Text(rng.Value, DDD) = Sun Then rng.Value = End If Next End With rngData.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True End Sub Regards Prince Dubey. On Wednesday, 27 June 2012 00:46:00 UTC+5:30, Viswanathan Yoganathan wrote: Hi all, In the attached excel, I want to delete the week ends whereas at the same time the position of the month should not move. It means the Feb month should start from A34, March month should start from A64. Please advise using VBA macro. Thanks, Vishwa CONFIDENTIALITY NOTICE: This message and any attachment are confidential and may also be privileged. If you are not the intended recipient of this e-mail you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. If you are not the intended recipient please telephone or e-mail the sender and delete this message and any attachment from your system. On Wednesday, 27 June 2012 00:46:00 UTC+5:30, Viswanathan Yoganathan wrote: Hi all, In the attached excel, I want to delete the week ends whereas at the same time the position of the month should not move. It means the Feb month should start from A34, March month should start from A64. Please advise using VBA macro. Thanks, Vishwa CONFIDENTIALITY NOTICE: This message and any attachment are confidential and may also be privileged. If you are not the intended recipient of this e-mail you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. If you are not the intended recipient please telephone or e-mail the sender and delete this message and any attachment from your system. -- -- 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 -- -- 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$$ Re: Delete Blank Cells.xlsx
Hi, Do we have any vba code, The number of cells we are deleting in between data and the same number of cell to be inserted at the end of data. I believe by doing this the range would not change for the next month. Thanks, Vishwa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Prince Dubey Sent: Wednesday, June 27, 2012 8:31 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Delete Blank Cells.xlsx Hi Vishwa, Please try this i hope this will do your work Public Sub DeleteWeekends() Dim rng As Range Dim rngData As Range With ThisWorkbook.Worksheets(Sheet1) Set rngData = .Range(A1:A .Range(A .Rows.Count).End(xlUp).Row) For Each rng In rngData If Application.WorksheetFunction.Text(rng.Value, DDD) = Sat Or Application.WorksheetFunction.Text(rng.Value, DDD) = Sun Then rng.Value = End If Next End With rngData.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True End Sub Regards Prince Dubey On Wednesday, 27 June 2012 00:46:00 UTC+5:30, Viswanathan Yoganathan wrote: Hi all, In the attached excel, I want to delete the week ends whereas at the same time the position of the month should not move. It means the Feb month should start from A34, March month should start from A64. Please advise using VBA macro. Thanks, Vishwa CONFIDENTIALITY NOTICE: This message and any attachment are confidential and may also be privileged. If you are not the intended recipient of this e-mail you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. If you are not the intended recipient please telephone or e-mail the sender and delete this message and any attachment from your system. -- -- 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.commailto:excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.commailto:excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Delete Blank Cells 1.1.xlsm Description: Delete Blank Cells 1.1.xlsm
$$Excel-Macros$$ Re: Label record sheet
I'll explain all of my request *1.This sheet made for keep history of label printing * *2.the format of label are* A 1207 x line no.Month 5 digits running number *3.I've variety of product code and product code use for 3 Line// I call the name of production line = A,B and T* *4. Quantity concern with running no.* ex. if start new month with product a , production line a , qty = 500 ,YYMM = *1207* label which i recorded are start=A12071 and end =120700500 So, if I start next row with same condition (product no, production line,YYMM and qty = 30) I must be record for start=A120700501 and end=A120700530 and if I start next row with same product no, product line, YYMM = *1208*and qty 20 I must be record start=A12071 end=A120700020 *5. There are three conditions for record as I need = Product no,Production line, and YYMM* * * Do you understand my requirement ? , Please feedback * * finally, Can someone advice this sheet for me ? On Wednesday, June 27, 2012 3:03:21 PM UTC+7, Attapan wrote: Dear all experts I’ve some questions about functions on the sheet, I made the sheet, So it use for record the label for production I tried and tried to do with many fomular, but the results are not OK L I posted that sheet for all, Please discipline for me Very thanks Rdgs * * *Attapan Chainarongboon* *Production control Dept (Compressor)* *Calsonic Kansei (Thailand)*** *E-mail* : attapan_chainarongb...@ck-mail.com *Phone* : (+66) 038-210-129 #101 *Mobile phone*: (+66) 086-824-5045 -- -- 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$$ Introduce Yourself !!
HI, My name is Ankit Agrawal, I'm from New Delhi. Working as Asst A/c manager with a SS Kothari Mehta Co. Apart from my work, I like to travel, music. Excel plays major supportive role in accounting and reporting. I am really happy to be a part of this forum. Thank you Ayush Ji. Regards, Ankit On Sat, Jun 9, 2012 at 12:51 AM, Ayush Jain jainayus...@gmail.com wrote: Hey all new and current posters, Welcome to excel group,one of the largest online community of excel Fans! I hope you enjoy your time here find this forum to be a friendly and knowledgeable community. Please feel free to post a small introduction, a friendly hello or tell us a bit about yourself. Why not tell us where are you from, what you do, what your interests are, how old you are, which is your favourite excel site or blog is or anything else that comes to mind! Thanks for your time Ayush Jain Group Manager Microsoft MVP -- 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 -- -- 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$$ Budget Template
Hi, I need some budget template. Please share some budget template. Regards, Ankit -- -- 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$$ Re: Label record sheet
Missed text (revised) On Wednesday, June 27, 2012 9:39:17 PM UTC+7, Attapan wrote: I'll explain all of my request *1.This sheet made for keep history of label printing * *2.the format of label are* A 1207 x line no.Month 5 digits running number *3.I've variety of product code and product code use for 3 Line// I call the name of production line = A,B and T* *4. Quantity concern with running no.* ex. if start new month with product a , production line a , qty = 500 ,YYMM = *1207* label which i recorded are start=A12071 and end =A120700500 So, if I start next row with same condition (product no, production line,YYMM and qty = 30) I must be record for start=A120700501 and end=A120700530 and if I start next row with same product no, product line, YYMM = *1208*and qty 20 I must be record start=A12071 end=A120700020 *5. There are three conditions for record as I need = Product no,Production line, and YYMM* * * Do you understand my requirement ? , Please feedback * * finally, Can someone advice this sheet for me ? On Wednesday, June 27, 2012 3:03:21 PM UTC+7, Attapan wrote: Dear all experts I’ve some questions about functions on the sheet, I made the sheet, So it use for record the label for production I tried and tried to do with many fomular, but the results are not OK L I posted that sheet for all, Please discipline for me Very thanks Rdgs * * *Attapan Chainarongboon* *Production control Dept (Compressor)* *Calsonic Kansei (Thailand)*** *E-mail* : attapan_chainarongb...@ck-mail.com *Phone* : (+66) 038-210-129 #101 *Mobile phone*: (+66) 086-824-5045 -- -- 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$$ Need Help !!! How to add sheet under another sheet
Hi Friends, I need sheet under another sheet. I have provided explanation in attached sheet. Please help. -- With regards, MaNgEsH -- -- 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 Sheet under another sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Need Help !!! How to add sheet under another sheet
There is no option for what are you looking, You just need to think about hide/unhide the rows Regards Rajan verma +91 7838100659 [IM-Gtalk] -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mangesh Vimay Sent: 27 June 2012 8:34 To: excel-macros Subject: $$Excel-Macros$$ Need Help !!! How to add sheet under another sheet Hi Friends, I need sheet under another sheet. I have provided explanation in attached sheet. Please help. -- With regards, MaNgEsH -- -- 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 -- -- 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$$ Budget Template
Here are some template, hope u can find something good for u J http://office.microsoft.com/en-us/templates/CL102207090.aspx Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Ankit Agrawal Sent: 27 June 2012 8:14 To: excel-macros Subject: $$Excel-Macros$$ Budget Template Hi, I need some budget template. Please share some budget template. Regards, Ankit -- -- 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 -- -- 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$$ Message Box to Show ActiveSheet.PageSetup.Orientation
I need to trap PageSetup settings to determine if the active worksheet can be printed programmatically. (Note that I'm not asking how to change them - if they are not within reason, the active sheet will not be printed.) I thought the following line of code would result in xlPortrait or xlHorizontal, or a numeric value associated with each: MsgBox ActiveSheet.Name Orientation = Application.ActiveSheet.PageSetup.Orientation However, regardless of which way the Orientation is set, the MsgBox always shows 1. I guess this means the orientation is set, but does not indicate which way. How can I pop-up a message box indicating the Orientation? I'm using Excel 2007, Windows 7. Thanks in advance, Mike -- -- 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$$ Message Box to Show ActiveSheet.PageSetup.Orientation
Hope this Help : Sub PopUpMassage(lngNumber As Long) MsgBox Switch(lngNumber = 1, xlPortrait, lngNumber = 2, xlHorizontal) End Sub Sub MTest() PopUpMassage Sheet1.PageSetup.Orientation End Sub Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of stonesfan Sent: 27 June 2012 9:55 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Message Box to Show ActiveSheet.PageSetup.Orientation I need to trap PageSetup settings to determine if the active worksheet can be printed programmatically. (Note that I'm not asking how to change them - if they are not within reason, the active sheet will not be printed.) I thought the following line of code would result in xlPortrait or xlHorizontal, or a numeric value associated with each: MsgBox ActiveSheet.Name Orientation = Application.ActiveSheet.PageSetup.Orientation However, regardless of which way the Orientation is set, the MsgBox always shows 1. I guess this means the orientation is set, but does not indicate which way. How can I pop-up a message box indicating the Orientation? I'm using Excel 2007, Windows 7. Thanks in advance, Mike -- -- 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 -- -- 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$$ Fwd: Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org)
Guys, have ur neck out on below mail ... its a time to face a contest. Keep Winning. -- Forwarded message -- From: Chandoo.org - Learn Excel Charting Online chando...@gmail.com Date: Mon, Jun 25, 2012 at 9:56 PM Subject: Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org) To: yogananda.mutha...@gmail.com ** Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org) http://chandoo.org/wp http://fusion.google.com/add?source=atgsfeedurl=http://feeds.feedburner.com/PointyHairedDilbert [image: Link to Chandoo.org - Learn Excel Charting Online] http://chandoo.org/wp -- Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest]http://feedproxy.google.com/~r/PointyHairedDilbert/~3/PuelD0v6xXU/?utm_source=feedburnerutm_medium=email Posted: 25 Jun 2012 01:25 AM PDT *Its contest time again! Put on your creative hats bring your Excel skills to the game.* Analyze more than 1900 survey responses present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundlehttp://www.amazon.com/gp/product/B003O6EE4U/ref=as_li_ss_tl?ie=UTF8tag=poinhairdilb-20linkCode=as2camp=1789creative=390957creativeASIN=B003O6EE4U(valued at $299). Sounds interesting? Read on. The contest 1. Step 1: Download Excel Salary Survey raw datahttp://img.chandoo.org/contests/salary-data-for-contest.xlsx . 2. Step 2: Analyze it and present your analysis using Excel charts (refer to rules below) 3. Step 3: *Submit your output by end of 6th of July (Friday)* 4. Step 4: If your output is mind-blowingly awesome, you could win XBOX 360 + Kinect Bundle Goals for analysis: While you are free to analyze the data in any way, your output must meet these goals - Help us understand how much Excel professionals make all over the world - Help us understand relationship between salary and role, experience, country hours of Excel work. How to send your entries Once you are ready to send your entry, 1. Check the rules (below) and make sure you have followed all of them. 2. *Email your workbooks to chandoo.d @ gmail.com* with subject “*Salary Survey Contest*“ 3. Please include your name best email address(es) so that we can reach you if there is any clarification needed. 4. *Please send in your entries before end of 6th July (Friday).* The Rules - You can make a dashboard or 1 page report - You can make several individual charts (or tables) too as long as they all fit in to one window. - You can make interactive or static charts - You can also use VBA to add interaction to the charts. - Even though you can use add-ins to make your report, user should be able to view your output without installing any add-ins - You can use Excel 2010, 2007 or 2003. Please note that this contest requires use of Excel. - You can submit more than one entry. - Your files must be unlocked any macros you use must have enough comments so that others can learn. - You can add data to this dataset (for example, PPP adjusted salaries, established salary benchmarks from public sources like govt. databases or websites) The Prizes The winner of this contest will receive 1 XBOX 360 (4GB) + Kinect Sports bundlehttp://www.amazon.com/gp/product/B003O6EE4U/ref=as_li_ss_tl?ie=UTF8tag=poinhairdilb-20linkCode=as2camp=1789creative=390957creativeASIN=B003O6EE4U(valued $299). We have 2 consolation prizes too. For the 2nd best entry, you get a Amazon Kindle Firehttp://www.amazon.com/gp/product/B0051VVOB2/ref=famstripe_kftag=poinhairdilb-20 and for 3rd best entry, you get a Kindle Touchhttp://www.amazon.com/gp/product/B005890G8Y/ref=famstripe_kttag=poinhairdilb-20($99 option). So there you go. 3 gorgeous gadgets up for grabs. [image: Excel Salary Survey - Prizes] Note: Since prizes are delivered thru Amazon.com, in case they cannot ship the item to your location, you will be offered $ amount. How do we pick the winners? Once all the entries are received, then we will open voting. 1. All entries will be published on Chandoo.org and you can vote for winner. 2. A panel of judges (names to be revealed later) will also vote. Judges votes carry higher weight. 3. The entries with highest votes will be winners. PS: If we get a lot of entries, we may eliminate a few during voting stage. So what are you waiting for? Go ahead and download the datahttp://img.chandoo.org/contests/salary-data-for-contest.xlsx. Start exploring and share your results with us. Need some inspiration? Check out past contest result pages to get some inspiration. - Sales Dashboard Contest – 32 entrieshttp://chandoo.org/wp/2010/01/04/sales-dashboards/ - Personal Expense Trackers – 7 entrieshttp://chandoo.org/wp/2010/07/16/download-expense-trackers/ - Sales Analysis Chart – 78
Re: $$Excel-Macros$$ Fwd: Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org)
Nice I will join the contest Sent from my BlackBerry® smartphone from Airtel Ghana -Original Message- From: yogananda muthaiah yogananda.mutha...@gmail.com Sender: excel-macros@googlegroups.com Date: Wed, 27 Jun 2012 22:17:47 To: excel-macrosexcel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org) Guys, have ur neck out on below mail ... its a time to face a contest. Keep Winning. -- Forwarded message -- From: Chandoo.org - Learn Excel Charting Online chando...@gmail.com Date: Mon, Jun 25, 2012 at 9:56 PM Subject: Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org) To: yogananda.mutha...@gmail.com ** Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest] (chandoo.org) http://chandoo.org/wp http://fusion.google.com/add?source=atgsfeedurl=http://feeds.feedburner.com/PointyHairedDilbert [image: Link to Chandoo.org - Learn Excel Charting Online] http://chandoo.org/wp -- Visualize Excel Salary Data You could win XBOX 360 + Kinect Bundle [Contest]http://feedproxy.google.com/~r/PointyHairedDilbert/~3/PuelD0v6xXU/?utm_source=feedburnerutm_medium=email Posted: 25 Jun 2012 01:25 AM PDT *Its contest time again! Put on your creative hats bring your Excel skills to the game.* Analyze more than 1900 survey responses present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundlehttp://www.amazon.com/gp/product/B003O6EE4U/ref=as_li_ss_tl?ie=UTF8tag=poinhairdilb-20linkCode=as2camp=1789creative=390957creativeASIN=B003O6EE4U(valued at $299). Sounds interesting? Read on. The contest 1. Step 1: Download Excel Salary Survey raw datahttp://img.chandoo.org/contests/salary-data-for-contest.xlsx . 2. Step 2: Analyze it and present your analysis using Excel charts (refer to rules below) 3. Step 3: *Submit your output by end of 6th of July (Friday)* 4. Step 4: If your output is mind-blowingly awesome, you could win XBOX 360 + Kinect Bundle Goals for analysis: While you are free to analyze the data in any way, your output must meet these goals - Help us understand how much Excel professionals make all over the world - Help us understand relationship between salary and role, experience, country hours of Excel work. How to send your entries Once you are ready to send your entry, 1. Check the rules (below) and make sure you have followed all of them. 2. *Email your workbooks to chandoo.d @ gmail.com* with subject “*Salary Survey Contest*“ 3. Please include your name best email address(es) so that we can reach you if there is any clarification needed. 4. *Please send in your entries before end of 6th July (Friday).* The Rules - You can make a dashboard or 1 page report - You can make several individual charts (or tables) too as long as they all fit in to one window. - You can make interactive or static charts - You can also use VBA to add interaction to the charts. - Even though you can use add-ins to make your report, user should be able to view your output without installing any add-ins - You can use Excel 2010, 2007 or 2003. Please note that this contest requires use of Excel. - You can submit more than one entry. - Your files must be unlocked any macros you use must have enough comments so that others can learn. - You can add data to this dataset (for example, PPP adjusted salaries, established salary benchmarks from public sources like govt. databases or websites) The Prizes The winner of this contest will receive 1 XBOX 360 (4GB) + Kinect Sports bundlehttp://www.amazon.com/gp/product/B003O6EE4U/ref=as_li_ss_tl?ie=UTF8tag=poinhairdilb-20linkCode=as2camp=1789creative=390957creativeASIN=B003O6EE4U(valued $299). We have 2 consolation prizes too. For the 2nd best entry, you get a Amazon Kindle Firehttp://www.amazon.com/gp/product/B0051VVOB2/ref=famstripe_kftag=poinhairdilb-20 and for 3rd best entry, you get a Kindle Touchhttp://www.amazon.com/gp/product/B005890G8Y/ref=famstripe_kttag=poinhairdilb-20($99 option). So there you go. 3 gorgeous gadgets up for grabs. [image: Excel Salary Survey - Prizes] Note: Since prizes are delivered thru Amazon.com, in case they cannot ship the item to your location, you will be offered $ amount. How do we pick the winners? Once all the entries are received, then we will open voting. 1. All entries will be published on Chandoo.org and you can vote for winner. 2. A panel of judges (names to be revealed later) will also vote. Judges votes carry higher weight. 3. The entries with highest votes will be winners. PS: If we get a lot of entries, we may eliminate a few during voting stage. So what are you waiting for? Go ahead and download the
$$Excel-Macros$$ Re: Regarding control tab in the scroll bar
hi dear, You should use form control's scroll bar. see in attachment. regards prince On Tuesday, 26 June 2012 14:09:19 UTC+5:30, friend wrote: Dear Sir/ Madam, Please see the attachment, I can not find the control tab in the format control of the scroll bar. Please help Thanking you Regard Sanjib -- - -- -- 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 Help !!! How to add sheet under another sheet
Hi Mangesh, Try using from Insert Menu -- Object -- choose Create from file and locate your sheet path that is available in your respective file then an Icon is displayed so being in a sheet when you click on that Icon you will get another sheet. Revert if you failed to understand the above. I think this option should meet your requirement 100%. Hi Rajan, Try to read of what I have mentioned above and review accordingly and comment of what you think about this ?? :-) Thanks, Ahmed Bawazir On Wed, Jun 27, 2012 at 6:03 PM, Mangesh Vimay mangesh.da...@gmail.comwrote: Hi Friends, I need sheet under another sheet. I have provided explanation in attached sheet. Please help. -- With regards, MaNgEsH -- -- 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 -- Ahmed Bawazir *احمد باوزير* -- -- 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$$ need of free e-book
HI to All, Could you please provide me mentioned pdf free ebook . BUILDING ACCOUNTING SYSTEM USING ACCESS 2007 -- 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$$ Need some VBA Question for interview.
Hi All, Can someone provide me some VBA Question for interview. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- 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$$ Re: Need some VBA Question for interview.
Hi Indrajit, Please see attached file. On Thursday, June 28, 2012 7:08:06 AM UTC+8, Bullet wrote: Hi All, Can someone provide me some VBA Question for interview. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- 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 50+Excel+VBA+Oral+Interview+Questions.docx Description: application/vnd.openxmlformats-officedocument.wordprocessingml.document
Re: $$Excel-Macros$$ Need some VBA Question for interview.
Dear Indrajit, Please find attached VBA questions and Answers. -- Thanks regards, Noorain Ansari www.noorainansari.com www.excelmacroworld.blogspot.com On Thu, Jun 28, 2012 at 4:38 AM, Indrajit $nai talk2indra...@gmail.comwrote: Hi All, Can someone provide me some VBA Question for interview. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- 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 -- 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 50 Excel VBA Oral Interview Questions.docx Description: application/vnd.openxmlformats-officedocument.wordprocessingml.document
$$Excel-Macros$$ Re: Need some VBA Question for interview.
Hi Indrajit, Please online stuff for this it would be very helpful for you. Regards Prince dubey. On Thursday, 28 June 2012 04:38:06 UTC+5:30, Bullet wrote: Hi All, Can someone provide me some VBA Question for interview. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- 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$$ Re: Message Box to Show ActiveSheet.PageSetup.Orientation
Hi stonesfan, Try This I hope this will help u. Sub PageOrientation() MsgBox Switch(Sheet1.PageSetup.Orientation = 1, xlPortrait, Sheet1.PageSetup.Orientation = 2, xlHorizontal) End Sub regards prince dubey On Wednesday, 27 June 2012 21:54:42 UTC+5:30, stonesfan wrote: I need to trap PageSetup settings to determine if the active worksheet can be printed programmatically. (Note that I'm not asking how to change them - if they are not within reason, the active sheet will not be printed.) I thought the following line of code would result in xlPortrait or xlHorizontal, or a numeric value associated with each: MsgBox ActiveSheet.Name Orientation = Application.ActiveSheet.PageSetup.Orientation However, regardless of which way the Orientation is set, the MsgBox always shows 1. I guess this means the orientation is set, but does not indicate which way. How can I pop-up a message box indicating the Orientation? I'm using Excel 2007, Windows 7. Thanks in advance, Mike -- 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$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
The custom format will not work as I need the data to have the date except for one special test. This worked so thanks Lalit. Evaluate(=Text(Day( Sheets(RawData).Cells(count, BarDate).Text ), )) Oddly when I used Sheet1 instead of RawData which is the same sheet I got a subscript out of range but worked fine with the alias. On Tuesday, June 26, 2012 8:47:02 PM UTC-7, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- 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$$ Re: Delete Blank Cells.xlsx
Hi Vishwa, Hope it works for you Sub BlanksAtEnd() Dim rngRangeAs Range Dim rngStartAs Range Dim rngEnd As Range Dim varMonth() As Variant Dim lngLoop As Long Const strDelima As String = @ Set rngRange = ActiveSheet.UsedRange varMonth = Array(January, February, March, April, May) For lngLoop = LBound(varMonth) To UBound(varMonth) varMonth(lngLoop) = varMonth(lngLoop) strDelima rngRange.Find(varMonth(lngLoop), LookIn:=xlValues).Address Next lngLoop With ActiveSheet For lngLoop = LBound(varMonth) To UBound(varMonth) Set rngStart = .Range(Split(varMonth(lngLoop), strDelima)(1)).Offset(1) If lngLoop UBound(varMonth) Then Set rngEnd = .Range(Split(varMonth(lngLoop + 1), strDelima)(1)).End(xlUp) ElseIf lngLoop = UBound(varMonth) Then Set rngEnd = .Cells(1, 1).End(xlUp) End If Set rngRange = .Range(rngStart, rngEnd) rngRange.RemoveDuplicates Columns:=1, Header:=xlNo On Error Resume Next Set rngRange = .Range(.Range(Split(rngRange.SpecialCells(xlCellTypeBlanks).Address, ,)(0)).Offset(1), rngEnd) rngRange.Offset(-1).Value = rngRange.Value On Error GoTo 0: On Error GoTo -1: Err.Clear Next lngLoop End With End Sub Regards, Lalit Mohan On Wednesday, 27 June 2012 00:46:00 UTC+5:30, Viswanathan Yoganathan wrote: Hi all, In the attached excel, I want to delete the week ends whereas at the same time the position of the month should not move. It means the Feb month should start from A34, March month should start from A64. Please advise using VBA macro. Thanks, Vishwa CONFIDENTIALITY NOTICE: This message and any attachment are confidential and may also be privileged. If you are not the intended recipient of this e-mail you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. If you are not the intended recipient please telephone or e-mail the sender and delete this message and any attachment from your system. -- 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$$ Re: Message Box to Show ActiveSheet.PageSetup.Orientation
Hi, Try this also Sub PgOrentation() Select Case Sheet1.PageSetup.Orientation Case 1: MsgBox xlPortrait Case 2: MsgBox xlHorizontal Case Else: MsgBox None End Select End Sub Regards, Lalit Mohan UTC+5:30, stonesfan wrote: I need to trap PageSetup settings to determine if the active worksheet can be printed programmatically. (Note that I'm not asking how to change them - if they are not within reason, the active sheet will not be printed.) I thought the following line of code would result in xlPortrait or xlHorizontal, or a numeric value associated with each: MsgBox ActiveSheet.Name Orientation = Application.ActiveSheet.PageSetup.Orientation However, regardless of which way the Orientation is set, the MsgBox always shows 1. I guess this means the orientation is set, but does not indicate which way. How can I pop-up a message box indicating the Orientation? I'm using Excel 2007, Windows 7. Thanks in advance, Mike -- 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$$ Re: Need Help !!! How to add sheet under another sheet
Hi, This kind of functionality is available in excel i don't know but we can achieve this not exactle but will look like this by using paste data as picture link. I don't know how much it will helful to you. Regards, Lalit Mohan On Wednesday, 27 June 2012 20:33:43 UTC+5:30, Mangesh wrote: Hi Friends, I need sheet under another sheet. I have provided explanation in attached sheet. Please help. -- With regards, MaNgEsH -- 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$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
use Text(a1,d) Mothilal On Wed, Jun 27, 2012 at 12:52 PM, Lalit_Mohan mohan.pande...@gmail.comwrote: Hi, Try this revised formula as my last post contain some error Evaluate(=Text(Day( Sheets(Sheet1).Cells(count, BarDate).Text ), )) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 11:59:36 UTC+5:30, Lalit_Mohan wrote: Hi, Try this. Evaluate(=Text(Day(Sheets(**Sheet1).Cells(count, BarDate).value), )) Regards, Lalit Mohan On Wednesday, 27 June 2012 10:13:06 UTC+5:30, tangledweb wrote: I believe this is the equivalent of your second new solution? Evaluate(=Text(Day(Sheets(**RawData).Cells(count, BarDate).Text), )) I tried it with and without the .text part. Both cases returned error 2015 though I have a 4 digit year and the form is month/day/year which is supposed to be what it wants. On Tuesday, June 26, 2012 9:30:29 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this Evaluate(=TEXT(DAY(A1),**)) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:45:46 UTC+5:30, tangledweb wrote: This seems to be designed to work directly in excel instead of a VB program. I tried to use it without the = but it said the function TEXT was not defined. I need a method that works in a VB program with the value 6/26/2012 stored in the array previously listed. On Tuesday, June 26, 2012 9:01:08 PM UTC-7, Lalit_Mohan wrote: Hi, You can try this =TEXT(DAY(A1),) Hope it helps you Regards, Lalit Mohan On Wednesday, 27 June 2012 09:17:02 UTC+5:30, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- -- 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 -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string?
Asa! Good to hear from you. I am fine. Hope the same for you. The example I posted seemed to work but I admit I did not check to see if the correct day was returned. Tomorrow I will do that and try out your format function version (which I think someone else posted earlier but I had not tried it yet). On Wed, Jun 27, 2012 at 9:35 PM, Asa Rossoff a...@lovetour.info wrote: Hi! Hope you are well. The VBA way is to use VBA's Format function (Replace CELL with the range object for the cell desired): Format(CELL.Value2, ) If you wanted to use the TEXT function, the example given -- I believe -- will return incorrect results. These versions would return the correct result (replace WORKSHEET/CELL): Worksheetfunction.Text(CELL, ) WORKSHEET.evaluate(text( CELL.address ,)) WORKSHEET.evaluate(text( CELL.value2 ,)) WORKSHEET.evaluate(text( CELL.value ,)) The Format function would be my preference, but of the TEXT function versions, the first is most efficient, then second, then third. The first uses a cell reference to supply the date value, the second a dateserial (Value2 returns a number for dates), the third a text representation of the date (the Value property converts date/time values on the worksheet to VBA's Date datatype, but when you concatenate it with a string, VBA's datatype coercion behavior is to convert it to a text representation using the system locale's short date and/or short time settings). The previous example acquired the cell value via it's Text property, which is dangerous and rarely desired. Text returns the value of the cell as formatted, but if the column is hidden, it returns an empty string. Also, the Day function will cause trouble. It's a common misunderstanding to think it should be used with the Text function, but this is almost never desired. Day returns a number between 1 and 7 to indicate the day of the week. Text formats dates or dateserial numbers, not day numbers. Text will interpret numbers between 1 and 7 as the first 7 days on it's dateserial calendar, which are 1/1/1900 through 1/7/1900. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Wednesday, June 27, 2012 8:23 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Can I get the day of the week (e.g. Friday) from the date already encoded in a string? The custom format will not work as I need the data to have the date except for one special test. This worked so thanks Lalit. Evaluate(=Text(Day( Sheets(RawData).Cells(count, BarDate).Text ), )) Oddly when I used Sheet1 instead of RawData which is the same sheet I got a subscript out of range but worked fine with the alias. On Tuesday, June 26, 2012 8:47:02 PM UTC-7, tangledweb wrote: If I already have Sheets(RawData).Cells(count, BarDate).Text having a value like 6/26/2012 is there a way to get the day of the week as a number or string for that date that does not require splitting it into its parts and feeding them to some function? If the latter the .net examples I found for that did not make sense to me so how is it done? -- 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 -- 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
