Re: $$Excel-Macros$$ Help Required (Excel Function or VBA Code)
Hi, Use this formula =LEN(A1)-LEN(SUBSTITUTE(A1, ,)) where A1 contains 9 7 5 6 On Tue, Jul 26, 2011 at 9:36 AM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote: Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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
Re: $$Excel-Macros$$ Restrict access to certain sheets
Hi You can use this code on workbook open event. 'User Level 1 is admin usage 'User Level 0 is general usage if a user logs in using level 1 he can see sheet4 for other users sheet4 will be hidden Sub test() If userlevel = 1 Then ThisWorkbook.Worksheets(Sheet4).Visible = xlVeryHidden Else ThisWorkbook.Worksheets(Sheet4).Visible = True End If End Sub On Tue, Jul 26, 2011 at 9:16 AM, XLS S xlst...@gmail.com wrote: Hey, Try this run this macro and put the password in vba code Sub Macro1() ActiveWorkbook.Unprotect Password:=xxx Sheets(*Sheet1*).Visible = False ActiveWorkbook.Protect Structure:=True, Password:=xxx End Sub On Tue, Jul 26, 2011 at 8:44 AM, mahamadou lawali malaw...@gmail.comwrote: I have a workbook with 3 sheets 2 of the sheets are for general use but one of the sheets i would like people to access only by a password is this possible? i can protect the sheet but i would like only certain people to view the information? protecting stops people from amending/entering data. -- -- 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 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
Re: $$Excel-Macros$$ Help with find date.
Try using Cdate(Mydate) in Find On Tue, Jul 26, 2011 at 9:44 AM, Tom tcli...@gmail.com wrote: I want the macro below to accept any date that I enter in its input box, e.g. 17/06/2011, and go and find it in the active worksheet. I got an error message saying, Object variable or With block variable not set. Can anyone help me? Thanks. Sub FindDate() Dim myDate As Date ' possibly incorrect myDate = Application.InputBox(What date are you looking for?, Type:=1) Cells.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub Tom -- -- 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
Re: $$Excel-Macros$$ How to remove Save As opiton from excel.
Hi Pls find the code. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then Cancel = True End If End Sub This will work only if the workbook has been saved once. On Fri, Jul 22, 2011 at 12:07 AM, Anshul anshula...@gmail.com wrote: Please let me know how i can remove save as option from excel file...I have restrict persons not to save as anexcel file. -- -- 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
Re: $$Excel-Macros$$ Convert HTML file to Excel
Hi Shree, Apologies for the delay .. Pls let me know what kind of HTML file would you like to convert ? Could you pls send me those html files ? btw You can also use third party tools what Ankur has mentioned in his mail. On Mon, Jul 11, 2011 at 5:11 PM, SHREE chidurala.sh...@gmail.com wrote: Thanks Vasant Its working fine, Please can you also advise coding for only converting HTML file into Excel... On Jul 8, 2:16 pm, SHREE chidurala.sh...@gmail.com wrote: Thanks Vasant Its working fine, Please can you also advise coding for only converting HTML file into Excel... On Jul 6, 8:57 pm, Vasant vasant...@gmail.com wrote: Hi Pls find the code and the file attached. The html files stored in the folder 'Folder' will be used for the report. Sub Test() Dim Fs As New FileSystemObject, Fl As File Dim Fld As Folder, FolderPath As String Dim WkBk As Workbook, DtWkBk As Workbook Set WkBk = Workbooks.Add NoShts = WkBk.Worksheets.Count WkBk.SaveAs Filename:=Report FolderPath = ThisWorkbook.Worksheets(Sheet1).Range(B1) Application.DisplayAlerts = False Set Fld = Fs.GetFolder(FolderPath) Cn = 1 For Each fls In Fld.Files If fls.Type = HTML Document Then Debug.Print fls.Type Workbooks.Open Filename:=fls.Name ActiveSheet.Cells.Copy If Cn = NoShts Then WkBk.Worksheets(Cn).Activate ActiveSheet.Paste ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4) Cn = Cn + 1 Else WkBk.Worksheets.Add Cn = Cn + 1 WkBk.Worksheets(Cn).Activate ActiveSheet.Paste ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4) End If End If Next Application.DisplayAlerts = True End Sub On Wed, Jul 6, 2011 at 4:59 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://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: vasantjobhttp://facebook.com/vasantjob Code.xlsm 23KViewDownload- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- -- 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
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
: 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
Re: $$Excel-Macros$$ Maill Merge in Two Excel Sheet
Hi Ankur, Glad to help you. Pls send me your file...let me try :) On Wed, Jul 6, 2011 at 12:34 PM, ankur ankurpande...@gmail.com wrote: hi vasant thnks for such nice code i have similar problem also...but i need some modification this code produce many workbook...cant it be possible to create a single workbook with different sheets for each employee ? another query i have asked one query earlier about data validation requirement, can u help me in that also im just giving my requirement.plz help me i have a workbook for calculating income tax of employees with many sheets,different sheets are having monthwise data of employeesone sheet for their saving particularsone main sheet in which all the calculation are done...and their net tax is calculated by getting data from the sheetslike form 16 i used data valiation in the to get details of each employee in the main sheet( by linking with vlookup and other formulas) i need one report which show net income tax of each employee in one sheet...like employee name---income tax 25415 5486 for that i have to manually change the data validation list from drop down...the income tax which came , i copy and paste in other sheet.i have to do this for all the employees.this take lot of time i want just by one click i got all the income tax data of all the employees whose name is in data validation listin another sheet. can u help me...? 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 11:45 AM, Vasant vasant...@gmail.com wrote: Hi Jai, Pls find the below code and the file attached. The code here creates a new workbook for each employee with their details and saves them with their name and code. Sub Test() Dim NwWkBk As Workbook Lrow = ThisWorkbook.Worksheets(2).Range(A65536).End(xlUp).Row For x = 2 To Lrow Set NwWkBk = Workbooks.Add ThisWorkbook.Worksheets(1).Cells.Copy NwWkBk.Worksheets(1).Activate ActiveSheet.Paste ActiveSheet.Range(A1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(A1).ColumnWidth ActiveSheet.Range(B1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(B1).ColumnWidth ActiveSheet.Range(C1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(C1).ColumnWidth ActiveSheet.Range(D1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(D1).ColumnWidth NwWkBk.Worksheets(1).Range(B6) = ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) NwWkBk.Worksheets(1).Range(B7) = ThisWorkbook.Worksheets(2).Range(C Trim(Str(x))) ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) NwWkBk.SaveAs ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) ( ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) ) Next x End Sub On Wed, Jul 6, 2011 at 10:45 AM, Jai jaihumtu...@gmail.com wrote: 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 -- Regards Vasant skype Id: vasantjob vasant...@gmail.com -- -- 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
Re: $$Excel-Macros$$ Maill Merge in Two Excel Sheet
Hi Ankur, Glad to help you. Pls send me your file...let me try :) On Wed, Jul 6, 2011 at 12:34 PM, ankur ankurpande...@gmail.com wrote: hi vasant thnks for such nice code i have similar problem also...but i need some modification this code produce many workbook...cant it be possible to create a single workbook with different sheets for each employee ? another query i have asked one query earlier about data validation requirement, can u help me in that also im just giving my requirement.plz help me i have a workbook for calculating income tax of employees with many sheets,different sheets are having monthwise data of employeesone sheet for their saving particularsone main sheet in which all the calculation are done...and their net tax is calculated by getting data from the sheetslike form 16 i used data valiation in the to get details of each employee in the main sheet( by linking with vlookup and other formulas) i need one report which show net income tax of each employee in one sheet...like employee name---income tax 25415 5486 for that i have to manually change the data validation list from drop down...the income tax which came , i copy and paste in other sheet.i have to do this for all the employees.this take lot of time i want just by one click i got all the income tax data of all the employees whose name is in data validation listin another sheet. can u help me...? 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 11:45 AM, Vasant vasant...@gmail.com wrote: Hi Jai, Pls find the below code and the file attached. The code here creates a new workbook for each employee with their details and saves them with their name and code. Sub Test() Dim NwWkBk As Workbook Lrow = ThisWorkbook.Worksheets(2).Range(A65536).End(xlUp).Row For x = 2 To Lrow Set NwWkBk = Workbooks.Add ThisWorkbook.Worksheets(1).Cells.Copy NwWkBk.Worksheets(1).Activate ActiveSheet.Paste ActiveSheet.Range(A1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(A1).ColumnWidth ActiveSheet.Range(B1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(B1).ColumnWidth ActiveSheet.Range(C1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(C1).ColumnWidth ActiveSheet.Range(D1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(D1).ColumnWidth NwWkBk.Worksheets(1).Range(B6) = ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) NwWkBk.Worksheets(1).Range(B7) = ThisWorkbook.Worksheets(2).Range(C Trim(Str(x))) ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) NwWkBk.SaveAs ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) ( ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) ) Next x End Sub On Wed, Jul 6, 2011 at 10:45 AM, Jai jaihumtu...@gmail.com wrote: 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 -- Regards Vasant skype Id: vasantjob vasant...@gmail.com -- -- 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
Re: $$Excel-Macros$$ Maill Merge in Two Excel Sheet
Hi Ankur, Not sure, whether i understood your query completly... Pls find this code This code takes the staff no. from Sheet2 and puts in A2 in the 'gents report sheet. It then picks up the result from C2 cell of the same sheet and puts into the income tax column for the corresponding staff code. Sub Test() Lrow = ThisWorkbook.Worksheets(Sheet2).Range(A65536).End(xlUp).Row For x = 2 To Lrow EmpCode = ThisWorkbook.Worksheets(Sheet2).Cells(x, 1) ThisWorkbook.Worksheets(gents report).Range(A2) = EmpCode It = ThisWorkbook.Worksheets(gents report).Range(C2) ThisWorkbook.Worksheets(Sheet2).Cells(x, 3) = It Next x End Sub On Wed, Jul 6, 2011 at 1:20 PM, ankur ankurpande...@gmail.com wrote: HI VASANT PLZ FIND THE ATTACHED FILE this is sample file...full data can't be attached becoz of file size. 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 12:49 PM, Vasant vasant...@gmail.com wrote: Hi Ankur, Glad to help you. Pls send me your file...let me try :) On Wed, Jul 6, 2011 at 12:34 PM, ankur ankurpande...@gmail.com wrote: hi vasant thnks for such nice code i have similar problem also...but i need some modification this code produce many workbook...cant it be possible to create a single workbook with different sheets for each employee ? another query i have asked one query earlier about data validation requirement, can u help me in that also im just giving my requirement.plz help me i have a workbook for calculating income tax of employees with many sheets,different sheets are having monthwise data of employeesone sheet for their saving particularsone main sheet in which all the calculation are done...and their net tax is calculated by getting data from the sheetslike form 16 i used data valiation in the to get details of each employee in the main sheet( by linking with vlookup and other formulas) i need one report which show net income tax of each employee in one sheet...like employee name---income tax 25415 5486 for that i have to manually change the data validation list from drop down...the income tax which came , i copy and paste in other sheet.i have to do this for all the employees.this take lot of time i want just by one click i got all the income tax data of all the employees whose name is in data validation listin another sheet. can u help me...? 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 11:45 AM, Vasant vasant...@gmail.com wrote: Hi Jai, Pls find the below code and the file attached. The code here creates a new workbook for each employee with their details and saves them with their name and code. Sub Test() Dim NwWkBk As Workbook Lrow = ThisWorkbook.Worksheets(2).Range(A65536).End(xlUp).Row For x = 2 To Lrow Set NwWkBk = Workbooks.Add ThisWorkbook.Worksheets(1).Cells.Copy NwWkBk.Worksheets(1).Activate ActiveSheet.Paste ActiveSheet.Range(A1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(A1).ColumnWidth ActiveSheet.Range(B1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(B1).ColumnWidth ActiveSheet.Range(C1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(C1).ColumnWidth ActiveSheet.Range(D1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(D1).ColumnWidth NwWkBk.Worksheets(1).Range(B6) = ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) NwWkBk.Worksheets(1).Range(B7) = ThisWorkbook.Worksheets(2).Range(C Trim(Str(x))) ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) NwWkBk.SaveAs ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) ( ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) ) Next x End Sub On Wed, Jul 6, 2011 at 10:45 AM, Jai jaihumtu...@gmail.com wrote: 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 -- Regards Vasant skype Id: vasantjob vasant...@gmail.com
Re: $$Excel-Macros$$ Sum based on conditional formatting
use conditional format with formula =indirect(B row()) pls find file attached. cells containg amounts out of those some cells are coloured. Is there any way to coloured only those coloured cells. One way i have by filter on the basis of colour but everytime to use that is hectic so can we use any formula on that. I am attaching herewith that sheet for ur reference. 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 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 CONDITIONAL_FORMATING.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Maill Merge in Two Excel Sheet
The macro is functioning as per my expectation. Please set the calculation to automatic in your workbook if you hv not done so. R u getting any error. If so what is the error. Goto into the debug mode and check if the values in empcode is changing. On Wed, Jul 6, 2011 at 5:16 PM, ankur ankurpande...@gmail.com wrote: HI VASANT THNKS FOR UR EFFORTS EmpCode = ThisWorkbook.Worksheets(Sheet2).Cells(x, 1)..this code is not working U R GETTING IN RIGHT DIRECTION, NOW I NEED... THE STAFF NO OF NEXT EMPLOYEE GET CHANGED AND THE RESULT GET PASTED IN SHEET2MEANS AUTO CHANGING OF DATA VALIDATION HAPPENS...AND RESULT GET PASTED IN THE SHEETS thnks 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 3:00 PM, Vasant vasant...@gmail.com wrote: Hi Ankur, Not sure, whether i understood your query completly... Pls find this code This code takes the staff no. from Sheet2 and puts in A2 in the 'gents report sheet. It then picks up the result from C2 cell of the same sheet and puts into the income tax column for the corresponding staff code. Sub Test() Lrow = ThisWorkbook.Worksheets(Sheet2).Range(A65536).End(xlUp).Row For x = 2 To Lrow EmpCode = ThisWorkbook.Worksheets(Sheet2).Cells(x, 1) ThisWorkbook.Worksheets(gents report).Range(A2) = EmpCode It = ThisWorkbook.Worksheets(gents report).Range(C2) ThisWorkbook.Worksheets(Sheet2).Cells(x, 3) = It Next x End Sub On Wed, Jul 6, 2011 at 1:20 PM, ankur ankurpande...@gmail.com wrote: HI VASANT PLZ FIND THE ATTACHED FILE this is sample file...full data can't be attached becoz of file size. 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 12:49 PM, Vasant vasant...@gmail.com wrote: Hi Ankur, Glad to help you. Pls send me your file...let me try :) On Wed, Jul 6, 2011 at 12:34 PM, ankur ankurpande...@gmail.com wrote: hi vasant thnks for such nice code i have similar problem also...but i need some modification this code produce many workbook...cant it be possible to create a single workbook with different sheets for each employee ? another query i have asked one query earlier about data validation requirement, can u help me in that also im just giving my requirement.plz help me i have a workbook for calculating income tax of employees with many sheets,different sheets are having monthwise data of employeesone sheet for their saving particularsone main sheet in which all the calculation are done...and their net tax is calculated by getting data from the sheetslike form 16 i used data valiation in the to get details of each employee in the main sheet( by linking with vlookup and other formulas) i need one report which show net income tax of each employee in one sheet...like employee name---income tax 25415 5486 for that i have to manually change the data validation list from drop down...the income tax which came , i copy and paste in other sheet.i have to do this for all the employees.this take lot of time i want just by one click i got all the income tax data of all the employees whose name is in data validation listin another sheet. can u help me...? 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 11:45 AM, Vasant vasant...@gmail.com wrote: Hi Jai, Pls find the below code and the file attached. The code here creates a new workbook for each employee with their details and saves them with their name and code. Sub Test() Dim NwWkBk As Workbook Lrow = ThisWorkbook.Worksheets(2).Range(A65536).End(xlUp).Row For x = 2 To Lrow Set NwWkBk = Workbooks.Add ThisWorkbook.Worksheets(1).Cells.Copy NwWkBk.Worksheets(1).Activate ActiveSheet.Paste ActiveSheet.Range(A1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(A1).ColumnWidth ActiveSheet.Range(B1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(B1).ColumnWidth ActiveSheet.Range(C1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(C1).ColumnWidth ActiveSheet.Range(D1).EntireColumn.ColumnWidth = ThisWorkbook.Worksheets(1).Range(D1).ColumnWidth NwWkBk.Worksheets(1).Range(B6) = ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) NwWkBk.Worksheets(1).Range(B7) = ThisWorkbook.Worksheets(2).Range(C Trim(Str(x))) ThisWorkbook.Worksheets(2).Range(A Trim(Str(x))) NwWkBk.SaveAs ThisWorkbook.Worksheets(2).Range(B Trim(Str(x))) ( ThisWorkbook.Worksheets(2).Range(A Trim
$$Excel-Macros$$ Fwd: Openings for VBA dev in Gurgaon
Hi Group, Pls find a job opening for VBA in Gurgaon. *Dear Vasant, *Hi, Trust you are doing good. This is Harmeet here from DynPro India Pvt. Ltd. About DynPro India Pvt. Ltd.: Established in 1996, DynPro Inc was formed by a management team with unrivalled experience in IT recruitment, E-Commerce Project Deployment and SAP consulting background. DynPro India Pvt Ltd. is a arm of DynPro Inc situated in US North Carolina near the famous Research Triangle Park area and has offices in UK, Sweden and Australia. We are the major software service partner for leading IT MNC. We have around 500 consultants working for us across the country with this client. In INDIA we have four office in Bangalore, Delhi, Pune, Kolkata Chennai. You can know more about us through our websites at www.dynpro.com / www.dynproindia.com . We have very good reputation with this client to provide consultants on time from 15 yrs to fulfil their projects, Here is an opportunity to consultants to hire directly by client depend on their performance in project. VBA Developer Experience: 3+yrs( Relevant exp must be 2.5+ yrs) Education: Any Job Location: Gurgaon (Our client Place-CMM Level 5- International MNC) Job Type: Permanent with DynPro Inc Salary: Excellent package (Salary is not a constrain) If you are interested please send me your updated word format resume along with the filled following table ASAP. Full Name : Contact No : Alternative No: ( Mandatory) Current Location : E-mail Id : Current CTC : Expected CTC : Notice Period (Max of 15 days) : DOB : If you want to help your friends to get the job in our organization, Please forward this mail to them Note: If you have already received the mail OR If I agitate you, please accept my Apologies and ignore this mail With Regards, Harmeet Kaur Arora DynPro India Pvt Ltd. 101, 1st Floor, The Estate, #121, Dickenson Road, Bengaluru, PIN - 560042. Tel: 080-30795014 www.dynpro.com / www.dynproindia.com harmee...@dynproindia.com -- -- 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
Re: $$Excel-Macros$$ Convert HTML file to Excel
Hi Pls find the code and the file attached. The html files stored in the folder 'Folder' will be used for the report. Sub Test() Dim Fs As New FileSystemObject, Fl As File Dim Fld As Folder, FolderPath As String Dim WkBk As Workbook, DtWkBk As Workbook Set WkBk = Workbooks.Add NoShts = WkBk.Worksheets.Count WkBk.SaveAs Filename:=Report FolderPath = ThisWorkbook.Worksheets(Sheet1).Range(B1) Application.DisplayAlerts = False Set Fld = Fs.GetFolder(FolderPath) Cn = 1 For Each fls In Fld.Files If fls.Type = HTML Document Then Debug.Print fls.Type Workbooks.Open Filename:=fls.Name ActiveSheet.Cells.Copy If Cn = NoShts Then WkBk.Worksheets(Cn).Activate ActiveSheet.Paste ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4) Cn = Cn + 1 Else WkBk.Worksheets.Add Cn = Cn + 1 WkBk.Worksheets(Cn).Activate ActiveSheet.Paste ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4) End If End If Next Application.DisplayAlerts = True End Sub On Wed, Jul 6, 2011 at 4:59 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: 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 -- 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 Code.xlsm Description: Binary data
Re: $$Excel-Macros$$ macro for copy down
Sub Test() Dim Rng As Range Lrow = ActiveSheet.Range(A65536).End(xlUp).Row ' Last Row in Column A. Set Rng = ActiveSheet.Range(B1) ' Cell where you have the vlookup formula Rng.AutoFill Destination:=Range(B1 :B Trim(Str(Lrow))), Type:=xlFillDefault End Sub On Tue, Jul 5, 2011 at 2:58 PM, Rash rashmiv.ni...@gmail.com wrote: Hi All, I have excel file with 12000+ rows, and 10 column where i have used Vlookup and sumif function in the second row, i need a macro which copy down the function till the last row with min. time frame. Appreciate your support in advance. Regards Rash -- -- 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 vasant...@gmail.com -- -- 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: and the Microsoft MVP award goes to Ashish Koul :)
Hey Ashish, Congrats!, You deserve it On Tue, Jul 5, 2011 at 3:47 AM, bpascal123 bpascal...@googlemail.com wrote: Ayush, I wish you well. Congrats to Ashish! Pascal -- -- 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 vasant...@gmail.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to use Event Change to Change Color of Row
try using conditional formatting. On Sun, Jul 3, 201r1 at 5:45 PM, Ahmed galal ahmed.ga...@live.com wrote: Hi all, I need to know how to use Event Change to Change Color of Row according to changing in specified Column cells. for example: I have column S any cell in it take code A,B,C,D,R W. I need when any cell change in the column S taken one of the previous 6 codes, then the Row of it change to specified color A,B = Grey Color C,D = Yellow color R = Reed Color W = Green Color Best regards, *Ahmed galal Mohamed* Procurement Engineer Head Office : SQUARE Engineering Firm Tel :(202) 2402 8846 Fax :(202) 2405 0476 Mobile :(010) 9 62 60 61 Website : http://www.square.com.eg 31 Lebanon St. Mohandsen, Giza, Egypt -- -- 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 vasant...@gmail.com -- -- 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 FILE CONTAINING IP ADDRESS
Hi Modi,
Pls find the UDF for retreiving IP Address of your machine.
link
http://stackoverflow.com/questions/828496/how-to-retrieve-this-computers-ip-address
Function GetIPAddress()
Const strComputer As String = . ' Computer name. Dot means local computer
Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i
Dim strIPAddress As String ' Connect to the WMI service
Set objWMIService = GetObject(winmgmts: _
{impersonationLevel=impersonate}!\\ strComputer \root\cimv2)
' Get all TCP/IP-enabled network adapters
Set IPConfigSet = objWMIService.ExecQuery _
(Select * from Win32_NetworkAdapterConfiguration Where
IPEnabled=TRUE) ' Get all IP addresses associated with these
adapters
For Each IPConfig In IPConfigSet
IPAddress = IPConfig.IPAddress
If Not IsNull(IPAddress) Then
strIPAddress = strIPAddress Join(IPAddress, , )
End If
Next
GetIPAddress = strIPAddress
End Function
On Fri, Jul 1, 2011 at 2:24 PM, rf1234 rf1234 rfhyd1...@gmail.com wrote:
On 7/1/11, L.K. Modi ca.mod...@gmail.com wrote:
Dear Group Members
I need an excel file in wihch by running macro we can see our IP ADDRESS.
Please provide me that file by which we can know the IP address of our
System
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,
Prashant Tripathi
Engineer-SW
Mobile: 0017202597567
Please consider the environment before printing.
Immer zielen auf die vollkommene Harmonie des
Denkens Wort deed.Always zielen darauf ab,
reinigen Sie Ihre Meinung und alles wird gut.
Always aim at complete harmony of thought
word deed.Always aim at purifying your
thoughts everything will be well.
--
--
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
vasant...@gmail.com
--
--
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$$ work books name in a folder
Check this link http://www.ozgrid.com/forum/showthread.php?t=66389page=1 On Thu, Jun 30, 2011 at 1:40 PM, L.K. Modi ca.mod...@gmail.com wrote: Dear GroupMembers, I want to list the file name that are in closed folder .Means any way by which i can get the files or workboooks name that are in a particular folder. 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 Vasant skype Id: vasantjob vasant...@gmail.com -- -- 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$$ Stuck on very simple problem - VBA for Excel
Hi Use len(cCellValue) to get the characters count. As for the dates, excel stores internally as the number of days elapsed since 1-Jan-1900 for eg. 24-Oct-2010 is stored internally as 40475 days, the len value is 5. Hope this helps On Wed, Jun 29, 2011 at 1:33 AM, Jon Kanas ka...@qadas.com wrote: I have a macro which works down through all the cells in a column, parsing the contents of the cell for a particular text string. Here's the general outline: sCellValue = ActiveCell.Value CharCount = sCellValue.Characters.Count Found = 0 For i = 1 To CharCount If the cell contains a date, I get an error on the Characters.Count although the variabls sCellValue is correct. My guess was that the cell with the date wasn't text, so Characters.Count won't work. I tried using the istext function, but cannot figure out the syntax to test it for true or false. I guess I haven't ever used any of the boolean functions in VBA. Any / all suggestions appreciated. Regards, Jon -- -- 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 vasant...@gmail.com -- -- 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: Stuck on very simple problem - VBA for Excel
Hi Use len(cCellValue) to get the characters count. As for the dates, excel stores internally as the number of days elapsed since 1-Jan-1900 for eg. 24-Oct-2010 is stored internally as 40475 days, the len value is 5. Hope this helps On Wed, Jun 29, 2011 at 8:28 AM, GoldenLance samde...@gmail.com wrote: sCellValue = ActiveCell.Text On Jun 29, 1:03 am, Jon Kanas ka...@qadas.com wrote: I have a macro which works down through all the cells in a column, parsing the contents of the cell for a particular text string. Here's the general outline: sCellValue = ActiveCell.Value CharCount = sCellValue.Characters.Count Found = 0 For i = 1 To CharCount If the cell contains a date, I get an error on the Characters.Count although the variabls sCellValue is correct. My guess was that the cell with the date wasn't text, so Characters.Count won't work. I tried using the istext function, but cannot figure out the syntax to test it for true or false. I guess I haven't ever used any of the boolean functions in VBA. Any / all suggestions appreciated. Regards, Jon -- -- 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 vasant...@gmail.com -- -- 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$$ Send individual complete details through email to respective client
Hi, Pls. refer the url mentioned below l for sending mails through outlook. A security overide application for outlook is also available in the net which will help you in sending mail without manual intervention. I have been successfuly using this for some time. http://www.dicks-clicks.com/excel/olSending.htm Modify your code according to your requirements. On Tue, Jun 28, 2011 at 9:19 PM, Rajat Kapoor rajat.bi...@gmail.com wrote: There is a sheet with name Sheet1 and it contains the following data: A1 contains name, B1 Product, C1 Amount, D1 Email A1 B1 C1 D1 Name Product Amount Email Raj Shoes 1000 a...@a.com Sun Socks 800 b...@b.com Raj Socks 700 a...@a.com Sun Ball 500 b...@b.com Raj Tennis 3000 a...@a.com Now a macro should be created in such a way that email should be send automatically to both Raj Sun with details shown below Raj EMAIL DETAILS Name Product Amount Raj Shoes 1000 Raj Socks 700 Raj Tennis 3000 Total 4700 Sun Email Details Name Product Amount Sun Socks 800 Sun Ball 500 TOTAl 1300 Please keep in mind such a macro would be used to send emails to more than 1000 people. So please provide a error proof fully automatically 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 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 vasant...@gmail.com -- -- 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$$ Extract a comment and paste in adjacent cell
Try this. Sub Test() Dim Rng as range Set Rng = ActiveSheet.Range(A1) Debug.Print Rng.Comment.Text End sub if you want to use it as a UDF Function GetComment(Rng as range) GetComment= Rng.Comment.Text End sub On Wed, Jun 29, 2011 at 9:31 AM, Heather galo...@comcast.net wrote: I would like to extract comments and paste it next to the cell. For example: The comment in cell A1 would be pasted in B1 The comment in cell A2 would be pasted in B2 until the bottom row of the data. If this possible or does the fact that comments are objects prohibit this? Any help is appreciated - I am trying to make sense of data where comments were overused! Thanks! Heather -- -- 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 vasant...@gmail.com -- -- 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 req.
use this code Sub InsertRows() Dim WkSht As Worksheet Dim Rng As Range Set WkSht = ThisWorkbook.Worksheets(Sheet1) For x = 2 To WkSht.Range(A65536).End(xlUp).Row If WkSht.Range(A Trim(Str(x))) Then Application.StatusBar = Inserting Rows on Row : Trim(Str(x + 1)) WkSht.Range(A Trim(Str(x + 1))).EntireRow.Insert WkSht.Range(A Trim(Str(x + 1))).EntireRow.Insert End If Next x End Sub On Wed, Jun 29, 2011 at 4:59 PM, Rakesh Sharma rksharma...@gmail.com wrote: Dear all, please help thanks in adv. -- Regards Rakesh Sharma -- -- 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 vasant...@gmail.com -- -- 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$$ Solution required for preparing Schedule
try this. use this UDF for the type columns. Pass the Previous Type value into the function Public Function CalculateType(Rng As Range) If Rng.Value = 300 Or Rng.Value = 550 Or Rng.Value = 800 Or Rng.Value = 1050 Or Rng.Value = 1300 Or Rng.Value = 1550 Then CalculateType = 50 ElseIf Rng.Value = 750 Then CalculateType = 250 ElseIf Rng.Value = 1000 Or Rng.Value = 2000 Or Rng.Value = 3000 Then CalculateType = Rng.Value End If End Function On Tue, Jun 28, 2011 at 11:34 AM, Subhash Yadav scy2...@gmail.com wrote: Please help me out. On Tue, Jun 21, 2011 at 10:09 AM, Subhash Yadav scy2...@gmail.com wrote: Dear Experts, Please refer my attached file for which i want solution. My queries are: 1. In the type column - Value is previous type+50. but if the values are 300,550,800,1050,1300,1550 etc this start again from 50. 750 should be 250. 1000, 2000, 3000 should remain same. 2. Which ever cell is blank in the Carried out HMR / KMR the adjacent (earlier) two cell data should come in next months MS-1 Type and Due HMR / KMR against every asset code. Thank in advance to you. -- Subhash Chand Yadav -- -- 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 -- Subhash Chand Yadav -- -- 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 vasant...@gmail.com -- -- 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$$ Solution required for preparing Schedule
Sorry...correction. use this. Public Function CalculateType(Rng As Range) If Rng.Value = 300 Or Rng.Value = 550 Or Rng.Value = 800 Or Rng.Value = 1050 Or Rng.Value = 1300 Or Rng.Value = 1550 Then CalculateType = 50 ElseIf Rng.Value = 750 Then CalculateType = 250 ElseIf Rng.Value = 1000 Or Rng.Value = 2000 Or Rng.Value = 3000 Then CalculateType = Rng.Value Else CalculateType = Rng.Value + 50 End If End Function On Tue, Jun 28, 2011 at 2:01 PM, Vasant vasant...@gmail.com wrote: try this. use this UDF for the type columns. Pass the Previous Type value into the function Public Function CalculateType(Rng As Range) If Rng.Value = 300 Or Rng.Value = 550 Or Rng.Value = 800 Or Rng.Value = 1050 Or Rng.Value = 1300 Or Rng.Value = 1550 Then CalculateType = 50 ElseIf Rng.Value = 750 Then CalculateType = 250 ElseIf Rng.Value = 1000 Or Rng.Value = 2000 Or Rng.Value = 3000 Then CalculateType = Rng.Value End If End Function On Tue, Jun 28, 2011 at 11:34 AM, Subhash Yadav scy2...@gmail.com wrote: Please help me out. On Tue, Jun 21, 2011 at 10:09 AM, Subhash Yadav scy2...@gmail.com wrote: Dear Experts, Please refer my attached file for which i want solution. My queries are: 1. In the type column - Value is previous type+50. but if the values are 300,550,800,1050,1300,1550 etc this start again from 50. 750 should be 250. 1000, 2000, 3000 should remain same. 2. Which ever cell is blank in the Carried out HMR / KMR the adjacent (earlier) two cell data should come in next months MS-1 Type and Due HMR / KMR against every asset code. Thank in advance to you. -- Subhash Chand Yadav -- -- 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 -- Subhash Chand Yadav -- -- 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 vasant...@gmail.com -- Regards Vasant skype Id: vasantjob vasant...@gmail.com -- -- 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 Formating a dynamic table just some cells
did u try Range(A1048576).end(xlup).row '-- to get the last populated row in column A On Mon, Jun 27, 2011 at 3:58 PM, Jorge Marques leote.w...@gmail.com wrote: Hi guys i need your expertise, i have this macro and i need your advise, i just need to format the excel like i put in the example to format the cells with line upperlines around all data and only in the cells filled in column A. this is my code so far, i know to macro it, but the format of table keeps changing, i tried to do it with range select and xldown and right but it stops on the first non empty cell and doesn´t continue!1000 thanks Sub copycash() Dim ws As Worksheet Sheets(Pivot Cash).Select Sheets(Pivot Cash).UsedRange.Copy Windows(Comparsheet.xlsx).Activate For Each ws In Worksheets If ws.UsedRange.Cells.Count 1 Then ws.Delete Next ws Application.DisplayAlerts = True Set ws = Worksheets.Add(After:=Sheets(Sheets.Count)) 'ws.Name = ActiveSheet.Name With ws ws.Range(A1).PasteSpecial Paste:=xlPasteValues End With Cells.Select Selection.NumberFormat = #,##0_ ;[Red]-#,##0 Range(A1).Select Selection.Font.Size = 14 Selection.Font.Bold = True Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range(A1).Select Cells.EntireColumn.AutoFit Application.CutCopyMode = False -- -- 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 vasant...@gmail.com -- -- 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$$ Disable specific macro's and functions
try this set sheet calculation to manual 'application.Calculation=xlCalculationManual (VBA) sheet calculates only when the user presses the Shift + F9 button. On Mon, Jun 27, 2011 at 6:08 PM, crossy75 sjc5...@yahoo.co.uk wrote: I have this bit of code in a spreadsheet - it is used with a formula to count the number of visible rows. This is useful in accounts where people hid things thus counting what you see isnt always what you get! 'Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible 'Dim Cell As Range 'Application.Volatile 'Set Vis = Nothing 'For Each Cell In Rin 'If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then 'If Vis Is Nothing Then 'Set Vis = Cell 'Else 'Set Vis = Union(Vis, Cell) 'End If 'End If 'Next Cell 'End Function 'Function COUNTIFv(Rin As Range, Condition As Variant) As Long 'Same as Excel COUNTIF worksheet function, except does not count 'cells that are hidden 'Dim A As Range 'Dim Csum As Long 'Csum = 0 'For Each A In Vis(Rin).Areas 'Csum = Csum + WorksheetFunction.CountIf(A, Condition) 'Next A 'COUNTIFv = Csum 'End Function anyway the problem is this code seems to run the whole time - thus i would like a way to have it deactivated until it comes to do do something ie press a button, and then the function is activated, the screen refreshes and i can print or do whatever. then switch it off again so the more complex macros etc can run faster (and smoother) without this getting in the way. many thanks -- -- 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 vasant...@gmail.com -- -- 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: Disable specific macro's and functions
Use a public variable like this Public Flg as boolean in the workbook open even set the variable to false Flg=False Hv a toggle button on the worksheet which will set the Flg value to True and False alternatively when you press the button. when the flg is true, function will work else it will not. add an if condition in your function. if Flg is true then '---continue with function Pls let me know if this does not work. Regards On Mon, Jun 27, 2011 at 8:26 PM, GoldenLance samde...@gmail.com wrote: Try removing Application.Volatile On Jun 27, 5:38 pm, crossy75 sjc5...@yahoo.co.uk wrote: I have this bit of code in a spreadsheet - it is used with a formula to count the number of visible rows. This is useful in accounts where people hid things thus counting what you see isnt always what you get! 'Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible 'Dim Cell As Range 'Application.Volatile 'Set Vis = Nothing 'For Each Cell In Rin 'If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then 'If Vis Is Nothing Then 'Set Vis = Cell 'Else 'Set Vis = Union(Vis, Cell) 'End If 'End If 'Next Cell 'End Function 'Function COUNTIFv(Rin As Range, Condition As Variant) As Long 'Same as Excel COUNTIF worksheet function, except does not count 'cells that are hidden 'Dim A As Range 'Dim Csum As Long 'Csum = 0 'For Each A In Vis(Rin).Areas 'Csum = Csum + WorksheetFunction.CountIf(A, Condition) 'Next A 'COUNTIFv = Csum 'End Function anyway the problem is this code seems to run the whole time - thus i would like a way to have it deactivated until it comes to do do something ie press a button, and then the function is activated, the screen refreshes and i can print or do whatever. then switch it off again so the more complex macros etc can run faster (and smoother) without this getting in the way. many thanks -- -- 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 vasant...@gmail.com -- -- 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$$ Want to excel in excel
welcome On Sun, Jun 26, 2011 at 5:32 PM, Arundatti M arundatt...@gmail.com wrote: Allow me to join -- -- 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 -- -- 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 code instr - help to understand it
Hi syntax : InStr( [start], string_being_searched, string2, [compare] ) searches for (lowercase value of in cell 'Cel' concatenated with '«') in TmpX On Mon, Jun 27, 2011 at 4:54 AM, netuser1110 netuser...@gmail.com wrote: Hi, Does anyone can explain me what this loop does and more specificly the InStr function the way it is used below : For Each Cel In Rng1 If Len(Cel) 0 Then If InStr(1, TmpX, LCase(Cel) Chr(171)) = 0 Then TmpX = TmpX LCase(Cel) Chr(171) n = n + 1: Rng2(n, nCol + 2) = Cel.Value End If End If Next Cel Thanks -- -- 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 -- -- 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$$ Private sub selection-change preventing copy paste
You can use a flag, public boolean variable which should be set to true by default, the code in worksheets should run only if if the flag is true. When the copy code is executed the flag has to be set to false which will prevent the code from execution. After the copy code is executed the flag can again be set to its default value ie true. something like this. Public Flg as boolean Flg=True Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Flg=True then Application.EnableEvents = False Columns(2).Interior.ColorIndex = 0 Cells(ActiveCell.Row, 2).Interior.Color = vbYellow Application.EnableEvents = True endif End Sub Sub Copy_April() Flg=false Sheets(1).Select Range(A1:AG100).Select Selection.Copy sheets(13).select Range(d10).Select activecell.PasteSpecial :xl paste special values flg=true end sub On Thu, Jun 23, 2011 at 2:12 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Vasant Thank you very much, excellent work. I posted the query two times before this but I din't get a proper answer on that time.but this time u did it well. I have one more problem in that file. this file is used for marking attendance of employees, I have an another page for the settlement of salary,in that page I have to copy data from data entry sheets April to March but when I tried to copy the data the code I am given in each page preventing copy paste, the code is Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Columns(2).Interior.ColorIndex = 0 Cells(ActiveCell.Row, 2).Interior.Color = vbYellow Application.EnableEvents = True End Sub Sub Copy_April() Sheets(1).Select Range(A1:AG100).Select Selection.Copy sheets(13).select Range(d10).Select activecell.PasteSpecial :xl paste special values end sub Is there any code available to pause the above code for the time being,ie when the macro for copy paste works Regards Thanks Rajesh Kainikkara On 6/22/11, Vasant vasant...@gmail.com wrote: pls try this Sub locksheet() Dim DataRng As Range, DataFilledRange As Range Dim WkSht As Worksheet Set DataRng = ThisWorkbook.Worksheets(April).Range(C3:F7) Set WkSht = ThisWorkbook.Worksheets(April) WkSht.Unprotect With WkSht.Cells .Locked = fase .FormulaHidden = False End With For Each cls In DataRng If cls.Value Then If WorksheetFunction.CountA(WkSht.Range(Cells(3, cls.Column), Cells(3 + DataRng.Rows.Count - 1, cls.Column))) DataRng.Rows.Count Then If DataFilledRange Is Nothing Then Set DataFilledRange = cls Else Set DataFilledRange = Application.Union(DataFilledRange, cls) End If End If End If Next cls With DataFilledRange .Locked = True .FormulaHidden = True End With WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Wed, Jun 22, 2011 at 7:28 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Vasant Thanks for the code, its working well I need a condition in this the columns must not be locked in case all the data entry cells filled eg; A B C D E F G H I S N NAME1 2 3 4 5 6 7 3 3 3 1 0 0 0 0 1 RAJESH X X X 2 SANUX X 3 ANILX X Here in column C D have full data so it have to be locked, but columnE don't have full data so it should remain unlocked till it finished the data entry.Row 3 have counting formula Cell A3 have the max formula. u can compare them for Range(b2).Select ActiveCell.Offset(0, 1).Select Application.ScreenUpdating = False ActiveSheet.Unprotect Password:=rajesh If ActiveCell.Text Range(a2).Text Then ActiveCell.Offset(0, 1).Select Else ActiveCell.EntireColumn.Locked = True ActiveSheet.Protect Password:=rajesh End If Application.ScreenUpdating = True The code explain my idea about locking, but I don't to know how to make a loop .Pls consider this also modify the code. Regards Rajesh Kainikkara On 6/22/11, Vasant vasant...@gmail.com wrote: pls try this this will lock the populated cells in the range C3:AA5 in sheet 'april' Sub locksheet() Dim DataRng As Range, DataFilledRange As Range Dim WkSht As Worksheet Set DataRng = ThisWorkbook.Worksheets(April).Range(C3:AA5) Set WkSht = ThisWorkbook.Worksheets(April) WkSht.Unprotect For Each cls In DataRng If cls.Value Then If DataFilledRange Is Nothing Then Set DataFilledRange = cls Else Set DataFilledRange = Application.Union(DataFilledRange, cls) End If End If Next cls With DataFilledRange .Locked = True .FormulaHidden = True End With WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Wed, Jun 22, 2011 at 3:54
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
pls try this this will lock the populated cells in the range C3:AA5 in sheet 'april' Sub locksheet() Dim DataRng As Range, DataFilledRange As Range Dim WkSht As Worksheet Set DataRng = ThisWorkbook.Worksheets(April).Range(C3:AA5) Set WkSht = ThisWorkbook.Worksheets(April) WkSht.Unprotect For Each cls In DataRng If cls.Value Then If DataFilledRange Is Nothing Then Set DataFilledRange = cls Else Set DataFilledRange = Application.Union(DataFilledRange, cls) End If End If Next cls With DataFilledRange .Locked = True .FormulaHidden = True End With WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Wed, Jun 22, 2011 at 3:54 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Experts I add a code in the work sheet for the identification of data selected, But the copy paste is not working in that sheet. How can I solve the issue,Pls check the code tell me the change required. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Columns(2).Interior.ColorIndex = 15 Cells(ActiveCell.Row, 2).Interior.Color = vbYellow Application.EnableEvents = True End Sub 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 -- Regards Vasant -- -- 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$$ Private sub selection-change preventing copy paste
pls try this Sub locksheet() Dim DataRng As Range, DataFilledRange As Range Dim WkSht As Worksheet Set DataRng = ThisWorkbook.Worksheets(April).Range(C3:F7) Set WkSht = ThisWorkbook.Worksheets(April) WkSht.Unprotect With WkSht.Cells .Locked = fase .FormulaHidden = False End With For Each cls In DataRng If cls.Value Then If WorksheetFunction.CountA(WkSht.Range(Cells(3, cls.Column), Cells(3 + DataRng.Rows.Count - 1, cls.Column))) DataRng.Rows.Count Then If DataFilledRange Is Nothing Then Set DataFilledRange = cls Else Set DataFilledRange = Application.Union(DataFilledRange, cls) End If End If End If Next cls With DataFilledRange .Locked = True .FormulaHidden = True End With WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Wed, Jun 22, 2011 at 7:28 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Vasant Thanks for the code, its working well I need a condition in this the columns must not be locked in case all the data entry cells filled eg; A B C D E F G H I S N NAME1 2 3 4 5 6 7 3 3 3 1 0 0 0 0 1 RAJESH X X X 2 SANUX X 3 ANILX X Here in column C D have full data so it have to be locked, but columnE don't have full data so it should remain unlocked till it finished the data entry.Row 3 have counting formula Cell A3 have the max formula. u can compare them for Range(b2).Select ActiveCell.Offset(0, 1).Select Application.ScreenUpdating = False ActiveSheet.Unprotect Password:=rajesh If ActiveCell.Text Range(a2).Text Then ActiveCell.Offset(0, 1).Select Else ActiveCell.EntireColumn.Locked = True ActiveSheet.Protect Password:=rajesh End If Application.ScreenUpdating = True The code explain my idea about locking, but I don't to know how to make a loop .Pls consider this also modify the code. Regards Rajesh Kainikkara On 6/22/11, Vasant vasant...@gmail.com wrote: pls try this this will lock the populated cells in the range C3:AA5 in sheet 'april' Sub locksheet() Dim DataRng As Range, DataFilledRange As Range Dim WkSht As Worksheet Set DataRng = ThisWorkbook.Worksheets(April).Range(C3:AA5) Set WkSht = ThisWorkbook.Worksheets(April) WkSht.Unprotect For Each cls In DataRng If cls.Value Then If DataFilledRange Is Nothing Then Set DataFilledRange = cls Else Set DataFilledRange = Application.Union(DataFilledRange, cls) End If End If Next cls With DataFilledRange .Locked = True .FormulaHidden = True End With WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Wed, Jun 22, 2011 at 3:54 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Experts I add a code in the work sheet for the identification of data selected, But the copy paste is not working in that sheet. How can I solve the issue,Pls check the code tell me the change required. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Columns(2).Interior.ColorIndex = 15 Cells(ActiveCell.Row, 2).Interior.Color = vbYellow Application.EnableEvents = True End Sub 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 -- Regards Vasant -- -- 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
Re: $$Excel-Macros$$ HELP NEEDED FOR FORMULA
Hi Hari, U can use this simple UDF. Public Function CommonNumbers(Rng1 As Range, Rng2 As Range) Fc = For x = 1 To Len(Rng1.Value) src = Mid(Rng1.Value, x, 1) If InStr(Fc, src) = 0 Then Fc = Fc src End If Next x For x = 1 To Len(Rng2.Value) src = Mid(Rng2.Value, x, 1) If InStr(Fc, src) = 0 Then Fc = Fc src End If Next x CommonNumbers = Fc End Function On Mon, Jun 20, 2011 at 11:03 AM, HARI NAIR hari.shrin...@gmail.com wrote: I WANT YOUR VALUE HELP TO PUT A FORMULA IN THE ATTACHED SHEET. PLEASE HELP THANKS IN ADVANCE.. HARI -- -- 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 -- -- 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 GIRISH.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Looking for codes to copy and paste same items into different sheets
Try this for copying selected values in different sheets Sub test() Set Rng = Selection For Each cls In Rng For Each wks In ThisWorkbook.Sheets If wks.Name Shop A Then wks.Range(A Trim(Str(wks.Range(A65536).End(xlUp).R ow + 1))) = cls End If Next Next End Sub On Mon, Jun 20, 2011 at 2:42 PM, John Mutesi jmut...@mtc.com.na wrote: Dear friends, See attachment for this problem. I want a code which ask you to select the product name (coffee, tea, sugar etc) in Shop A. Once selected, the product names should be copied and pasted into shops (shop B to shop E) below the heading. I thought of having a copy which uses an array to populate the items and then do the pasting from them. Kindly advice further. John -- This e-mail message and any attachment hereto are confidential information intended for a specific addressee and purpose. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you may not disclose, copy, distribute or take any action based on the contents hereof, or retaining this message or any part of it, in any form whatsoever. Any such conduct is prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately by replying to this message and destroy all copies hereof. No opinion expressed by the sender necessarily constitutes the opinion of Mobile Telecommunications Ltd (MTC). This message does not constitute a guarantee or proof of the facts mentioned herein. No employee or intermediary is authorized to conclude a binding agreement on behalf of MTC by e-mail without the express written confirmation by a duly authorized representative of MTC. All reasonable precautions have been taken to ensure a virus free message however, MTC cannot guarantee this nor accept responsibility whatsoever for loss or damage arising from the use of this e-mail or attachments. MTC reserve the right to monitor all e-mail communications transmitted through our network. -- -- 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 -- -- 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$$ small doubt help me.
use copy, and then paste special with transpose On Mon, Jun 20, 2011 at 3:46 PM, kannan excel kannan.ex...@gmail.comwrote: Hi Guru Pls help me i am waiting for your reply. regards Kannan V 9941077703 onechennaiproperty.com -- -- 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 -- -- 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$$ Looking for codes to copy and paste same items into different sheets
the code copies the selected cells in shop A worksheet to the other sheets in the workbook one below the another. u hv to select the the products in sheet shop A and run this macro. PFA . Select the products in sheet A and then run the macro test. On Mon, Jun 20, 2011 at 6:28 PM, John Mutesi jmut...@mtc.com.na wrote: Vasant, What are these codes doing? *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Vasant *Sent:* Monday, June 20, 2011 12:37 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Looking for codes to copy and paste same items into different sheets Try this for copying selected values in different sheets Sub test() Set Rng = Selection For Each cls In Rng For Each wks In ThisWorkbook.Sheets If wks.Name Shop A Then wks.Range(A Trim(Str(wks.Range(A65536).End(xlUp).R ow + 1))) = cls End If Next Next End Sub On Mon, Jun 20, 2011 at 2:42 PM, John Mutesi jmut...@mtc.com.na wrote: Dear friends, See attachment for this problem. I want a code which ask you to select the product name (coffee, tea, sugar etc) in Shop A. Once selected, the product names should be copied and pasted into shops (shop B to shop E) below the heading. I thought of having a copy which uses an array to populate the items and then do the pasting from them. Kindly advice further. John -- This e-mail message and any attachment hereto are confidential information intended for a specific addressee and purpose. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you may not disclose, copy, distribute or take any action based on the contents hereof, or retaining this message or any part of it, in any form whatsoever. Any such conduct is prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately by replying to this message and destroy all copies hereof. No opinion expressed by the sender necessarily constitutes the opinion of Mobile Telecommunications Ltd (MTC). This message does not constitute a guarantee or proof of the facts mentioned herein. No employee or intermediary is authorized to conclude a binding agreement on behalf of MTC by e-mail without the express written confirmation by a duly authorized representative of MTC. All reasonable precautions have been taken to ensure a virus free message however, MTC cannot guarantee this nor accept responsibility whatsoever for loss or damage arising from the use of this e-mail or attachments. MTC reserve the right to monitor all e-mail communications transmitted through our network. -- -- 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 -- -- 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 -- This e-mail message and any attachment hereto are confidential information intended for a specific addressee and purpose. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you may not disclose, copy, distribute or take any action based on the contents hereof, or retaining this message or any part of it, in any form whatsoever. Any such conduct is prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately by replying to this message and destroy all copies hereof. No opinion expressed by the sender necessarily constitutes the opinion of Mobile Telecommunications Ltd (MTC). This message does not constitute a guarantee or proof of the facts mentioned herein. No employee or intermediary is authorized to conclude a binding agreement on behalf of MTC by e-mail without the express written confirmation by a duly
Re: $$Excel-Macros$$ querry 4
your formula will come in this form =IF(AND(E5=60,D5=60),D3+D4+1-60,) On Mon, Jun 20, 2011 at 6:15 PM, Nemi Gandhi nemigan...@gmail.com wrote: please see the attached file. Please help. -- Nemi Gandhi 98204 92963 -- -- 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 -- -- 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$$ Dynamic file name and sum
There are many approaches of doing it.
I have listed two here..
Pass the month and year as parameters to the - 'UpdateFormula' subroutine.
You can pass the parameters either through a worksheet change event or a
userform.
'Trigger on worksheet change event in Memo sheet (all the workbooks has to
be opened)
'--
Private Sub Worksheet_Change(ByVal Target As Range)
If ThisWorkbook.Worksheets(Memo).Range(J2) And
ThisWorkbook.Worksheets(Memo).Range(J3) Then
Call UpdateFormula(ThisWorkbook.Worksheets(Memo).Range(J2),
ThisWorkbook.Worksheets(Memo).Range(J3))
End If
End Sub
Sub UpdateFormula(Mn, Yr)
Flnm = DDD_Report_ Mn _ Trim(Str(Yr)) .xls
ThisWorkbook.Worksheets(Memo).Range(F4).Formula =
=SUM(prod!J2:J9)-SUM('E:\[ Flnm ]prod'!$I$2:$I$9)
End Sub
The other is : -
this approach will work if the workbook names are standardized.
ie. DDD_REPORT_APR2011.xls, DDD_REPORT_MAR2011.xls and so on.
the below code gets the previous month file name using the current month
workbook name and then updates the formula in the cell if the previous month
file is available in the folder.
Sub UpdateFormula2()
Dim Flnm As String, Mn As String, Yr As Integer, PrevMn As String
Dim FlPath As String, WkBk As Workbook, GetPrevMnthFile As String
Flnm = ThisWorkbook.Name
Mn = Mid(Flnm, 12, 3)
Yr = Mid(Flnm, 15, 4)
PrevMn = WorksheetFunction.Choose(Month(DateAdd(m, -1, DateValue(01-
Mn - Yr))), Jan, Feb, Mar, Apr, May, Jun, Jul, Aug,
Sep, Oct, Nov, Dec)
If PrevMn = Dec Then
Yr = Yr - 1
End If
GetPrevMnthFile = DDD_REPORT_ PrevMn Trim(Str(Yr)) .xls
FlPath = ThisWorkbook.Path \ GetPrevMnthFile
If IsFileExists(FlPath) Then
ThisWorkbook.Worksheets(Memo).Range(F4).Formula =
=SUM(prod!J2:J9)-SUM(' ThisWorkbook.Path \[ GetPrevMnthFile
]prod'!$I$2:$I$9)
else
msgbox Previous Month File does not exist !
End If
End Sub
' Function to check if file exists in the folder
Function IsFileExists(Flnm
As String) As Boolean
On Error Resume Next
If Not Dir(Flnm, vbDirectory) = vbNullString Then IsFileExists = True
On Error GoTo 0
End Function
Hope this helps
Regards
Vasant
On Thu, Jun 2, 2011 at 6:55 PM, Skanda skanda.pokkun...@gmail.com wrote:
Vasant,I'm seeking help to write a macro.
On Thu, Jun 2, 2011 at 12:23 AM, Vasant vasant...@gmail.com wrote:
A crude way of doing it is using the indirect function (though not
advisable), on condition that the file name is updated in C13 cell and file
is open.
Generally Indirect is not used outside a workbook
=SUM(prod!J2:J9)-SUM(INDIRECT([C13]prod!$I$2:$I$9))
The other way round is writing a macro.
On Wed, Jun 1, 2011 at 7:00 PM, Skanda skanda.pokkun...@gmail.comwrote:
vasant everymonth we have to change the file name.i.e MAR_2011
On Wed, Jun 1, 2011 at 4:58 AM, Vasant vasant...@gmail.com wrote:
Hi Skanda,
=SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9)
Hope this helps
Regards
Vasant
On Wed, Jun 1, 2011 at 12:15 AM, Skanda skanda.pokkun...@gmail.comwrote:
There are two excel workbooks:DDD_REPORT_APR2011.xls and
DDD_REPORT_MAR2011.xls.
In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4
in memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column)
minus sum(i Column) from prod worksheet minus sum(i2:i9) of prod sheet
from DDD_REPORT_MAR2011.xls.
This is a done every month so the file names have to be dynamic.
--
--
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
--
--
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
Re: $$Excel-Macros$$ Dynamic file name and sum
A crude way of doing it is using the indirect function (though not advisable), on condition that the file name is updated in C13 cell and file is open. Generally Indirect is not used outside a workbook =SUM(prod!J2:J9)-SUM(INDIRECT([C13]prod!$I$2:$I$9)) The other way round is writing a macro. On Wed, Jun 1, 2011 at 7:00 PM, Skanda skanda.pokkun...@gmail.com wrote: vasant everymonth we have to change the file name.i.e MAR_2011 On Wed, Jun 1, 2011 at 4:58 AM, Vasant vasant...@gmail.com wrote: Hi Skanda, =SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9) Hope this helps Regards Vasant On Wed, Jun 1, 2011 at 12:15 AM, Skanda skanda.pokkun...@gmail.comwrote: There are two excel workbooks:DDD_REPORT_APR2011.xls and DDD_REPORT_MAR2011.xls. In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4 in memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column) minus sum(i Column) from prod worksheet minus sum(i2:i9) of prod sheet from DDD_REPORT_MAR2011.xls. This is a done every month so the file names have to be dynamic. -- -- 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 -- -- 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 Vasant -- -- 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: Need Matching excel formula
Use Vlookup On Sat, May 28, 2011 at 2:36 PM, GoldenLance samde...@gmail.com wrote: =IF(OR(COUNTIF($B$2:$B$14,B2)=1,B2=),TEXT(B2,#),NO) On May 28, 9:24 am, kannan excel kannan.ex...@gmail.com wrote: Hi Excel Guru, i don't want to enter same mobile number. so i need matching or tracing formula. If I entered mobile number in a column means, how do i know? i already entered this mobile number. pls find my attachment. regards kannan V onechennaiproperty.com text.xlsx 14KViewDownload -- -- 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 -- -- 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$$ I am only looking for e-mail id in different colums
You can copy the all the email ids after filtering them and then paste special and transpose them. Is this wht u r looking for ? On 9/9/10, Pankaj Kumar rajputpanka...@gmail.com wrote: Hi, Experts I need u r help, i have a excelsheet my problem is I am only looking for e-mail id in different colums ex in Colum F --- resume @alsachi.com if I do one by one copy paste it's a lot of time , is any short cut to do it -- *Warm Regards Pankaj kumar M: 9899816107 e-mail: rajputpanka...@gmail.com rajputpank...@yahoo.in* * rajputpankaj1...@rediffmail.com* -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ sending e mail based on data in column in excel
Yes, it is possible to do Regards Vasant On 8/20/10, Chandra Gupt Kumar kumar.bemlmum...@gmail.com wrote: Dear All, My requirements are as follows; 1. Based on date of birth (Col D), mail to be sent on particular day to respective office for good wishes. 2. Based on date of joining , congratulation mail to be sent if the concerned completes 5,10,15,20,25,35,40 years of service. It is possible to do so ? Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ my att
Can you be more precise what you want ? Regards Vasant 9880533187 On 8/20/10, ALAM aftab.ala...@gmail.com wrote: Dear all, Solve my poblems Note:- I want to all details in excel sheet (like *NAME IN-TIME DATE ) *JUST CLICK OF *EMP ID*. ALSO WE ENCLOSED EXCEL SHEET FOR ALL OF U -- RegardsThanks, AFTAB ALI 9310903589 -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ FOR MIS
HI Aftab, U can use filters. On 8/11/10, Mukesh Kukreja moksh@gmail.com wrote: i hope it will resolve ur problem, if not pl let me knw what exactly u want On Wed, Aug 11, 2010 at 12:50 PM, ALAM aftab.ala...@gmail.com wrote: DEAR ALL, I WANT TO MAKE A MIS REPORT OF ATTANDANCE AS PER DATED. REG I WANT TO DETAILS ACCORDING TO EMP-ID, JUST FOR SELECTED IMP-ID PLS FIND AN ENCLOSED FILES -- RegardsThanks, AFTAB ALI 9310903589 -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Thank you Dave Bonallack !!
Congratulations Dave! great help for all of us, keep contributing. Regards Vasant On 8/4/10, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Thanks you very much *Dave*.. God has given you a brilliant mind..and you always solve to group's problem Kindly Keep it in future.. Regards, Noorain On Wed, Aug 4, 2010 at 10:49 AM, Dave Bonallack davebonall...@hotmail.com wrote: Thanks Dilip. Dave. -- Date: Tue, 3 Aug 2010 20:43:16 +0530 Subject: Re: $$Excel-Macros$$ Thank you Dave Bonallack !! From: dilipan...@gmail.com To: excel-macros@googlegroups.com CC: davebonall...@hotmail.com Thanks Dave for Gearing upp...!! you are awsome.. Keep helping the GROUP...!! -- Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India On Sat, Jul 31, 2010 at 2:29 PM, Ayush jainayus...@gmail.com wrote: Dear Group, In the recent group survey conducted, I asked one question that How the top posters should be rewarded in the group ... and most of you replied that we should announce the name of top poster every month... So the time has come to announce the Top Poster of July 2010 and i.e. Our own Excel and Macros Expert DAVE BONALLACK. He is not only top poster of the month but is the top poster of the group with 461 posts. Dave, you are the asset of the group and we feel very proud for your association with this group. Your voluntary support is very helpful ( and life saver for some :) ) . We wish you all the best for your assignments and expect long association with the group. Thank you Dave :) I have published the name of monthly top poster on Home Page... Keep posting !! Regards, Ayush Jain Group Manager P.S. If you have any feedback for group, please fill the survey formhttp://www.surveymonkey.com/s/L8BTDNQ -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about
Re: $$Excel-Macros$$ how to enable macros?
HI If you are using excel 2003 Click on Tools-Macro-Security and then select Medium and close the file and open the file again. If you are using excel 2007 Click on Office Button Goto Excel Options, select Trust Center, select Trust Centre Settings, select Macro Settings, check on Enable All Macros Hope this is clear Regards Vasant On 7/30/10, Pooja S poojasoni2...@gmail.com wrote: hi there is a problem in opening an excel file with macros...wht should be done to open the excel file with macros? pls rply soon..it is urgent -- Pooja -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Create macro to reformat spreadsheet for importing
HI, Hope this helps pls run the macro ALign in the attached file. Data in Sheet1 is aligned in Sheet2. I hv copied the code for your convenience. *Sub Align() Dim Wksht As Worksheet, NWksht As Worksheet Set Wksht = ThisWorkbook.Worksheets(Sheet1) Set NWksht = ThisWorkbook.Worksheets(Sheet2) Lrow = Wksht.Cells.SpecialCells(xlCellTypeLastCell).Row Cntr = 1 For x = 1 To Lrow If Len(Wksht.Cells(x, 1)) = 4 Then NWksht.Cells(Cntr, 1) = Wksht.Cells(x, 1) NWksht.Cells(Cntr, 2) = Wksht.Cells(x + 2, 1) NWksht.Cells(Cntr, 2) = Wksht.Cells(x + 3, 1) Cntr = Cntr + 1 End If Next x End Sub* Regards Vasant On Fri, Jul 30, 2010 at 7:56 PM, Ecovindaloo vindal...@gmail.com wrote: I need to create macro to reformat spreadsheet for importing into Access. As usual the spreadsheet is not formatted properly. Here is a piece of the spreadsheet: 3196 28320221EBLADENST 39294812 275813701MABLELANE 40189085 3200 601771294ANGELINEDRIVE 40240796 60136321JOHNMBOORDR 39278689 3207 271062990CARRIAGEDRRAMSGAT 39345882 271065981OLDPLANKRD 39456725 What I need to do is create a macro to add a column and then take the ID Number (i.e. 3169) and put it in those rows below. Then delete the row with the ID Number and blank rows. Is there any easy way to do this in a macro? Thanks in advance for the help. -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Book1.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Fwd: Help Needed_Very Urgent
Hi, The Recd Date and Completion date is differs for each employee. So you mean to say that date range has to be generated for each employee. Could you suggest the format in which you want this to be done ? Regard Vasant Hi Vasant, This works great. Thanks for your help. Need a small change on this. The attached file will have dates already presented in sheet Dummy. Now what I want to have the dates automatically populated when macro running. The macro should check the minimum date in column E(Recv Date) and maximum date in column F(Compleation Date). Then it should populate the dates from min date to max date automatically. As and when I run the macro it should populate the dates based on minimum and maximum automatically. Note: if min date is 09/07/2010 and max date is 12/08/2010 then the macro should populate dates from cell B2-09/07/2010, B3-10/07/2010, B4-11/07/201012/08/2010. till the last date. Thanks for your time, Looking for your help to proceed further. Thanks again, Sharma On Tue, Jul 27, 2010 at 9:39 AM, Vasant vasant...@gmail.com wrote: Hope this is works... Regards Vasant On Tue, Jul 27, 2010 at 4:22 PM, amrahs k amrahs...@gmail.com wrote: Hi Dilip, I have attached the sample file with my query. Please look into this and help me to proceed further since i have left with only one working day. Looking for your help, Thanks in Advance, Sharma -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Need help
Do u want the data in ms-word as a paragraph ? On 7/28/10, madha...@yahoo.com madha...@yahoo.com wrote: Dear expert I have some data stored in excel sheet and I wanted to convert the same in paragraph form can I know how can I convert the same in paragraph form. Warm regards Madhav Vyas Sent on my BlackBerry® from Vodafone -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ How to see the ascii code of a text withing a field
use code() function in the worksheet or use the following function to get the ascii value of each character seperated by a space Public Function GetAscii(Var) Dim Cd As String For x = 1 To Len(Var) Cd = Cd IIf(Cd = , Trim(Str(Asc(Mid(Var, x, 1, Trim(Str(Asc(Mid(Var, x, 1) Next x GetAscii = Cd End Function On 7/28/10, galsaba gals...@aol.com wrote: Is there a way to see what the ascii code is for each character in a chosen field? The reason is, I got a file from China that contain some characters that on xls they look as space, but when I do replace space by nothing this space is still there. so I assume it is not really a space character, but a character that I cannot see. Thanks galsaba -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ EXCEL-VBA Query...
Here is your code. Regards Vasant On Tue, Jul 27, 2010 at 6:31 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Experts.. We want to transfer *Total Asset* column(F) in another sheet(Database) on daily basis in current date column through *Transfer in Database* Button. Please see attached file.. -- With thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards Vasant -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Current_Stock.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
