$$Excel-Macros$$ Download data of Shares from NSE
Hi All I want to make a dashboard in excel by downloading data from NSE website. currently i do manually, I want to do it by macro. Is there any way to do the same by running the macro. Regards, Deepak -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Just started with version 2010 suggestions needed
Hi, I've just started using MS Excel 2010. Although I've been using Excel for my work for years now and also plan to purchase John Walkenbach's book on programming with 2010 I would like to have opinions from all of my peers on what other things should I read to use Excel more optimally In case anyone happens to have any ebooks / PDF material or tutorials on use of Excel 2010 could you please send them to my mail ID ? My mail ID is anandydr at gmail dot com. Eagerly awaiting your response... Anand Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Simple VBA code for filter data in ascending
Hi All I need the simplest VBA code to filter any data in ascending. Regards, Deepak -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Recovering corrupted excel file
Hi, Is there any way to recover corrupted excel file. Thanks, Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to remove space in excel cell
Hi all, Its a v good information and i also did not know this. but i want to share another way to perform the same by find an replace. copy both the spaces and put in find textbox and replace with single space Regards, Deepak On Wed, May 18, 2011 at 8:20 PM, STDEV(i) setiyowati.d...@gmail.com wrote: check whether the character is space / CHAR(32) =CODE(A1) if this formula returns 32 then use TRIM if this formual returns other numbers that 32 (space) use SUBSTITUTE On Wed, May 18, 2011 at 2:43 PM, anandydr anand...@gmail.com wrote: At times the space is leading space which is not completely removed using TRIM ( ) could anyone suggest what to do in such case? On May 18, 3:38 am, STDEV(i) setiyowati.d...@gmail.com wrote: sometimes what we thought as the space (as prefix or suffix) turns out to be *Char (160)*so we need formula like this =SUBSTITUTE(A1,CHAR(160),) On Sun, May 15, 2011 at 12:48 PM, Prabhu prabhugate...@gmail.com wrote: Hi friends, How to remove spaces(bugs) in a particular cell either will be as prefix or suffix before or after number or text. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct
There is no array used.. Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Chandra Shekar Sent: Thursday, May 19, 2011 7:25 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct Hi, Thanks for the reply. Can we do this without using array function. Regards, Chandra Shekar B On Thu, May 19, 2011 at 11:16 AM, Anil Bhange anil.bha...@tatacommunications.commailto:anil.bha...@tatacommunications.com wrote: HI Chandra, See if it helps... Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.commailto:excel-macros@googlegroups.com] On Behalf Of Chandra Shekar Sent: Wednesday, May 18, 2011 7:51 PM To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct Hi Asish, Please reply to the mail. :) I have attached both the files for your information. Regards, Chandra Shekar B On Tue, May 17, 2011 at 7:34 PM, Chandra Shekar chandrashekarb@gmail.commailto:chandrashekarb@gmail.com wrote: Hi Ashish, Please find attached file. Thanks, Chanra Shekar B On Tue, May 17, 2011 at 6:09 PM, ashish koul koul.ash...@gmail.commailto:koul.ash...@gmail.com wrote: i have made changes in the data may be because of that can you attach the sample file back On Tue, May 17, 2011 at 12:06 PM, Chandra Shekar chandrashekarb@gmail.commailto:chandrashekarb@gmail.com wrote: Hi Asish, Thanks for your reply, but I did'nt get the desired result i.e. in MAX, MIN AVG for first record should be 100, 50 avergage should be 87.5. Can we use sumproduct for all the formulas instead of array formulas. Thanks in advanc. Regards, Chandra Shekar B On Mon, May 16, 2011 at 9:15 PM, ashish koul koul.ash...@gmail.commailto:koul.ash...@gmail.com wrote: try this On Mon, May 16, 2011 at 12:12 PM, Chandra Shekar chandrashekarb@gmail.commailto:chandrashekarb@gmail.com wrote: Hi, I want Min Max and Average by using Sumproduct. My problem is I want a formula which should be based on Date and Title as shown on Column D, E F and based on Column C I need Average, Min Max Values. If I confusing pls let me know. Thanks Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.comhttp://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.comhttp://akoul.blogspot.com/ akoul.wordpress.comhttp://akoul.wordpress.com/ My Linkedin Profilehttp://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.comhttp://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.comhttp://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul
Re: $$Excel-Macros$$ Recovering corrupted excel file
Use a Function Like this ='C:\Users\Xl_Lover\Desktop\[Book2[Sheet1]]Index'!A1 On Fri, May 20, 2011 at 4:00 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hi, Is there any way to recover corrupted excel file. Thanks, Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct
Hi, You are using array formula i.e.=MIN(IF($A$2:$A$12=E2;IF($A$2:$A$12=F2;IF($B$2:$B$12=D2;$C$2:$C$12; =AVERAGE(IF($A$2:$A$12=E2;IF($A$2:$A$12=F2;IF($B$2:$B$12=D2;$C$2:$C$12;. Is there any way to do this without using array formula. Thanks, Chandra Shekar B On Fri, May 20, 2011 at 2:24 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: There is no array used.. Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Chandra Shekar *Sent:* Thursday, May 19, 2011 7:25 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct Hi, Thanks for the reply. Can we do this without using array function. Regards, Chandra Shekar B On Thu, May 19, 2011 at 11:16 AM, Anil Bhange anil.bha...@tatacommunications.com wrote: HI Chandra, See if it helps… Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Chandra Shekar *Sent:* Wednesday, May 18, 2011 7:51 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ MinMaxAverage : Using Sumproduct Hi Asish, Please reply to the mail. :) I have attached both the files for your information. Regards, Chandra Shekar B On Tue, May 17, 2011 at 7:34 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hi Ashish, Please find attached file. Thanks, Chanra Shekar B On Tue, May 17, 2011 at 6:09 PM, ashish koul koul.ash...@gmail.com wrote: i have made changes in the data may be because of that can you attach the sample file back On Tue, May 17, 2011 at 12:06 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hi Asish, Thanks for your reply, but I did'nt get the desired result i.e. in MAX, MIN AVG for first record should be 100, 50 avergage should be 87.5. Can we use sumproduct for all the formulas instead of array formulas. Thanks in advanc. Regards, Chandra Shekar B On Mon, May 16, 2011 at 9:15 PM, ashish koul koul.ash...@gmail.com wrote: try this On Mon, May 16, 2011 at 12:12 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hi, I want Min Max and Average by using Sumproduct. My problem is I want a formula which should be based on Date and Title as shown on Column D, E F and based on Column C I need Average, Min Max Values. If I confusing pls let me know. Thanks Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before
Re: $$Excel-Macros$$ Recovering corrupted excel file
Hi can u expalin briefly in steps. Thanks, Chandra Shekar B On Fri, May 20, 2011 at 5:02 PM, rajan verma rajanverma1...@gmail.comwrote: Use a Function Like this ='C:\Users\Xl_Lover\Desktop\[Book2[Sheet1]]Index'!A1 On Fri, May 20, 2011 at 4:00 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hi, Is there any way to recover corrupted excel file. Thanks, Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help -Extract data from one file to onother file
Hi Seti, Thanks for responce but i know that formula but i need here vba code for same. On 5/20/11, STDEV(i) setiyowati.d...@gmail.com wrote: (1) Array formula: =SMALL(IF(('[Row data.xlsx]Sheet1'!$D$3:$D$105=C2)* ('[Row data.xlsx]Sheet1'!$D$3:$D$105=C3),ROW(1:103)),ROW(1:103)) (2) Formula =IF(ISERR($A6),,OFFSET('[Row data.xlsx]Sheet1'!B$2,$A6,0)) check the attachments and see if it helps.. -siti- On Fri, May 20, 2011 at 5:39 PM, Umed Singh umed.singh.par...@gmail.comwrote: Hi Gurus, please help me to write vba code in attached report file. Actully i want to extract data from one file to onother file. as selecting start date and end date. Regards, Umed Singh -- Umed Singh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Dynamic formula
There are two data files say temp1_mon.xls and temp2_monyyy.xls.these two data files change everymonth i.e temp1.xls is always the current data month and temp2.xls is previous data month. here monyyy is displayed as APR2011 for current month and MAR2011 for previous month. There is a tab called MEMO in temp1_mon.cls where the cell reference(4,6) is calculated as: (sum of column 10 from minus sum of column 9 from current month file ) minus sum of column 10 from previous month file. So the file which is current month will become prior month for the next month run. Help me to make the formula dynamic. VB code can do this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
Pls try it =CONCATENATE(A1,:,B1) On Fri, May 20, 2011 at 5:08 PM, Kabir:-) saroj.32...@gmail.com wrote: Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO
Hi Kabir, See if it help. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Kabir:-) Sent: Friday, May 20, 2011 5:09 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need a formula for getting the output of one calculation as RATIO Dear Gurus, I need a formula for calculated two numbers and resulted output should be in a Ratio.i.e., 2:1 or 1.5:3. Thank you in advance, Regards, Saroj. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Ratio.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Dynamic formula
can you attach the sample file On Fri, May 20, 2011 at 10:07 PM, Skanda skanda.pokkun...@gmail.com wrote: There are two data files say temp1_mon.xls and temp2_monyyy.xls.these two data files change everymonth i.e temp1.xls is always the current data month and temp2.xls is previous data month. here monyyy is displayed as APR2011 for current month and MAR2011 for previous month. There is a tab called MEMO in temp1_mon.cls where the cell reference(4,6) is calculated as: (sum of column 10 from minus sum of column 9 from current month file ) minus sum of column 10 from previous month file. So the file which is current month will become prior month for the next month run. Help me to make the formula dynamic. VB code can do this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: Macro for Converting txt to xls using Text Import Wizard
Sub delimit_txt_file() Dim s As Workbook Dim fNameAndPath As Variant fNameAndPath = Application.GetOpenFilename _ (FileFilter:=Text Files (*.txt), *.txt, _ Title:=Select File To Be Opened) Workbooks.OpenText Filename:=fNameAndPath, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:=| Set s = ActiveWorkbook s.Sheets(1).Cells.Select s.Sheets(1).Cells.EntireColumn.AutoFit Dim i As Long For i = 2 To s.Sheets(1).Range(a1).End(xlDown).Row s.Sheets(1).Cells(i, 3).Value = Application.Trim(s.Sheets(1).Cells(i, 3).Value | s.Sheets(1).Cells(i, 4).Value) If Right(s.Sheets(1).Cells(i, 3).Value, 1) = | Then s.Sheets(1).Cells(i, 3).Value = Left(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If If Left(s.Sheets(1).Cells(i, 3).Value, 1) = | Then s.Sheets(1).Cells(i, 3).Value = Right(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If Next i s.Sheets(1).Columns(D:D).Delete Shift:=xlToLeft End Sub On Thu, May 19, 2011 at 8:59 PM, Susan 1 sunni...@gmail.com wrote: Hi Jsin - I've attached an example of the formatted spreadsheet and the raw data txt file, the macro or VB I need would automatically format the txt file into the spreadsheet style. Thanks for you help on this! On Thu, May 19, 2011 at 4:54 AM, JsinSk jsin...@gmail.com wrote: I can guess how you want it formatted, but is it possible for you to attach a sample excel sheet with the desired tabs/columns how you want it? On May 18, 3:31 pm, Susan 1 sunni...@gmail.com wrote: Hi ~ Wondering if you could assist me with the Text import wizard to configure the columns and tab delimiter for the attached txt file. And then send me the steps. I will be needing to do this every week to the same types of txt files, importing into Excel and I’d like to create a Macro for this but cannot figure out how to set the tab/columns. Thanks, Sunnie 2065409868_05-03-2011.txt 6KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Freeze/Unalterable Cells
its doing the same thing i guess can you check it On Wed, May 18, 2011 at 4:06 AM, Vinod Narayanan nvino...@gmail.com wrote: Hi Ashish, Thanks for your help... As i have explained in my previous mail, its a questionnaire and the cell should freeze the moment the answers is chosen / writtten The macro freezes the moment an answer for a single question is answered... but what i require is when a person answers a question(b3) the answer in C3 should only freeze... then when he answers a question (B4), the answer in C4 should only freeze.. so on.. Please help me On Tue, May 17, 2011 at 10:00 PM, ashish koul koul.ash...@gmail.comwrote: http://akoul.blogspot.com/search?q=protect Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then 'target.column =3 because column c is no 3 column 'you can change password ashish and choose your own ActiveSheet.Unprotect Password:=ashish If Not IsEmpty(Target.Value) Then Target.Locked = True End If ActiveSheet.Protect Password:=ashish Else: End If End Sub On Mon, May 16, 2011 at 1:04 AM, Vinod Narayanan nvino...@gmail.comwrote: Hi Team, Request you to help me creating an assessment sheet wherein the person taking the test, once chooses the option given in column C, the particular cell should freeze... If the question is given B3 cell and the corresponding answers in C3 (either drop down or need to key in ... depending upon the question) Sample sheet is attached... the movement the option is chosen by the person, the cell c3 should freeze and make it unalterable -- Thanks and Regards Vinod Narayanan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks and Regards Vinod Narayanan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Delete columns A thru D if blank
can you atatch the sample worksheet On Wed, May 18, 2011 at 2:31 AM, Bob bobandrich...@comcast.net wrote: I'm having a problem with, when the code runs, once I type something in cell A3 the formula is automatically copied down. Thats fine but it copies down each time I selcet a cell. I do not want any blanks. I can not just delete blank rows because there is something in columns F thru M. One blank row is fine. I just don't want it to keep inserting a formula each time a select change is made. Maybe.. something like A3:D if blank delete that section instead of entire row. Any help please. Here is what I have... Sub auto_open() ' Auto_Open Macro ' Macro recorded 05/17/11 by R.L.S. ThisWorkbook.Sheets(Worksheet2).OnEntry = ValidateCells End Sub Sub ValidateCells() Worksheets(Worksheet2).Range(D3).Formula = =if(C3=,,C3*B3) Worksheets(Worksheet2).Range(D3, Range(D65536).End(xlUp).Offset(1, 1)).FillDown Worksheets(Worksheet2).Range(E3).Formula = =if(B3=,, (B3*144*C3)) Worksheets(Worksheet2).Range(E3, Range(D65536).End(xlUp).Offset(1, 1)).FillDown End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel