Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel
what in case of address which one it should pic absolute address access address On Fri, May 13, 2011 at 8:42 PM, Ivaylo ivanov.iva...@gmail.com wrote: Hi Ashish, Thank you very much for your efforts! Your macro is just what I needed. However, it seems that it doesn't handle the exceptions: === How to handle exceptions: If the consituent word doesn't have its own headword in the list, then the missing word must be copied in Sheet 2, column A and the phrase from which it comes from in column B - i.e. Sheet 2 must be list all the words which are part of a multiword phrases but don't have their own headwords. This is the case with abnormal termination. The word abnormal exists as a headword, and the phrase abnormal termination is copied in column B next to abnormal. However, the word termination doesn't exist in column A (as a headword), then termination must be copied to column A in Sheet 2 and the phrase abnormal termination to column B in Sheet 2 (next to termination). === Is it possible for you to add this functionality? On May 13, 4:26 pm, ashish koul koul.ash...@gmail.com wrote: try this code Sub tests() Dim i As Long Dim z As String For i = 1 To Sheets(1).Range(a65356).End(xlUp).Row If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), ) 0 Then Cells(i, 2) = Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim(Cells(i, 1)), , |) End If Next i For i = 1 To Sheets(1).Range(a65356).End(xlUp).Row z = If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), ) = 0 Then For swa = 1 To Sheets(1).Range(a65356).End(xlUp).Row If swa i Then If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)), Application.WorksheetFunction.Trim(Cells(i, 1))) 0 Then z = z Application.WorksheetFunction.Trim(Cells(swa, 1)) | End If End If Next swa If Right(z, 1) = | Then Cells(i, 2) = Left(z, Len(z) - 1) Else Cells(i, 2) = z End If End If Next i End Sub On Wed, May 11, 2011 at 6:03 PM, Ivaylo ivanov.iva...@gmail.com wrote: Hi to all VBA gurus, I need help with an Excel macro which can do the following: I have a long list of dictionary headwords in column A (sorted alphabetically), something like this: abnormal termination abnormal abort about absolute absolute address absolute code accent access access address access mode access control access time address ... The list consists of single words and multiword phrases. My task is to put in column B reference to the single words that constitute the multiword phrases. For example, for abnormal termination column B must be populated with abnormal and termination, separated by |: abnormal termination abnormal|termination The second part of this task is that for the single-word headwords I must put references to their corresponding multiword phrases. For example, for absolute column B must be populated with absolute address and absolute code, separated by |: absolute absolute address|absolute code Here is a desired output after the processing: Column A - Column B abnormal - termination abnormal|termination abnormal - abnormal termination abort - about - absolute - absolute address|absolute code absolute address - absolute|address absolute code - absolute|code accent - access access - address|access mode|access control|access time access address - access|address access mode - access|mode access control - access|control access time - access|time address - absolute address|access address How to handle exceptions: If the consituent word doesn't have its own headword in the list, then the missing word must be copied in Sheet 2, column A and the phrase from which it comes from in column B - i.e. Sheet 2 must be list all the words which are part of a multiword phrases but don't have their own headwords. This is the case with abnormal termination. The word abnormal exists as a headword, and the phrase abnormal termination is copied in column B next to abnormal. However, the word termination doesn't exist in column A (as a headword), then termination must be copied to column A in Sheet 2 and the phrase abnormal termination to column B in Sheet 2 (next to termination). I'll be very grateful if anyone can help me writing such a macro. -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros
$$Excel-Macros$$ Re: Rank the data
cells F5 =RANK(E5,$E$5:$E$14,0) ... cells F14 =RANK(E14,$E$5:$E$14,0) everything is OK, good luck for you On 5月13日, 上午10時50分, Rajesh K R rajeshkainikk...@gmail.com wrote: Hi Experts, Pls find the attachment and tell me how to rank the rooms as per the collection received from them. Regards Rajesh Kainikkara RANKING.xls 18K檢視下載 -- -- 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 some files from a folder to another folder
use move to move files and copy to copy the files Public f As Object, d As Object Sub Main() Dim Fso As Object Set Fso = CreateObject(Scripting.FileSystemObject) Set RootFolder = Fso.GetFolder(C:\Documents and Settings\ashish\Desktop\ashish koul) FolderRead RootFolder End Sub Sub FolderRead(ByRef myFolder) Dim sw As Object Set sw = CreateObject(Scripting.FileSystemObject) For Each f In myFolder.Files Var = f.Path If Right(f.Name, 4) = .xls Or Right(f.Name, 5) = .xlsx Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ End If If Right(f.Name, 5) = .jpeg Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ End If If Right(f.Name, 4) = .pdf Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ End If Next f For Each d In myFolder.SubFolders FolderRead d Next d End Sub On Sat, May 14, 2011 at 7:15 AM, Rafael Bruno reservagra...@gmail.comwrote: Hi friends, I have a simple request that it's confusing me. I have a folder (let's call c:\motherfolder\), that has thousands of subfolders. In each subfolder there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1 .jpeg. I need to copy all pdfs to one folder (let's say c:\pdfs_folder\), all excel files to another, and all jpegs to another. Can it be done? A long time ago (years) I remember doing something similar, but only with .xls files (and even this I can't remember how). Thanks in advance, Rafael WebRep Overall rating -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Copy some files from a folder to another folder
use this one Public f As Object, d As Object, sw As Object Sub Main() Dim Fso As Object Set sw = CreateObject(Scripting.FileSystemObject) Set Fso = CreateObject(Scripting.FileSystemObject) Set RootFolder = Fso.GetFolder(C:\Documents and Settings\ashish\Desktop\ashish koul) FolderRead RootFolder End Sub Sub FolderRead(ByRef myFolder) For Each f In myFolder.Files Var = f.Path If Right(f.Name, 4) = .xls Or Right(f.Name, 5) = .xlsx Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ End If If Right(f.Name, 5) = .jpeg Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ End If If Right(f.Name, 4) = .pdf Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ End If Next f For Each d In myFolder.SubFolders FolderRead d Next d End Sub On Sat, May 14, 2011 at 12:19 PM, ashish koul koul.ash...@gmail.com wrote: use move to move files and copy to copy the files Public f As Object, d As Object Sub Main() Dim Fso As Object Set Fso = CreateObject(Scripting.FileSystemObject) Set RootFolder = Fso.GetFolder(C:\Documents and Settings\ashish\Desktop\ashish koul) FolderRead RootFolder End Sub Sub FolderRead(ByRef myFolder) Dim sw As Object Set sw = CreateObject(Scripting.FileSystemObject) For Each f In myFolder.Files Var = f.Path If Right(f.Name, 4) = .xls Or Right(f.Name, 5) = .xlsx Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ End If If Right(f.Name, 5) = .jpeg Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ End If If Right(f.Name, 4) = .pdf Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ End If Next f For Each d In myFolder.SubFolders FolderRead d Next d End Sub On Sat, May 14, 2011 at 7:15 AM, Rafael Bruno reservagra...@gmail.comwrote: Hi friends, I have a simple request that it's confusing me. I have a folder (let's call c:\motherfolder\), that has thousands of subfolders. In each subfolder there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1 .jpeg. I need to copy all pdfs to one folder (let's say c:\pdfs_folder\), all excel files to another, and all jpegs to another. Can it be done? A long time ago (years) I remember doing something similar, but only with .xls files (and even this I can't remember how). Thanks in advance, Rafael WebRep Overall rating -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Copy some files from a folder to another folder
Hi Ashish well done ! Appreciate your knowledge help ! On Sat, May 14, 2011 at 12:19 PM, ashish koul koul.ash...@gmail.com wrote: use move to move files and copy to copy the files Public f As Object, d As Object Sub Main() Dim Fso As Object Set Fso = CreateObject(Scripting.FileSystemObject) Set RootFolder = Fso.GetFolder(C:\Documents and Settings\ashish\Desktop\ashish koul) FolderRead RootFolder End Sub Sub FolderRead(ByRef myFolder) Dim sw As Object Set sw = CreateObject(Scripting.FileSystemObject) For Each f In myFolder.Files Var = f.Path If Right(f.Name, 4) = .xls Or Right(f.Name, 5) = .xlsx Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\excel\ End If If Right(f.Name, 5) = .jpeg Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\jpeg\ End If If Right(f.Name, 4) = .pdf Then sw.copyFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ 'sw.MoveFile f.Path, C:\Documents and Settings\ashish\Desktop\pdf\ End If Next f For Each d In myFolder.SubFolders FolderRead d Next d End Sub On Sat, May 14, 2011 at 7:15 AM, Rafael Bruno reservagra...@gmail.comwrote: Hi friends, I have a simple request that it's confusing me. I have a folder (let's call c:\motherfolder\), that has thousands of subfolders. In each subfolder there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1 .jpeg. I need to copy all pdfs to one folder (let's say c:\pdfs_folder\), all excel files to another, and all jpegs to another. Can it be done? A long time ago (years) I remember doing something similar, but only with .xls files (and even this I can't remember how). Thanks in advance, Rafael WebRep Overall rating -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ maximum value of scroll bar should be linked to max data of individual sheet
Dear All, Is it possible that maximum value of scroll bar should be linked to total number of entries in related data of individual sheet? If A2 is Administration, Business and Of Max value of scroll bar should be 46. If A2 is Building and Construction, Max value of scroll bar should be 63. If A2 is Catering and Hospitality, Max value of scroll bar should be 20. This is avoid Zeros coming in cells when scrolling down beyond 20 or 46 for second third sheet data. Regards, ZAFAR IQBAL Mobile: 0556 014 035 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of STDEV(i) Sent: Thursday, May 12, 2011 3:04 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help to make my project good. please check the attachment if it helps.. best regards, STDEV(i) note: we need dynamic Max Value of ScrollBar so we use Scroolbar from ActiveX Control, instead of from FORM. A little macro wil work each time you change the career On Wed, May 11, 2011 at 5:21 PM, karan 1237 karan1...@gmail.com wrote: Respected Sirs/Mam, I need your help making my project good. Please find attachment. I will be very obliged if someone can help me as earlier as possible. -- Basically I have to do that when I click on drop down list select another career i.e. Building and Construction so In sub career list data should comes from the Building and construction's sheet and If I select another career i.e. Catering and Hospitality so data should comes from its list (Catering and Hospitality) and scroll bar should work with the same. I have 16 careers so please tell me how can I do this. Thnx in Advance ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı -- -- 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$$ no of days in the month ?
Dear Friends i want find total no days in the month i.e January = 31 days Feb = 29 days march = 30 days like -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- 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$$ no of days in the month ?
find attached Sheet On Sat, May 14, 2011 at 3:36 PM, jmothilal gjmothi...@gmail.com wrote: Dear Friends i want find total no days in the month i.e January = 31 days Feb = 29 days march = 30 days like -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Book1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Problem regarding rank formula
Hi experts, pls check the attached file and solve my issue regarding ranking the rooms according they are used for rent. In column H I given the formula for rank it, but it shows wrong values, the answer required given in columni. Regards Rajesh Kainikkara -- -- 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 RANKING(1).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Calculate Due dates
Thanks ashish bhayya for that great formula.Upon drilling deep into the formula the result,i feel we are into the logical error.To make it clear, for Supplier A, if the transaction date is *12/05/2001*.the formula should return the previous month just like, *05-Apr'2011 TO 04-May'2011.Kindly update the formula. Thanks in advance. * On Thu, May 12, 2011 at 9:41 AM, Anil Bhange anil.bha...@tatacommunications.com wrote: I think Ashish is right use this, Still try with this “mmm'” Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Mr excel *Sent:* Wednesday, May 11, 2011 8:37 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Calculate Due dates what i meant is instead of the period,the formula is showing as a result the formula should return only the month with the year. For example,*05-Apr-2011 TO 04-May-2011*. should return apr'2011. hope this is not confusing you...ashish... On Sat, May 7, 2011 at 1:25 PM, ashish koul koul.ash...@gmail.com wrote: change dd-mmm- to dd-mmm' in formula On Sat, May 7, 2011 at 7:47 AM, Mr excel excelkeec...@gmail.com wrote: dear ashish,, That is excellent great.Thanks a lot .The formula u provided is very nearer to the solution i wanted.everthing is same except in the month column of the sheet.Name of the month should be printed like April'2011 instead of *05-Apr-2011 TO 04-May-2011*. the above formula also is very helpful to me in another scenario.. Really my joy knew no boundaries.. On Fri, May 6, 2011 at 6:46 AM, ashish koul koul.ash...@gmail.com wrote: try this one On Thu, May 5, 2011 at 9:11 PM, ashish koul koul.ash...@gmail.com wrote: see if it helps On Thu, May 5, 2011 at 7:23 AM, Mr excel excelkeec...@gmail.com wrote: For example, The billing cycle for supplier A is 5th of this month to 4th next so all the transactions pertaining to Supplier A will be billed in this period. Here in this example, there is only one transaction related to A and the billing is done for the month of jan'2011 so in the month column Jan'2011 should be displayed as this single transaction is done between 5th of Jan'2011-4th Feb'2011 Likewise for all the other suppliers… Hope I had made it clear. On Wed, May 4, 2011 at 9:57 PM, ashish koul koul.ash...@gmail.com wrote: can you expaln with some example in workbook On Tue, May 3, 2011 at 8:30 PM, keechak excelkeec...@gmail.com wrote: Hi forum, Thanks very much for creating such a wonderful forum. I had a problem in calculating the due dates for different customers. Col A contains suppliers with the dates they supply the raw material amount of the material supplied in the next columns B C Respectively. I need a formula or a vba routine which can calculate the due dates for all the suppliers who supply the material. There are certain cut-off dates for each supplier in calculating the due dates in the billing cycle table. I want to print the months in Column D basing upon the supply date for each supplier and the cut-off dates in the right side table(Billing Cycle -table). Please help me WebRep Overall rating -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel WebRep Overall rating -- -- Some important links for excel
Re: $$Excel-Macros$$ no of days in the month ?
thanks to mr verma, with his formula =DAY(DATE(YEAR(TODAY()),MONTH(*(B21)*)+1,1)-1) how about =DAY(DATE(YEAR(TODAY()),MONTH((B21))+1,*0*)) The *last day of this month* is = the *0*th day of the Next Month !! On Sat, May 14, 2011 at 7:03 PM, rajan verma rajanverma1...@gmail.com wrote: find attached Sheet On Sat, May 14, 2011 at 3:36 PM, jmothilal gjmothi...@gmail.com wrote: Dear Friends i want find total no days in the month i.e January = 31 days Feb = 29 days march = 30 days like -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- 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$$
use your keyboard On Wed, May 11, 2011 at 6:15 PM, jmothilal gjmothi...@gmail.com wrote: I am typing Name and place name day 2 day different excel files, what is easy method i type these names and place names. -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- 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$$ Calculate Due dates
can you attach the sample sheet On Sat, May 14, 2011 at 6:20 PM, Mr excel excelkeec...@gmail.com wrote: Thanks ashish bhayya for that great formula.Upon drilling deep into the formula the result,i feel we are into the logical error.To make it clear, for Supplier A, if the transaction date is *12/05/2001*.the formula should return the previous month just like, *05-Apr'2011 TO 04-May'2011.Kindly update the formula. Thanks in advance. * On Thu, May 12, 2011 at 9:41 AM, Anil Bhange anil.bha...@tatacommunications.com wrote: I think Ashish is right use this, Still try with this “mmm'” Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Mr excel *Sent:* Wednesday, May 11, 2011 8:37 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Calculate Due dates what i meant is instead of the period,the formula is showing as a result the formula should return only the month with the year. For example,*05-Apr-2011 TO 04-May-2011*. should return apr'2011. hope this is not confusing you...ashish... On Sat, May 7, 2011 at 1:25 PM, ashish koul koul.ash...@gmail.com wrote: change dd-mmm- to dd-mmm' in formula On Sat, May 7, 2011 at 7:47 AM, Mr excel excelkeec...@gmail.com wrote: dear ashish,, That is excellent great.Thanks a lot .The formula u provided is very nearer to the solution i wanted.everthing is same except in the month column of the sheet.Name of the month should be printed like April'2011 instead of *05-Apr-2011 TO 04-May-2011*. the above formula also is very helpful to me in another scenario.. Really my joy knew no boundaries.. On Fri, May 6, 2011 at 6:46 AM, ashish koul koul.ash...@gmail.com wrote: try this one On Thu, May 5, 2011 at 9:11 PM, ashish koul koul.ash...@gmail.com wrote: see if it helps On Thu, May 5, 2011 at 7:23 AM, Mr excel excelkeec...@gmail.com wrote: For example, The billing cycle for supplier A is 5th of this month to 4th next so all the transactions pertaining to Supplier A will be billed in this period. Here in this example, there is only one transaction related to A and the billing is done for the month of jan'2011 so in the month column Jan'2011 should be displayed as this single transaction is done between 5th of Jan'2011-4th Feb'2011 Likewise for all the other suppliers… Hope I had made it clear. On Wed, May 4, 2011 at 9:57 PM, ashish koul koul.ash...@gmail.com wrote: can you expaln with some example in workbook On Tue, May 3, 2011 at 8:30 PM, keechak excelkeec...@gmail.com wrote: Hi forum, Thanks very much for creating such a wonderful forum. I had a problem in calculating the due dates for different customers. Col A contains suppliers with the dates they supply the raw material amount of the material supplied in the next columns B C Respectively. I need a formula or a vba routine which can calculate the due dates for all the suppliers who supply the material. There are certain cut-off dates for each supplier in calculating the due dates in the billing cycle table. I want to print the months in Column D basing upon the supply date for each supplier and the cut-off dates in the right side table(Billing Cycle -table). Please help me WebRep Overall rating -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel WebRep Overall rating --
