RE: $$Excel-Macros$$ help regarding date formula
Use this in D4 =DATE(YEAR(C4),MONTH(C4)+1,5) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of L.K. Modi Sent: Thursday, July 07, 2011 10:26 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ help regarding date formula Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- 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 regarding date formula
* hi L K modi use this formula in d4 =EOMONTH(C4,0)+5 *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Thu, Jul 7, 2011 at 11:29 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Use this in D4* *=DATE(YEAR(C4),MONTH(C4)+1,5)* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *L.K. Modi *Sent:* Thursday, July 07, 2011 10:26 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ help regarding date formula ** ** Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need formula for Current Date Access
* my pleasure *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Wed, Jul 6, 2011 at 7:04 PM, Idhrees Mohamed idhrees...@gmail.comwrote: Dear Ankur, Its working fine. Thank you very much. Regards, Idhrees. On Tue, Jul 5, 2011 at 3:46 PM, ankur ankurpande...@gmail.com wrote: *You can create a form, by using the IIF function in Control Source property...Of the field which required =IIf([Valid date]=Date(),valid,expired) one sample file is attached... *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Tue, Jul 5, 2011 at 12:35 PM, Idhrees Mohamed idhrees...@gmail.comwrote: Dear Friends, I need a help to access query for current date. I have an column with name valid date. I need a formula for validity date less than current date, its shown valid otherwise expired like that. Please help me the ms access fuction for this. -- Thanks Regards, Mohamed Idhrees. S. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, Mohamed Idhrees. S. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: Query
* *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Wed, Jul 6, 2011 at 6:05 PM, ankur ankurpande...@gmail.com wrote: *hi ashish sir may this help u ... *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Wed, Jul 6, 2011 at 5:30 PM, ashish koul koul.ash...@gmail.com wrote: On Wed, Jul 6, 2011 at 4:56 PM, Kal xcel kalx...@gmail.com wrote: Dear Experts, For this chart 3001-5000 = 30 5001-6000 = 40 6001-7000 = 45 7001-8000 = 50 8001-9000 = 50 9001-15000 = 110 15001-25000 = 130 25001-4 = 150 40001= =200 I use this formula: =IF(AND(D16=3001,D16=5000),30,IF(AND(D16=5001,D16=6000),40,IF(AND(D16=6001,D16=7000),45,IF(AND(D16=7001,D16=8000),50,IF(AND(D16=8001,D16=9000),90,IF(AND(D16=9001,D16=15000),110,IF(AND(D16=15001,D16=25000),130,IF(AND(D16=25001,D16=4),150,IF(AND(D16=40001),200,0) Is their any other short/intelligent way to get desired result? Please help Thanks in advance Kalyan -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ http://akoul.posterous.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
Re: $$Excel-Macros$$ Maill Merge in Two Excel Sheet
*HI VASANT thanks a lot code is working fine in my main data file... this is what i needed u had saved my time and efforts.. thnks again *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Thu, Jul 7, 2011 at 12:42 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this* * * *Sub MailMerge()* *Application.DisplayAlerts = False* *Application.ScreenUpdating = False* *On Error Resume Next* *Dim Form As Worksheet* *Dim detail As Worksheet* *Dim sh As Worksheet* * * *Dim rng As Range* *Dim cell As Range* * * *For Each sh In ActiveWorkbook.Sheets* *If sh.Name Form And sh.Name Details Then sh.Delete* *Next* * * * * *Set Form = Sheets(Form)* *Set detail = Sheets(Details)* *Set rng = detail.Range(A2:A detail.UsedRange.Rows.Count)* * * *result = MsgBox(Do you want to Generatedetail.Range(F5).Value - detail.Range(E5).Value + 1 Records , vbYesNo)* *If result = 6 Then* ** *For i = detail.Range(e5).Value + 1 To detail.Range(F5).Value + 1* *Form.Range(b6).Value = detail.Range(A i).Offset(0, 2).Value detail.Range(A i).Offset(0, 3).Value* *Form.Range(b7).Value = detail.Range(A i).Offset(0, 2).Value detail.Range(A i).Value* *Form.Cells.Copy* *Sheets.Add Form* *ActiveSheet.Paste* *ActiveSheet.Name = detail.Range(A i).Value* *Application.CutCopyMode = False* ** *Next* *End If* *detail.Activate* *Application.DisplayAlerts = False* *Application.ScreenUpdating = False* *End Sub* * * * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Jai *Sent:* Wednesday, July 06, 2011 10:45 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Maill Merge in Two Excel Sheet ** ** Dear Experts i wnat to mail merge From Sheet -2 To Sheet -1, in Sheet -2 , Name, Desination, Emp code . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Converting Text Case By VBA
*dear friends plz check the attached file for changing the text case of selected range *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Thu, Jul 7, 2011 at 10:23 AM, anandydr anand...@gmail.com wrote: Dear STDEV(i), As you say text case can be converted to Proper Case using VBA but wont be using Application.Function/Worksheet Function give faster results as compared to this other method? Warm regards, Anand Kumar On Jul 7, 6:02 am, STDEV(i) setiyowati.d...@gmail.com wrote: Converting text case to ProperCase in VBA alt: Instead of using Application.Function / WorksheetFunction) we can also use VBA function: *StrConv *function StrConv( , vbProperCase) Example in sub procedur Sub ConvertProperCase() Application.ScreenUpdating = False Dim Cell As Range For Each Cell In Selection.Cells If Not Cell.HasFormula Then Cell.Value = *StrConv(Rng, vbProperCase)* End If Next Cell Application.ScreenUpdating = True End Sub Thank you On Thu, Jul 7, 2011 at 3:18 AM, Rajan_Verma rajanverma1...@gmail.com wrote: http://excelpoweruser.blogspot.com/2011/07/hiding-data-on-worksheet.h... *** ** ** -- -- 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 all change case.xlsm Description: Binary data
Re: $$Excel-Macros$$ help regarding date formula
THANKS A LOT MY PROBLEM IS SOLVED NOW BY THESE VALUABLE MEMBERS REGARDS LKMODI On Thu, Jul 7, 2011 at 10:25 AM, L.K. Modi ca.mod...@gmail.com wrote: Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Help on Chart
Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- 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 regarding date formula
*ok sir *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Thu, Jul 7, 2011 at 11:44 AM, L.K. Modi ca.mod...@gmail.com wrote: THANKS A LOT MY PROBLEM IS SOLVED NOW BY THESE VALUABLE MEMBERS REGARDS LKMODI On Thu, Jul 7, 2011 at 10:25 AM, L.K. Modi ca.mod...@gmail.com wrote: Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help on Chart
chart only represent the data of visible cells if you hide any rows or will apply filter it will show the data of visible cells only check the attachment choose any name u will see the chart will automatically change if it is not working as per the default setting . http://office.microsoft.com/en-us/excel-help/display-hidden-worksheet-data-in-a-chart-HP005202880.aspx On Thu, Jul 7, 2011 at 12:11 PM, Atul Kesaria atul.kesa...@gmail.comwrote: Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- 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/ http://akoul.posterous.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 chart.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Help on Chart
*hi atul dynamic chart preparation can help have a look link below http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/ *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Thu, Jul 7, 2011 at 12:11 PM, Atul Kesaria atul.kesa...@gmail.comwrote: Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- 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 on Chart
See if it help -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Kesaria Sent: Thursday, July 07, 2011 12:11 PM To: excel-macros Cc: atul.kesaria Subject: $$Excel-Macros$$ Help on Chart Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- 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 FilterdChart.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ VBA Comment Box - Copy of
Hi Have a photo in a comments box in Database.xls. Also have a vlookup in another workbook Shipment-New.xls to bring forward the text from the cell into the wb. This works great. I also want to bring forward the comment box in the same cell from Database.xls into Shipment-New.xls. Would like to use vlookup function but I believe not possible. Is a vba event better, and do I use vba copy/paste special macro, or is there a better method. Thanks Charlie -- -- 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 Database.xls Description: MS-Excel spreadsheet Shipment-New.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ help regarding date formula
Hi, u use this =DATE(YEAR(C4),MONTH(C4)+1,5)and drag the fill handle down till c4 is not blank. u will get what u want. R.Karunanithi. --- On Thu, 7/7/11, L.K. Modi ca.mod...@gmail.com wrote: From: L.K. Modi ca.mod...@gmail.com Subject: $$Excel-Macros$$ help regarding date formula To: excel-macros@googlegroups.com Date: Thursday, July 7, 2011, 10:25 AM Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- 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 regarding date formula
hi, the betterr is =DATE(YEAR(C4),MONTH(C4)+1,5) because he wants 'next month' 5th. R.Karunanithi --- On Thu, 7/7/11, ashish koul koul.ash...@gmail.com wrote: From: ashish koul koul.ash...@gmail.com Subject: Re: $$Excel-Macros$$ help regarding date formula To: excel-macros@googlegroups.com Date: Thursday, July 7, 2011, 11:26 AM use this =DATE(YEAR(C4),MONTH(C4),5) On Thu, Jul 7, 2011 at 10:25 AM, L.K. Modi ca.mod...@gmail.com wrote: Dear Members, Me attaching a sheet which relates to Excise duty payment.The result should be 5 of the next of the month of Invoice.We are currently doing it manually. But is there any formula by which we can do that fast and in less time. Hope you wil surely help me. Regards LKModi -- -- 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.posterous.com/ akoul.wordpress.com My Linkedin Profile 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
RE: $$Excel-Macros$$ Help on Chart
Hi, It's a pivot chart in your sheets its taking references from Pivot Table , when you will apply filter in Pivot table it will Show Only Filter Summary Data, -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Kesaria Sent: Thursday, July 07, 2011 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Help on Chart Hello Team, I think i was not very clear on my request. Please find attached excel sheet in which there is main data on sheet 1 and on the basis of which I have prepare chart on sheet 6. What I want is as soon as set a filter on Sheet 1 the chart on sheet 6 should be refreshed. Cheers Atul On 7/7/11, Rajan_Verma rajanverma1...@gmail.com wrote: See if it help -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Kesaria Sent: Thursday, July 07, 2011 12:11 PM To: excel-macros Cc: atul.kesaria Subject: $$Excel-Macros$$ Help on Chart Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help on Chart
Hi Rajan, I would be doing filter not on the pivot table...but on the data in the sheet 1 which should reflect in the chart and the table. Cheers Atul On 7/7/11, Rajan_Verma rajanverma1...@gmail.com wrote: Hi, It's a pivot chart in your sheets its taking references from Pivot Table , when you will apply filter in Pivot table it will Show Only Filter Summary Data, -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Kesaria Sent: Thursday, July 07, 2011 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Help on Chart Hello Team, I think i was not very clear on my request. Please find attached excel sheet in which there is main data on sheet 1 and on the basis of which I have prepare chart on sheet 6. What I want is as soon as set a filter on Sheet 1 the chart on sheet 6 should be refreshed. Cheers Atul On 7/7/11, Rajan_Verma rajanverma1...@gmail.com wrote: See if it help -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Kesaria Sent: Thursday, July 07, 2011 12:11 PM To: excel-macros Cc: atul.kesaria Subject: $$Excel-Macros$$ Help on Chart Hello Team, I am preparing chart basis certain data. However wanted to check some way to prepare the chart only on the data which reflect in the excel sheet post i do a filter. So as soon as I change the filter the chart should change. Do let me know if that can be done. Cheers Atul -- -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ VBA Comment Box - Copy of
Hello : Note that the macro should be in the Shipment-New.xls workbook : Sub CopyComments() Dim Rg As Range, C As Comment Dim Sh As Worksheet, x As Range Set Sh = Workbooks(Database.xls).Sheets(Sheet1) With Sheets(Sheet1) For Each Rg In .Range(.[J12], .Cells(.Rows.Count, 10).End(xlUp)) Set x = Sh.[J:J].Find(Rg.Value, , , xlWhole) If Not x Is Nothing Then Set Var = x.Offset(, 1) x.Offset(, 1).Copy Rg.Offset(, 2).PasteSpecial Paste:=xlPasteComments End If Next Rg End With End Sub Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de Cab Boose Envoyé : jeudi 7 juillet 2011 08:56 À : excel-macros@googlegroups.com Objet : $$Excel-Macros$$ VBA Comment Box - Copy of Hi Have a photo in a comments box in Database.xls. Also have a vlookup in another workbook Shipment-New.xls to bring forward the text from the cell into the wb. This works great. I also want to bring forward the comment box in the same cell from Database.xls into Shipment-New.xls. Would like to use vlookup function but I believe not possible. Is a vba event better, and do I use vba copy/paste special macro, or is there a better method. Thanks Charlie -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Macro for a BOQ
Dear Friends, I have a BOQ(Bills of Quantity) and I need to add a formula to each rate. The formula should be as follow. =rate*(Constant cell reference) For an example; The rate on cell E18 is 250.00 (see attached BOQ) It should like this after run the macro '=250*($H$1) It need to repeat for each rate when macro runs. Can any one help? Thanks in advance Regards, Dilan -- -- 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 group.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Pivot Issue
Hi, The data is not displaying in the below code which has written for pivot. Please let me know where is the error is. Thanks in advance Sub pivot_table() Dim pvt_ch As PivotCache Dim pvt_tbl As PivotTable Dim rng As Range Set rng = ThisWorkbook.Worksheets(Formatdata).UsedRange Set pvt_ch = ThisWorkbook.PivotCaches.Add(xlDatabase, rng) ThisWorkbook.Worksheets(Pivot).Select Set pvt_tbl = pvt_ch.CreatePivotTable(Worksheets(Pivot).Range(B3)) With pvt_tbl .AddFields Array(Person Location, Asset, Reported DateTime) End With With pvt_tbl .CalculatedFields.Add Count of Bridge Ticket Number, xlCount .CalculatedFields.Add Count of Reported DateTime, xlCount End With With pvt_tbl.PivotFields(Count of Bridge Ticket Number) .Orientation = xlDataField .Function = xlCount End With With pvt_tbl.PivotFields(Count of Reported DateTime) .Orientation = xlDataField ' .Function = xlCount End With With pvt_tbl.DataPivotField .Orientation = xlColumnField .Position = 1 End With With pvt_tbl pitm_cnt = pvt_tbl.PivotFields(Person Location).PivotItems.Count For j = 1 To pitm_cnt pvt_tbl.PivotFields(Person Location).PivotItems(j).ShowDetail = False Next End With Application.CutCopyMode = False End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Excel question
Hi Experts, Could you please look into my excel query. I have the multiple columns filled with some Revenue values. I need the first month when the revenue started flowing in (anything which is greater than 0). For example – for the Account Name: Mohan, We started recognizing the revenue from ‘3/31/2010’ so ‘Revenue started from Month’ Column should have 3/31/2010 as the output. Excel formula or Macro . Anything is fine with me :-) Thanking you in Advance. -- Regards, AK -- -- 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 ABC sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Convert HTML file to Excel
Thanks a lot Rajan. But the below macro is not running in Excel 2003 or any reference need to select from Tools. Please confirm. Regards, Shrinivas Citi(r) Global Transaction Services - India UB City, Canberra Block, # 24, Vittal Mallya Road, Bangalore - 56 00 01. Ph- +91-80-4144 6339 / 6340 Email - shrinivas.chidur...@citi.com -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Rajan_Verma Sent: Thursday, July 07, 2011 1:23 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Convert HTML file to Excel See if it Help. Sub Compile() On Error GoTo Err_Clear: Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Fso As New Scripting.FileSystemObject Dim Path As String Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Pick Downloaded Bills Application.FileDialog(msoFileDialogFolderPicker).Show Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If Path = Then Exit Sub Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Save Compiled File CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show compiledPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If compiledPath = Then Exit Sub Dim Counter Dim File As File Dim FOlder As FOlder Dim wb As Workbook Dim ws As Worksheet Dim AcWb As Workbook Set AcWb = ActiveWorkbook ActiveWorkbook.Sheets.Add ActiveSheet.Name = Index Set FOlder = Fso.GetFolder(Path) For Each File In FOlder.Files Counter = Counter + 1 Set wb = Workbooks.Open(Path File.Name) If Application.Ready = True Then wb.Sheets(Index).Activate Call bulkUpload.GenerateReport ActiveSheet.UsedRange.Copy AcWb.Sheets(Index).Activate Range(A100).End(xlUp).Select ActiveSheet.Paste Application.CutCopyMode = False wb.Close End If Next If Counter 0 Then AcWb.SaveAs compiledPath Compiled, xlExcel12 AcWb.Close End If Err_Clear: Err.Clear Resume Next Application.DisplayAlerts = True Application.ScreenUpdating = True If Counter 1 Then MsgBox No File Found For Compile, vbInformation Else MsgBox Counter File Has been Compiled, Please Find your File at vbCrLf compiledPath, vbInformation End If End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Chidurala, Shrinivas Sent: Wednesday, July 06, 2011 5:00 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Convert HTML file to Excel Dear Excel Gurus, I have some HTML files which are saved in same folder and I want to convert them into Excel and merger the all files into 1 workbook. Please help me to create the macro for the same. Find attached 2 HTML files and required report. Regards, Shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- 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
Re: $$Excel-Macros$$ Excel question
Pls find a UDF Public Function GetFirstRevenueDate(Rng As Range) For Each cls In Rng.Cells If cls.Value 0 Then Dt = Cells(1, cls.Column) GetFirstRevenueDate = Format(Dt, dd-mmm-) Exit Function End If Next End Function File attached. On Thu, Jul 7, 2011 at 10:05 PM, AK exce...@gmail.com wrote: Hi Experts, Could you please look into my excel query. I have the multiple columns filled with some Revenue values. I need the first month when the revenue started flowing in (anything which is greater than 0). For example – for the Account Name: Mohan, We started recognizing the revenue from ‘3/31/2010’ so ‘Revenue started from Month’ Column should have 3/31/2010 as the output. Excel formula or Macro . Anything is fine with me :-) Thanking you in Advance. -- Regards, AK -- -- 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 Vasant skype Id: vasantjob http://facebook.com/vasantjob -- -- 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 ABC_sheet.xlsm Description: Binary data
Re: $$Excel-Macros$$ Convert HTML file to Excel
Yes Rajan, the code is calling a procedure in Call bulkUpload.GenerateReport The procedure has not been included. Could you please include that also ? On Thu, Jul 7, 2011 at 10:45 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Thanks a lot Rajan. But the below macro is not running in Excel 2003 or any reference need to select from Tools. Please confirm. Regards, Shrinivas Citi(r) Global Transaction Services - India UB City, Canberra Block, # 24, Vittal Mallya Road, Bangalore - 56 00 01. Ph- +91-80-4144 6339 / 6340 Email - shrinivas.chidur...@citi.com -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Rajan_Verma Sent: Thursday, July 07, 2011 1:23 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Convert HTML file to Excel See if it Help. Sub Compile() On Error GoTo Err_Clear: Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Fso As New Scripting.FileSystemObject Dim Path As String Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Pick Downloaded Bills Application.FileDialog(msoFileDialogFolderPicker).Show Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If Path = Then Exit Sub Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Save Compiled File CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show compiledPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If compiledPath = Then Exit Sub Dim Counter Dim File As File Dim FOlder As FOlder Dim wb As Workbook Dim ws As Worksheet Dim AcWb As Workbook Set AcWb = ActiveWorkbook ActiveWorkbook.Sheets.Add ActiveSheet.Name = Index Set FOlder = Fso.GetFolder(Path) For Each File In FOlder.Files Counter = Counter + 1 Set wb = Workbooks.Open(Path File.Name) If Application.Ready = True Then wb.Sheets(Index).Activate Call bulkUpload.GenerateReport ActiveSheet.UsedRange.Copy AcWb.Sheets(Index).Activate Range(A100).End(xlUp).Select ActiveSheet.Paste Application.CutCopyMode = False wb.Close End If Next If Counter 0 Then AcWb.SaveAs compiledPath Compiled, xlExcel12 AcWb.Close End If Err_Clear: Err.Clear Resume Next Application.DisplayAlerts = True Application.ScreenUpdating = True If Counter 1 Then MsgBox No File Found For Compile, vbInformation Else MsgBox Counter File Has been Compiled, Please Find your File at vbCrLf compiledPath, vbInformation End If End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Chidurala, Shrinivas Sent: Wednesday, July 06, 2011 5:00 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Convert HTML file to Excel Dear Excel Gurus, I have some HTML files which are saved in same folder and I want to convert them into Excel and merger the all files into 1 workbook. Please help me to create the macro for the same. Find attached 2 HTML files and required report. Regards, Shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links
Re: $$Excel-Macros$$ Excel question
Hi, C2 copy down. =IFERROR(INDEX(D$1:SF$1,MATCH(TRUE,INDEX(ISNUMBER(1/D2:SF2),),0)),Not Started) HTH Haseeb -- -- 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$$ Pivot Issue
Please attached corresponding data From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Chandra Shekar Sent: Thursday, July 07, 2011 8:15 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Pivot Issue Hi, The data is not displaying in the below code which has written for pivot. Please let me know where is the error is. Thanks in advance Sub pivot_table() Dim pvt_ch As PivotCache Dim pvt_tbl As PivotTable Dim rng As Range Set rng = ThisWorkbook.Worksheets(Formatdata).UsedRange Set pvt_ch = ThisWorkbook.PivotCaches.Add(xlDatabase, rng) ThisWorkbook.Worksheets(Pivot).Select Set pvt_tbl = pvt_ch.CreatePivotTable(Worksheets(Pivot).Range(B3)) With pvt_tbl .AddFields Array(Person Location, Asset, Reported DateTime) End With With pvt_tbl .CalculatedFields.Add Count of Bridge Ticket Number, xlCount .CalculatedFields.Add Count of Reported DateTime, xlCount End With With pvt_tbl.PivotFields(Count of Bridge Ticket Number) .Orientation = xlDataField .Function = xlCount End With With pvt_tbl.PivotFields(Count of Reported DateTime) .Orientation = xlDataField ' .Function = xlCount End With With pvt_tbl.DataPivotField .Orientation = xlColumnField .Position = 1 End With With pvt_tbl pitm_cnt = pvt_tbl.PivotFields(Person Location).PivotItems.Count For j = 1 To pitm_cnt pvt_tbl.PivotFields(Person Location).PivotItems(j).ShowDetail = False Next End With Application.CutCopyMode = False End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Excel question
Try this Function in C2 Function GetStartDate(RevRange As Range) As Variant Dim cell As Range For Each cell In RevRange If cell.Value 0 Then GetStartDate = Cells(1, cell.Column).Value Exit Function End If Next End Function From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of AK Sent: Thursday, July 07, 2011 10:06 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Excel question Hi Experts, Could you please look into my excel query. I have the multiple columns filled with some Revenue values. I need the first month when the revenue started flowing in (anything which is greater than 0). For example - for the Account Name: Mohan, We started recognizing the revenue from '3/31/2010' so 'Revenue started from Month' Column should have 3/31/2010 as the output. Excel formula or Macro . Anything is fine with me :-) Thanking you in Advance. -- Regards, AK -- -- 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$$ Convert HTML file to Excel
Please Remove that Line From the Code. It will work fine.. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Vasant Sent: Thursday, July 07, 2011 11:04 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Convert HTML file to Excel Yes Rajan, the code is calling a procedure in Call bulkUpload.GenerateReport The procedure has not been included. Could you please include that also ? On Thu, Jul 7, 2011 at 10:45 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Thanks a lot Rajan. But the below macro is not running in Excel 2003 or any reference need to select from Tools. Please confirm. Regards, Shrinivas Citi(r) Global Transaction Services - India UB City, Canberra Block, # 24, Vittal Mallya Road, Bangalore - 56 00 01. Ph- +91-80-4144 6339 / 6340 Email - shrinivas.chidur...@citi.com -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Rajan_Verma Sent: Thursday, July 07, 2011 1:23 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Convert HTML file to Excel See if it Help. Sub Compile() On Error GoTo Err_Clear: Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Fso As New Scripting.FileSystemObject Dim Path As String Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Pick Downloaded Bills Application.FileDialog(msoFileDialogFolderPicker).Show Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If Path = Then Exit Sub Application.FileDialog(msoFileDialogFolderPicker).Title = Select Folder to Save Compiled File CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show compiledPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If compiledPath = Then Exit Sub Dim Counter Dim File As File Dim FOlder As FOlder Dim wb As Workbook Dim ws As Worksheet Dim AcWb As Workbook Set AcWb = ActiveWorkbook ActiveWorkbook.Sheets.Add ActiveSheet.Name = Index Set FOlder = Fso.GetFolder(Path) For Each File In FOlder.Files Counter = Counter + 1 Set wb = Workbooks.Open(Path File.Name) If Application.Ready = True Then wb.Sheets(Index).Activate ActiveSheet.UsedRange.Copy AcWb.Sheets(Index).Activate Range(A100).End(xlUp).Select ActiveSheet.Paste Application.CutCopyMode = False wb.Close End If Next If Counter 0 Then AcWb.SaveAs compiledPath Compiled, xlExcel12 AcWb.Close End If Err_Clear: Err.Clear Resume Next Application.DisplayAlerts = True Application.ScreenUpdating = True If Counter 1 Then MsgBox No File Found For Compile, vbInformation Else MsgBox Counter File Has been Compiled, Please Find your File at vbCrLf compiledPath, vbInformation End If End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Chidurala, Shrinivas Sent: Wednesday, July 06, 2011 5:00 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Convert HTML file to Excel Dear Excel Gurus, I have some HTML files which are saved in same folder and I want to convert them into Excel and merger the all files into 1 workbook. Please help me to create the macro for the same. Find attached 2 HTML files and required report. Regards, Shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com 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 http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
Re: $$Excel-Macros$$ Excel question
=INDEX($1:$1,0,SMALL(IF(D2:AB20,COLUMN(D2:AB2),),1)) press ctrl +shift+ enter check the attachment On Fri, Jul 8, 2011 at 12:31 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this Function in C2* * * *Function GetStartDate(RevRange As Range) As Variant* *Dim cell As Range* *For Each cell In RevRange* *If cell.Value 0 Then* *GetStartDate = Cells(1, cell.Column).Value* *Exit Function* *End If* *Next* *End Function* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *AK *Sent:* Thursday, July 07, 2011 10:06 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Excel question ** ** Hi Experts, ** ** Could you please look into my excel query. I have the multiple columns filled with some Revenue values. I need the first month when the revenue started flowing in (anything which is greater than 0). ** ** For example – for the Account Name: Mohan, We started recognizing the revenue from ‘3/31/2010’ so ‘Revenue started from Month’ Column should have 3/31/2010 as the output. ** ** Excel formula or Macro . Anything is fine with me :-) Thanking you in Advance. -- Regards, AK -- -- 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/ http://akoul.posterous.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 ABC sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ copy data without opening file
Hi Ankur, this is FANTASTIC. thanks a lot. From: ankur ankurpande...@gmail.com To: excel-macros@googlegroups.com Sent: Tue, 5 July, 2011 12:16:06 PM Subject: Re: $$Excel-Macros$$ copy data without opening file check this also one sample file and explanations is given http://www.vbaexpress.com/kb/getarticle.php?kb_id=454 Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Tue, Jul 5, 2011 at 12:13 PM, ankur ankurpande...@gmail.com wrote: hi hanumant you may find this to be useful http://spreadsheetpage.com/index.php...a_closed_file/ Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Tue, Jul 5, 2011 at 12:40 AM, hanumant shinde arsfan2...@yahoo.co.in wrote: Hi friends, is there any way where we can copy data from closed workbook A and paste it into workbook B which is open? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ copy data without opening file
thanks rajan. this is really great. i didnt know abt this. thanks again From: Rajan_Verma rajanverma1...@gmail.com To: excel-macros@googlegroups.com Sent: Tue, 5 July, 2011 12:53:47 PM Subject: RE: $$Excel-Macros$$ copy data without opening file No, We cant Copy the data from Closed Workbook.. but we can get the Data by Using Full Reference of cell .. Like ='C:\Users\Xl_Lover\Desktop\[ForDownload[Sheet1]]Sheet1'!A1 From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of ankur Sent: Tuesday, July 05, 2011 12:13 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ copy data without opening file hi hanumant you may find this to be useful http://spreadsheetpage.com/index.php...a_closed_file/ Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Tue, Jul 5, 2011 at 12:40 AM, hanumant shinde arsfan2...@yahoo.co.in wrote: Hi friends, is there any way where we can copy data from closed workbook A and paste it into workbook B which is open? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel ~WRD000.jpg
Re: $$Excel-Macros$$ VBA Comment Box - Copy of
Hi Daniel Thanks for code. Have done as you suggest and installed in the Shipments wb. When I run the code it goies thru to var =... and gives a message that ' variable not defined ' . Is there something missding ? Your help appreciated. Thanks Charlie On Fri, Jul 8, 2011 at 3:33 AM, ashish koul koul.ash...@gmail.com wrote: try this attachment run the macro it will open dialog box and choose the database file On Thu, Jul 7, 2011 at 3:17 PM, Daniel dcolarde...@free.fr wrote: Hello : ** ** Note that the macro should be in the Shipment-New.xls workbook : ** ** Sub CopyComments() Dim Rg As Range, C As Comment Dim Sh As Worksheet, x As Range Set Sh = Workbooks(Database.xls).Sheets(Sheet1) With Sheets(Sheet1) For Each Rg In .Range(.[J12], .Cells(.Rows.Count, 10).End(xlUp)) Set x = Sh.[J:J].Find(Rg.Value, , , xlWhole) If Not x Is Nothing Then Set Var = x.Offset(, 1) x.Offset(, 1).Copy Rg.Offset(, 2).PasteSpecial Paste:=xlPasteComments End If Next Rg End With End Sub ** ** Regards. Daniel ** ** *De :* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *De la part de* Cab Boose *Envoyé :* jeudi 7 juillet 2011 08:56 *À :* excel-macros@googlegroups.com *Objet :* $$Excel-Macros$$ VBA Comment Box - Copy of ** ** Hi Have a photo in a comments box in Database.xls. Also have a vlookup in another workbook Shipment-New.xls to bring forward the text from the cell into the wb. This works great. I also want to bring forward the comment box in the same cell from Database.xls into Shipment-New.xls. Would like to use vlookup function but I believe not possible. Is a vba event better, and do I use vba copy/paste special macro, or is there a better method. Thanks Charlie -- -- 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/ http://akoul.posterous.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
$$Excel-Macros$$ Help regarding bank statment
Dear Members, Thanks for your valuable support. I have an querry in which i have to check interest calculation for banks. Me attaching a sheet in which column A have date that is in different format. and in column B the balance as on the respective dates. As the bank charges interest on the closing balance for particular dates. So there are many balances as on the same date. What i need first that the date should be in format of dd-mmm-yy and secondly like there are many balances of 1st june so i need only the closing balance of 1st june and so on. it means last line that is in 1st june this i have to do for all dates. can anyone help me as this is more time taking without proper direction. Thanks in advance Regards LKModi -- -- 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 test sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Help Required Urgent :)
add this code to new workbook and run the macro and choose the csv file Option Compare Text Sub swa() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim s As Workbook Dim i, j, k As Long Set s = Workbooks.Open(Application.GetOpenFilename) j = 1 For i = 1 To s.Sheets(1).Range(a1048576).End(xlUp).Row s.Activate If s.Sheets(1).Cells(i, 1).Value = TITLERIGHT Then k = i s.Sheets(1).Rows(j : k).Copy ThisWorkbook.Activate ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Range(a1).Select ActiveSheet.Paste j = i i = i + 1 End If Next i ThisWorkbook.Sheets(4).Activate Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub On Fri, Jul 8, 2011 at 3:49 AM, ICWAI Help icwai.answ...@gmail.com wrote: Hi Ashish, i have date in sheet its huge date i want the date between The row highlighted *TITLE RIGHT should copy into new sheet* *like * ** *1, first select hte data between Two Title right and paste into new sheet.* *total we need to create 80 sheets as i have huge data.* ** ** *Thanks,* *Rakesh.* -- -- 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/ http://akoul.posterous.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$$ Help regarding bank statment
hi check the attachment see if it helps On Fri, Jul 8, 2011 at 10:09 AM, L.K. Modi ca.mod...@gmail.com wrote: Dear Members, Thanks for your valuable support. I have an querry in which i have to check interest calculation for banks. Me attaching a sheet in which column A have date that is in different format. and in column B the balance as on the respective dates. As the bank charges interest on the closing balance for particular dates. So there are many balances as on the same date. What i need first that the date should be in format of dd-mmm-yy and secondly like there are many balances of 1st june so i need only the closing balance of 1st june and so on. it means last line that is in 1st june this i have to do for all dates. can anyone help me as this is more time taking without proper direction. Thanks in advance Regards LKModi -- -- 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/ http://akoul.posterous.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 test sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Help regarding bank statment
HOPE THIS MIGHT BE USEFUL. On 8 July 2011 10:09, L.K. Modi ca.mod...@gmail.com wrote: Dear Members, Thanks for your valuable support. I have an querry in which i have to check interest calculation for banks. Me attaching a sheet in which column A have date that is in different format. and in column B the balance as on the respective dates. As the bank charges interest on the closing balance for particular dates. So there are many balances as on the same date. What i need first that the date should be in format of dd-mmm-yy and secondly like there are many balances of 1st june so i need only the closing balance of 1st june and so on. it means last line that is in 1st june this i have to do for all dates. can anyone help me as this is more time taking without proper direction. Thanks in advance Regards LKModi -- -- 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 test_sheet(1).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Query
Thanks Ashish Thanks Ankur On Thu, Jul 7, 2011 at 11:51 AM, ankur ankurpande...@gmail.com wrote: * *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Wed, Jul 6, 2011 at 6:05 PM, ankur ankurpande...@gmail.com wrote: *hi ashish sir may this help u ... *Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ On Wed, Jul 6, 2011 at 5:30 PM, ashish koul koul.ash...@gmail.comwrote: On Wed, Jul 6, 2011 at 4:56 PM, Kal xcel kalx...@gmail.com wrote: Dear Experts, For this chart 3001-5000 = 30 5001-6000 = 40 6001-7000 = 45 7001-8000 = 50 8001-9000 = 50 9001-15000 = 110 15001-25000 = 130 25001-4 = 150 40001= =200 I use this formula: =IF(AND(D16=3001,D16=5000),30,IF(AND(D16=5001,D16=6000),40,IF(AND(D16=6001,D16=7000),45,IF(AND(D16=7001,D16=8000),50,IF(AND(D16=8001,D16=9000),90,IF(AND(D16=9001,D16=15000),110,IF(AND(D16=15001,D16=25000),130,IF(AND(D16=25001,D16=4),150,IF(AND(D16=40001),200,0) Is their any other short/intelligent way to get desired result? Please help Thanks in advance Kalyan -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ http://akoul.posterous.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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Excel question
Thanks Vasant, Haseeb, Rajan and Ashish for your solution .. you guys are Master ! Regards, AK On Fri, Jul 8, 2011 at 5:39 AM, ashish koul koul.ash...@gmail.com wrote: =INDEX($1:$1,0,SMALL(IF(D2:AB20,COLUMN(D2:AB2),),1)) press ctrl +shift+ enter check the attachment On Fri, Jul 8, 2011 at 12:31 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this Function in C2* * * *Function GetStartDate(RevRange As Range) As Variant* *Dim cell As Range* *For Each cell In RevRange* *If cell.Value 0 Then* *GetStartDate = Cells(1, cell.Column).Value* *Exit Function* *End If* *Next* *End Function* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *AK *Sent:* Thursday, July 07, 2011 10:06 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Excel question ** ** Hi Experts, ** ** Could you please look into my excel query. I have the multiple columns filled with some Revenue values. I need the first month when the revenue started flowing in (anything which is greater than 0). ** ** For example – for the Account Name: Mohan, We started recognizing the revenue from ‘3/31/2010’ so ‘Revenue started from Month’ Column should have 3/31/2010 as the output. ** ** Excel formula or Macro . Anything is fine with me :-) Thanking you in Advance. -- Regards, AK -- -- 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/ http://akoul.posterous.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 -- Regards, AK -- -- 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