Re: $$Excel-Macros$$ Nos. of Sundays between two days
Thank you very much for the prompt reply Sir. - Original Message - From: ashish koul To: excel-macros@googlegroups.com Sent: Monday, February 14, 2011 8:17 PM Subject: Re: $$Excel-Macros$$ Nos. of Sundays between two days start date in a1 and end date in a2 no of sundays =INT(((A2-A1)-NETWORKDAYS(A1,A2))/2)+1 On Mon, Feb 14, 2011 at 6:04 PM, N Pradhan nalini.pradha...@gmail.com wrote: Dear Sir, Formula required for nos. of sundays between two days. e.g. if an employee has joined on 18/7/2010, how many sundays between 18/7/2010 and 31/01/2011. Thanks Regards. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com akoul.wordpress.com My Linkedin Profile P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ A little OT
The biggest confusion here is the difference between the Worksheet OBJECT: Sheet1 the Worksheet NAME: Sheet1 and Sheets ARRAY: Sheets(1) An Excel workbook must contain at least one sheet. Excel will INTERNALLY Identify this sheet object as Sheet1 and will initially NAME the sheet Sheet1 But these are two different things. In the VB Editor Object Explorer, you'll see: Sheet1(Sheet1) Meaning that the Sheet1 object is NAMED Sheet1 The key here is that Sheet1 can be NAMED anything, but will always be Sheet1 as long as it exists. You can name your dog kitty but that doesn't change the fact that it's still a dog. That's why you can use: Sheet1.Name = DataSheet The Name Property of the Sheet1 Object is set to DataSheet So, even though the VB Object Explorer shows: Sheet1(DataSheet) You cannot use: DataSheet.Select Because DataSheet is not an Object... Now, the Sheets() Array... It's exactly that. It's an array of sheet objects. The interesting thing here is that the position in the array is dependent upon where the sheet appears in the workbook. If you have three sheets, Named: Name1, Name2 and Name3 and they were originally created in numerical order and positioned left-to-right. The Sheets() Array will have them listed as: Sheets(1).Name = Name1 Sheets(2).Name = Name2 Sheets(3).Name = Name3 and the VBA Object Explorer will show them as: Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) But if you move Name3 to the far left, so they're shown as: Name3, Name1, Name2 The Object Explorer will still show them as: Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) But the Sheets ARRAY will be: Sheets(1).Name = Name3 Sheets(2).Name = Name1 Sheets(3).Name = Name2 BTW: The Sheets Array also has the internal name as the CodeName object. So.. summarize... There are three distinct ways to identify a sheet (maybe more?) The Sheet OBJECT, the Sheet NAME, and the Sheets ARRAY You cannot change the codeName of an existing sheet object. You CAN (of course) change the Sheet Name, and the Sheets ARRAY is changed by the position of the sheet in the workbook. In answer to your question.. Your question contains syntax errors. Because when you say there are 7 sheets, but Sheet(7) doesn't exist. You mean that Sheet7 doesn't exist... Sheets(7) is the 7th element of the Sheets ARRAY, which DOES exist. Does that help? or was it so wordy that you fell asleep midway and woke up with a keyboard imprint on your right cheek? Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Mon, February 14, 2011 11:33:24 PM Subject: $$Excel-Macros$$ A little OT Hi Paul, I have also noticed that after deleting and creating sheets, the Sheet numbers are out of order, and can be missing altogether. ie in a workbook of 7 sheets, one of them may be Sheet(11), while sheets 8 and 9 are not present at all. So, my question is, why does this line of code (Sheets(Sheets.Count).Select) always select the last sheet in a workbook, even when the last sheet isn't necessarily the one with the highest sheet number, and also, if the sheet.count is say, 7, the thing still works if Sheet(7) doesn't exist! Regards - Dave Date: Mon, 14 Feb 2011 19:07:28 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 To: excel-macros@googlegroups.com In your VBA Object Explorer, you'll see Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) If you delete the sheet named Name2, create a new sheet, and call the NEW sheet Name2 It won't be: Sheet2(Name2), it'll be: Sheet4(Name2). So the LIST will be: Sheet1(Name1) Sheet3(Name3) Sheet4(Name4) in VBA the Sheets array is: Sheets(1).Name = Name1 Sheets(2).Name = Name3 Sheets(3).Name = Name2 Because the Sheets() array is a list of sheet names. An odd occurence though is: If you save and exit Excel, open the file, and create a new Sheet (Name4) it will create it as: Sheet2(Name4) and the array becomes: Sheets(1).Name = Name1 Sheets(2).Name = Name4 Sheets(3).Name = Name3 Sheets(4).Name = Name2 So, basically, when you see sheet1, sheet2, etc in the VBA Project Explorer, it is NOT a sheet name and canNOT be used as the array. Because of this, using sheets(1) or Sheets(2) is inconsistent, and I NEVER use it. (some of my macros delete and create several sheets during processing. It's not uncommon to be up to sheet300 in a session). What I would suggest is to use: Sheets(Name1) or set a variable for the sheet name ShtName = Name3 and use: Sheets(ShtName).Select Paul From: Nasim nbeiz...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Mon, February 14, 2011 6:02:46 PM Subject: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 Hi, we are using excel 2010. I use sheets(2) in my code and it was
Re: $$Excel-Macros$$ Load image from the url
Sub test() UserForm1.Image1.Picture = LoadPicture(D:\koul\krishna.jpg) UserForm1.Show End Sub On Tue, Feb 15, 2011 at 11:55 AM, San Pat myitems2...@gmail.com wrote: How I can load a image from a url using the LoadPicture function in userform. Regards, -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ error checking VBA Excel 2003
I have written a sub routine to add a name to a dynamic list but I have three problems with it and would be grateful for some advice. First the routine:- Sub InputNewUser() ' ' InputNewUser Macro ' Macro recorded 01/02/2011 by Brian ' Dim Newuser As String Dim nextRow As Long Dim flag As Boolean flag = False 'find out if main database or staff copy - true if main database false if email copy For Each wksheet In Application.Worksheets If wksheet.Name = HoursWorkedexpenses Then flag = True Exit For End If Next wksheet If flag = False Then MsgBox You do not have sufficient authority to create a new user End Else End If 'Ask for name of new user Newuser = Application.InputBox(Please enter name of new user (Surname first)) 'turns it into proper letters Newuser = Application.Proper(Newuser) 'Check to see if cancel button is pressed go back to Menu If Newuser = Then Sheets(Menu).Select Exit Sub End If 'Routine to check if Newuser already exists in list of users ' 'Find last row Set SrcSht = Sheets(UserList) nextRow = SrcSht.Cells(Cells.Rows.Count, A).End(xlUp).Row + 1 Application.ScreenUpdating = False Range(A nextRow).Select ActiveCell.FormulaR1C1 = Newuser Columns(A:A).Select Selection.Sort Key1:=Range(A2), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.ScreenUpdating = True 'display message to show that it has been added to userlist MsgBox (Newuser has been added to List of Users) 'return to menu page Sheets(Menu).Select End Sub The problems are as follows:- 1. I have just added the code to check to see if the cancel button is pressed but it does not exit the subroutine - it adds a false to the list of users. 2. How do I check to see if the Newuser entry already exists in the list of users 3. The routine seems to work when running it from the VBA window but when the macro is attached to a button on the Menu Wrksheet it fails to add the name to the list of users. This routine worked up until a few days ago and it really has me stumped Any help would be appreciated Brian Scotland -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Problem with Sorting in row wise
Respected Sir, I have many 500 rows in my excel sheet i want sort all row ascending order. for exp 1 row 102 103 105 110 120 2 row 103 102 110 105 120 how i convert it into 1 row 102 103 105 110 120 2 row 102 103 105 110 120 -- -- Some important links for excel users: 1. Follow us on TWITTER 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 RAVI.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Problem with Sorting in row wise
try this Sub Macro1() Dim i As Long For i = 1 To Sheets(1).Range(a1).End(xlDown).Row ActiveWorkbook.Worksheets(Sheet1).Sort.SortFields.Clear Worksheets(Sheet1).Sort.SortFields.Add Key:=Range(b i :i i), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(Sheet1).Sort .SetRange Worksheets(Sheet1).Range(b i :i i) .Header = xlGuess .MatchCase = False .Orientation = xlLeftToRight .SortMethod = xlPinYin .Apply End With Next i End Sub On Tue, Feb 15, 2011 at 7:09 PM, Excel Expert excel...@gmail.com wrote: Respected Sir, I have many 500 rows in my excel sheet i want sort all row ascending order. for exp 1 row 102 103 105 110 120 2 row 103 102 110 105 120 how i convert it into 1 row 102 103 105 110 120 2 row 102 103 105 110 120 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Query
Thanks *Ashish *for reply but can you please provide formula without pivot. Condition you have understand that if sum of any classification is grater then others,it shows that value. On Tue, Feb 15, 2011 at 8:14 AM, ashish koul koul.ash...@gmail.com wrote: On Tue, Feb 15, 2011 at 12:16 AM, Aamir Shahzad aamirshahza...@gmail.comwrote: Hi all experts, Please resolve my query by formula. sheet attached. Regards, Aamir Shahzad -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Query
will it be these three classifications only On Tue, Feb 15, 2011 at 8:35 PM, Aamir Shahzad aamirshahza...@gmail.comwrote: Thanks *Ashish *for reply but can you please provide formula without pivot. Condition you have understand that if sum of any classification is grater then others,it shows that value. On Tue, Feb 15, 2011 at 8:14 AM, ashish koul koul.ash...@gmail.comwrote: On Tue, Feb 15, 2011 at 12:16 AM, Aamir Shahzad aamirshahza...@gmail.com wrote: Hi all experts, Please resolve my query by formula. sheet attached. Regards, Aamir Shahzad -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ RASHI QUERRY
VLOOKUP(IF(VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)= 1440,VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)-1440,VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)),$A$2:$C$15,3,1) On Tue, Feb 15, 2011 at 4:43 PM, Nemi Gandhi nemigan...@gmail.com wrote: Thank you Mr. sundervalen, but i need only one formula instead of calculating value for date, month and time separately,. Is it possible? and thank you to Mr. Ashish kaul also for help. On Feb 14, 8:42 pm, Sundarvelan N nsund...@gmail.com wrote: Check if mine is ok for you Thanks, Sundarvelan, 9600160150 On Mon, Feb 14, 2011 at 8:08 PM, ashish koul koul.ash...@gmail.com wrote: see if it helps On Mon, Feb 14, 2011 at 5:15 PM, Nemi Gandhi nemigan...@gmail.com wrote: attached herewith rashi querry sheet. Please provide me formula and 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 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* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials 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 -- Thanks N.Sundarvelan 9600160150 Xl002.xls 41KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ data in horizontal row
hi please use this simple procedure Excel Text to Column Command The Text to Column command allows you to break text in one column into several columns. For example, if you have LastName, FirstName in a column, you can break the text into two columns—one containing LastName and the other containing FirstName. 1. Insert as many columns to the left of the column to be split as needed. 2. Select the cells to be separated. 3. Pull down the Data menu and choose Text to Columns 4. Choose Delimited and click on Next 5. Choose the Delimiter that is used in the column, such as comma and click on Next 6. To set the column data format, click on the column and then click on the format desired under column data format (this is optional). Repeat this for each column and click on Finish. Excel Transpose Command Use the transpose feature to switch data from vertical to horizontal. 1. Select the cells to switch 2. Click on the Copy tool 3. Click where you want the data to be transposed to 4. Pull down the Edit menu and choose Paste Special 5. Click on Transpose and click on OK Thanks, Raj On Tue, Feb 15, 2011 at 1:37 AM, Hems coolh...@gmail.com wrote: Hi Guys, Pls check attached excel sheet. I have data in across 2 columns which i want move in one row through macro, is it possible? actually the data is extracted from access database so if its poss through macro then i can process in access rather trolling it through excel. appreciate your efforts and thanks in advance. -- Kind Regards, Hems -- -- Some important links for excel users: 1. Follow us on TWITTER 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
Fwd: $$Excel-Macros$$ Solution required
Dear Excel Gurus, I know mailmerge but how to do the same thing in excel, I also know that It is very easily done using Ms-access. how to do mailmerge in excel. because we want the desired data in excel only Regards Jitender -- Forwarded message -- From: Sanjoy Nath sanjoy.nath...@gmail.com Date: 14 February 2011 12:46 Subject: Re: $$Excel-Macros$$ Solution required To: excel-macros@googlegroups.com Dear Jitender, Use the mail merge option in the word On Sat, Feb 12, 2011 at 9:38 PM, Jitender kumar jk9779771...@gmail.comwrote: Dear Excel Gurus, Urgent help required from your part, Plz help me on the file attached. Problem in file Thanks in advance Regards Jitender -- -- Some important links for excel users: 1. Follow us on TWITTER 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 -- Your Sincerely Sanjoy Nath Engineer EOL Gurgaon EOL/M/NZ/Engg.Dept.(Haryana Division) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2
Thanks for your thorough explanation Paul. I now understand that sheets() points to the position of the sheet in the file. I still do not understand how to use the sheet.name though. This is what I have in objext explorer sheet1 (Process) 'is the first sheet sheet2 (Hours) ' is 3rd sheet sheet3 (Breakdown) ' is 2nd sheet 1- Should I say : Thisworkbook.sheet2.name= whatever Thisworkbook.sheet(whatever).select 2- Doesn't this change the name that the tab is showing (showing whatever instead of hours)? If so, what if somebody renames the tabs later. Will this not confuse a user as I am replacing the sheet name to what I want? 3- Or should I simply say: Thisworkbook.sheet2.select? Does this select sheet2(hours) or sheet2(Breakdown)? I guess it will be sheet2(hours). 4- What if I need to use variable and refer to sheet2 not sheet in 2nd position? I used to used sheets(i). if i=2 then it will point to sheet3(which in 2nd position-as I learnt from you) whereas I need to point to sheet2 which is in 3rd position. what syntax should I use? 5- Here is the part that I am referencing my sheets: Dim WSB, WSH As Worksheet Dim u, t, p, q, LastRowH, LastRowB As Integer Set WSB = ThisWorkbook.Sheets(9) ' :I changed it to Set WBS = Thisworkbook.Sheet9 and it does not work Set WSH = ThisWorkbook.Sheets(2) I am still so confused. I appreciate if you could explaine this a bit more. Best regards, Nasim On Feb 14, 10:07 pm, Paul Schreiner schreiner_p...@att.net wrote: In your VBA Object Explorer, you'll see Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) If you delete the sheet named Name2, create a new sheet, and call the NEW sheet Name2 It won't be: Sheet2(Name2), it'll be: Sheet4(Name2). So the LIST will be: Sheet1(Name1) Sheet3(Name3) Sheet4(Name4) in VBA the Sheets array is: Sheets(1).Name = Name1 Sheets(2).Name = Name3 Sheets(3).Name = Name2 Because the Sheets() array is a list of sheet names. An odd occurence though is: If you save and exit Excel, open the file, and create a new Sheet (Name4) it will create it as: Sheet2(Name4) and the array becomes: Sheets(1).Name = Name1 Sheets(2).Name = Name4 Sheets(3).Name = Name3 Sheets(4).Name = Name2 So, basically, when you see sheet1, sheet2, etc in the VBA Project Explorer, it is NOT a sheet name and canNOT be used as the array. Because of this, using sheets(1) or Sheets(2) is inconsistent, and I NEVER use it. (some of my macros delete and create several sheets during processing. It's not uncommon to be up to sheet300 in a session). What I would suggest is to use: Sheets(Name1) or set a variable for the sheet name ShtName = Name3 and use: Sheets(ShtName).Select Paul From: Nasim nbeiz...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Mon, February 14, 2011 6:02:46 PM Subject: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 Hi, we are using excel 2010. I use sheets(2) in my code and it was working fine (since this was the 2nd tab in my file till I manually added another sheet and I moved this 3rd sheet betwenn sheet 1 and 2 so now 3rd sheet is in 2nd order in my file(2nd tab). I have renamed the tabs ofcourse and the indexes should still work but they dont. here is the broblem set wsh = thisworkbook.sheets(2) wsh.activate : this line activates sheets(3) which is the 2nd tab in my file now I used sheets(sheet2) and it produces errors. It seems like all sheet indexes after have shifted 1 number up. I need to reference many different sheets in this file and I want to be able to add/delete/move/rename sheets without any problems. Only if index was working I have never had this problem before. this is the first time this is happening. Is it seemthing I do wrong or is it the setup or is it excel 2010? I appreciate your help. Best regards, Nasim -- ------ Some important links for excel users: 1. Follow us on TWITTER 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 linkhttp://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
Re: $$Excel-Macros$$ Load image from the url
I am using a web url. Is there anyway I can covert/save web url in picture format and then call it in the below function? Regards, San On Tue, Feb 15, 2011 at 5:46 AM, ashish koul koul.ash...@gmail.com wrote: Sub test() UserForm1.Image1.Picture = LoadPicture(D:\koul\krishna.jpg) UserForm1.Show End Sub On Tue, Feb 15, 2011 at 11:55 AM, San Pat myitems2...@gmail.com wrote: How I can load a image from a url using the LoadPicture function in userform. Regards, -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Fwd: Need to Check
what exactly you are looking for .. do you want us to transfer data into the site from excel sheet On Tue, Feb 15, 2011 at 6:46 AM, Rajasekhar Praharaju rajasekhar.prahar...@gmail.com wrote: hi , Please help in this concern can any one help in this concern i require this computation using excel macro below is the link which is coded in HTML. Which is working fine but i cant use this link in my organisation so kindly can anyone help to build the excel macro for this concern. Thanks, Raj http://billingcombins.appspot.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 on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2
The choice of which approch to use is really a question of Readability and Functionality. You can use: Sheet1.Select Sheet2.Select Sheet3.Select as long as you know you're not going to be deleting and adding sheets. Once you delete a sheet, even if you make a copy and delete the original, this will fail and you have to update the code. Plus, as a programmer, you'll have to remember which sheet is the Process sheet, etc. The same problem occurs with the use of Sheets(i) If someone moves a sheet within the workbook, the sheets() array changes. I prefer to use something like this at the beginning of the macro: ShtProcess = Process ShtHours = Hours ShtBreakdn = Breakdown then within the macro: Sheets(ShtProcess).Select That way, If I want, I can create a routine that checks for valid sheet names. If someone changes the sheet name, I can find it based on keywords and rename the sheet variable. Even then, if I decide to change the sheet name, I only have to change it once in my routine. also, see my notes below: From: Nasim nbeiz...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, February 15, 2011 11:37:11 AM Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 -- this should be: Thisworkbook.sheetS(whatever).select -- 2- Doesn't this change the name that the tab is showing (showing whatever instead of hours)? If so, what if somebody renames the tabs later. Will this not confuse a user as I am replacing the sheet name to what I want? - Yes... Thisworkbook.sheet2.name = whatever does change the sheet name that is displayed in the sheet tabs. But you wouldn't change the sheet name unless necessary... Did I misunderstand the question? --- 3- Or should I simply say: Thisworkbook.sheet2.select? Does this select sheet2(hours) or sheet2(Breakdown)? I guess it will be sheet2(hours). - First of all sheet2 is not an array (so no ()'s) Sheet2 is a sheet OBJECT. The Sheets() array requires an array index, or the sheet name. sheets(Breakdown) would imply that there is a variable called Breakdown that has a value being passed to the Sheets() array. Since Breakdown is NOT a variable, it would result in: Sheets(), which would fail. Using your example, you can use: Sheets(Breakdown).select Sheets(2).Select (since you said Breakdown is the second sheet) Sheet3.select - 4- What if I need to use variable and refer to sheet2 not sheet in 2nd position? I used to used sheets(i). if i=2 then it will point to sheet3(which in 2nd position-as I learnt from you) whereas I need to point to sheet2 which is in 3rd position. what syntax should I use? -- I would suggest using the sheet name: ShtName = Breakdown Sheets(ShtName). If you MUST use the sheets array index, you can find it by using: For i = 1 to sheets.count if (sheets(i).name = Breakdown) then exit for end if next i sheets(i).Select -- 5- Here is the part that I am referencing my sheets: Dim WSB, WSH As Worksheet Dim u, t, p, q, LastRowH, LastRowB As Integer Set WSB = ThisWorkbook.Sheets(9) ' : I changed it to Set WBS = Thisworkbook.Sheet9 and it does not work Set WSH = ThisWorkbook.Sheets(2) -- Can you tell me what your workbook structure is? what are the sheet names? what does the VB explorer show? can you send me the file? (or at least part of it?) I'd be glad to help Paul - I am still so confused. I appreciate if you could explaine this a bit more. Best regards, Nasim On Feb 14, 10:07 pm, Paul Schreiner schreiner_p...@att.net wrote: In your VBA Object Explorer, you'll see Sheet1(Name1) Sheet2(Name2) Sheet3(Name3) If you delete the sheet named Name2, create a new sheet, and call the NEW sheet Name2 It won't be: Sheet2(Name2), it'll be: Sheet4(Name2). So the LIST will be: Sheet1(Name1) Sheet3(Name3) Sheet4(Name4) in VBA the Sheets array is: Sheets(1).Name = Name1 Sheets(2).Name = Name3 Sheets(3).Name = Name2 Because the Sheets() array is a list of sheet names. An odd occurence though is: If you save and exit Excel, open the file, and create a new Sheet (Name4) it will create it as: Sheet2(Name4) and the array becomes: Sheets(1).Name = Name1 Sheets(2).Name = Name4 Sheets(3).Name = Name3 Sheets(4).Name = Name2 So, basically, when you see sheet1,
$$Excel-Macros$$ Segmentation?
Say for example I have one tab that has a list of contact information - say anywhere from 1000 to over 100,000 contact items that include name (one field for first and last name), phone, address, etc. On the other tab I have a list of Categories. 1 column for Category Name and the other for a text identifier that is unique to that Category. Something like this: A column of Worksheet 1: Michael Lovelady Steve Smith Jonathan Lovelady Phillip Lovelace A B columns of 1st row of Worksheet 2 (hyphen separated): Love - lov What I want to do is add an additional column to Worksheet 1 that does a vlookup (or something similar?) to match text specified in worksheet 2 (column B) and if it matches to return the Category Name specified in column A. I basically have tons of data that I need to segment on an ongoing basis and am looking for a good way to do it. Many thanks to anyone who can 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: error checking VBA Excel 2003
I have solved the cancel button exit from the routine problem part 1 as follows:- 'Check to see if cancel button is pressed go back to Menu If Newuser = false Then Sheets(Menu).Select Exit Sub End If Still cannot get anything on the other two problems Brian Scotland On Feb 15, 11:40 am, BJthebear brian.jerm...@gmail.com wrote: I have written a sub routine to add a name to a dynamic list but I have three problems with it and would be grateful for some advice. First the routine:- Sub InputNewUser() ' ' InputNewUser Macro ' Macro recorded 01/02/2011 by Brian ' Dim Newuser As String Dim nextRow As Long Dim flag As Boolean flag = False 'find out if main database or staff copy - true if main database false if email copy For Each wksheet In Application.Worksheets If wksheet.Name = HoursWorkedexpenses Then flag = True Exit For End If Next wksheet If flag = False Then MsgBox You do not have sufficient authority to create a new user End Else End If 'Ask for name of new user Newuser = Application.InputBox(Please enter name of new user (Surname first)) 'turns it into proper letters Newuser = Application.Proper(Newuser) 'Check to see if cancel button is pressed go back to Menu If Newuser = Then Sheets(Menu).Select Exit Sub End If 'Routine to check if Newuser already exists in list of users ' 'Find last row Set SrcSht = Sheets(UserList) nextRow = SrcSht.Cells(Cells.Rows.Count, A).End(xlUp).Row + 1 Application.ScreenUpdating = False Range(A nextRow).Select ActiveCell.FormulaR1C1 = Newuser Columns(A:A).Select Selection.Sort Key1:=Range(A2), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.ScreenUpdating = True 'display message to show that it has been added to userlist MsgBox (Newuser has been added to List of Users) 'return to menu page Sheets(Menu).Select End Sub The problems are as follows:- 1. I have just added the code to check to see if the cancel button is pressed but it does not exit the subroutine - it adds a false to the list of users. 2. How do I check to see if the Newuser entry already exists in the list of users 3. The routine seems to work when running it from the VBA window but when the macro is attached to a button on the Menu Wrksheet it fails to add the name to the list of users. This routine worked up until a few days ago and it really has me stumped Any help would be appreciated Brian Scotland -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ RE: Excel graphs and formula for adding time spent
Dear Vinod, The sum formula is giving correct solution if you mean by 0:20 mins. Kindly explain where are you facing the issue or why do you think the sum for 4:30 is not correct. -- Best Regards! Prakash Singh Gusain *Please visit my blog (**click here http://prakashgusain.blogspot.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
$$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!
I am struggling with this and wondering if you could help.. Worksheet 1 Column A B Keyword Category telecom Telecom tele com#N/A Worksheet 2 Column A B termcategory telecom Telecom telcom Telecom tele comTelecom What I want to do is pull the category name from worksheet 2 on to worksheet 1. =VLOOKUP(*A2*,Sheet1!B:B,1,0) Is not working...any ideas? -- -- Some important links for excel users: 1. Follow us on TWITTER 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 intrinsic constants/enumerations
Sanjoy, Thanks for your reply. I suspect you are saying that if I press F2 in something like Visual Studio I will get a page come up that will show the values for the constants/enumerations. (I'm not at a place that I can check VS).My question may be a bit more fundamental. When I try to compile the compiler says such are not defined. So I think I need to do something similar as C and include some header file that defines the constants. In my research I've come across the statement Imports which might be the way but unfortunately it did not work for me. Does it make sense what I'm asking? I'm sure it's simple for all the experienced VB.NET programmers. Thanks, Jeff -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Diff excel Sheet based on Name test
Hi Gurus, I Just want to create Diff excel sheet based onn Emp name sheet name should be emp name date.there are diff test are available for each process I also want to update that process test scores in respective sheet (kindly see the sheets for more info),i am having very large excel database every week process names are changed so request you to kindly proive the solution which creates the automatically sheet name filter the test columns also i want to create that sheet as a individual workbook need to saved automatically on desktop with same sheet name date. -- Thanks Regards, Maulik Desai 9967363926 -- -- Some important links for excel users: 1. Follow us on TWITTER 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 Diff excel Sheet based on Name test.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Saving excel workbook to 2 folders
I ahve a workbook for each of my staff for coaching purposes, I need to save this into a Common drive so they can password protect it to enable only me and them to access it. I also need to save it without a password into a management drive to allow my manager to access it without the need for a password. Currently i do this in two separate operations, idally on save I would like it to just save in the two separate folders. I can write basic macros but do not have the time to dig into vba. Any help greatly appreciated. thanks Balders -- -- Some important links for excel users: 1. Follow us on TWITTER 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$$ RASHI QUERRY
=VLOOKUP(IF(SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))K8,SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))-K8,SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))),A3:C15,3,TRUE) will give you the solution! -- Best Regards! Prakash Singh Gusain *Please visit my blog (**click here http://prakashgusain.blogspot.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$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2
Hi Paul, Thanks for your help. I think I now understand how it works. After I sent you the file I tried what is suggested in below link one more time and it works great. It is basically the same thing that you told me to do with a little bit of change. Here is the link for anybody interested: http://www.tek-tips.com/faqs.cfm?fid=4090 So I changed the sheet's (name) in the properties window to shtBonus and then used it like this: shtBonus.Range(A1).select Once again, Thanks for your help and directing me to right direction. Best regards, Nasim On Feb 15, 11:48 am, Paul Schreiner schreiner_p...@att.net wrote: The choice of which approch to use is really a question of Readability and Functionality. You can use: Sheet1.Select Sheet2.Select Sheet3.Select as long as you know you're not going to be deleting and adding sheets. Once you delete a sheet, even if you make a copy and delete the original, this will fail and you have to update the code. Plus, as a programmer, you'll have to remember which sheet is the Process sheet, etc. The same problem occurs with the use of Sheets(i) If someone moves a sheet within the workbook, the sheets() array changes. I prefer to use something like this at the beginning of the macro: ShtProcess = Process ShtHours = Hours ShtBreakdn = Breakdown then within the macro: Sheets(ShtProcess).Select That way, If I want, I can create a routine that checks for valid sheet names. If someone changes the sheet name, I can find it based on keywords and rename the sheet variable. Even then, if I decide to change the sheet name, I only have to change it once in my routine. also, see my notes below: From: Nasim nbeiz...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, February 15, 2011 11:37:11 AM Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2 -- this should be: Thisworkbook.sheetS(whatever).select -- 2- Doesn't this change the name that the tab is showing (showing whatever instead of hours)? If so, what if somebody renames the tabs later. Will this not confuse a user as I am replacing the sheet name to what I want? - Yes... Thisworkbook.sheet2.name = whatever does change the sheet name that is displayed in the sheet tabs. But you wouldn't change the sheet name unless necessary... Did I misunderstand the question? --- 3- Or should I simply say: Thisworkbook.sheet2.select? Does this select sheet2(hours) or sheet2(Breakdown)? I guess it will be sheet2(hours). - First of all sheet2 is not an array (so no ()'s) Sheet2 is a sheet OBJECT. The Sheets() array requires an array index, or the sheet name. sheets(Breakdown) would imply that there is a variable called Breakdown that has a value being passed to the Sheets() array. Since Breakdown is NOT a variable, it would result in: Sheets(), which would fail. Using your example, you can use: Sheets(Breakdown).select Sheets(2).Select (since you said Breakdown is the second sheet) Sheet3.select - 4- What if I need to use variable and refer to sheet2 not sheet in 2nd position? I used to used sheets(i). if i=2 then it will point to sheet3(which in 2nd position-as I learnt from you) whereas I need to point to sheet2 which is in 3rd position. what syntax should I use? -- I would suggest using the sheet name: ShtName = Breakdown Sheets(ShtName). If you MUST use the sheets array index, you can find it by using: For i = 1 to sheets.count if (sheets(i).name = Breakdown) then exit for end if next i sheets(i).Select -- 5- Here is the part that I am referencing my sheets: Dim WSB, WSH As Worksheet Dim u, t, p, q, LastRowH, LastRowB As Integer Set WSB = ThisWorkbook.Sheets(9) ' : I changed it to Set WBS = Thisworkbook.Sheet9 and it does not work Set WSH = ThisWorkbook.Sheets(2) -- Can you tell me what your workbook structure is? what are the sheet names? what does the VB explorer show? can you send me the file? (or at least part of it?) I'd be glad to help Paul - I am still so confused. I appreciate if you could explaine this a bit more. Best regards, Nasim On Feb 14, 10:07 pm, Paul Schreiner schreiner_p...@att.net wrote: In your VBA Object Explorer, you'll
Re: $$Excel-Macros$$ Saving excel workbook to 2 folders
The command you're looking for is SaveCopyAs I would create a BeforeSave event, there, save the file locally, then define the backup location, unprotect the file, then save a copy at the backup location. Let me know if you need specifics. Paul From: balders baldr...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, February 15, 2011 5:35:47 PM Subject: $$Excel-Macros$$ Saving excel workbook to 2 folders I ahve a workbook for each of my staff for coaching purposes, I need to save this into a Common drive so they can password protect it to enable only me and them to access it. I also need to save it without a password into a management drive to allow my manager to access it without the need for a password. Currently i do this in two separate operations, idally on save I would like it to just save in the two separate folders. I can write basic macros but do not have the time to dig into vba. Any help greatly appreciated. thanks Balders -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!
Hi Mike, Please use: =VLOOKUP(A2,Sheet1!B:B,1,0) instead. Regards, Andy On Wed, Feb 16, 2011 at 2:21 AM, MikeMikeMike michael.lovel...@gmail.comwrote: I am struggling with this and wondering if you could help.. Worksheet 1 Column A B Keyword Category telecom Telecom tele com#N/A Worksheet 2 Column A B termcategory telecom Telecom telcom Telecom tele comTelecom What I want to do is pull the category name from worksheet 2 on to worksheet 1. =VLOOKUP(*A2*,Sheet1!B:B,1,0) Is not working...any ideas? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Segmentation?
can you please attach the sample workbook On Tue, Feb 15, 2011 at 11:58 PM, MikeMikeMike michael.lovel...@gmail.comwrote: Say for example I have one tab that has a list of contact information - say anywhere from 1000 to over 100,000 contact items that include name (one field for first and last name), phone, address, etc. On the other tab I have a list of Categories. 1 column for Category Name and the other for a text identifier that is unique to that Category. Something like this: A column of Worksheet 1: Michael Lovelady Steve Smith Jonathan Lovelady Phillip Lovelace A B columns of 1st row of Worksheet 2 (hyphen separated): Love - lov What I want to do is add an additional column to Worksheet 1 that does a vlookup (or something similar?) to match text specified in worksheet 2 (column B) and if it matches to return the Category Name specified in column A. I basically have tons of data that I need to segment on an ongoing basis and am looking for a good way to do it. Many thanks to anyone who can 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Saving excel workbook to 2 folders
Private Sub Workbook_BeforeClose(Cancel As Boolean) ' with password ThisWorkbook.SaveAs Filename:=d:\test3.xls , FileFormat:= _ xlNormal, Password:=abc, WriteResPassword:=abc1, ReadOnlyRecommended:=False _ , CreateBackup:=False 'without password ThisWorkbook.SaveAs Filename:=d:\test.xls End Sub On Wed, Feb 16, 2011 at 4:05 AM, balders baldr...@gmail.com wrote: I ahve a workbook for each of my staff for coaching purposes, I need to save this into a Common drive so they can password protect it to enable only me and them to access it. I also need to save it without a password into a management drive to allow my manager to access it without the need for a password. Currently i do this in two separate operations, idally on save I would like it to just save in the two separate folders. I can write basic macros but do not have the time to dig into vba. Any help greatly appreciated. thanks Balders -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Dynamic range
see if this helps Sub growth() Dim i As Long i = Range(s21).End(xlDown).Row Columns(S:S).Select Selection.Insert Shift:=xlToRight Range(S21).Select ActiveCell.FormulaR1C1 = Growth Range(S23).Select ActiveCell.FormulaR1C1 = =(RC[-2]-RC[-1])/RC[-2] Range(S23).Select Selection.NumberFormat = 0.00% Selection.AutoFill Destination:=Range(S23:S490) Range(S23:S i).Select End Sub On Wed, Feb 16, 2011 at 2:57 AM, Skanda skanda.pokkun...@gmail.com wrote: Sub growth() ' ' growth Macro ' Columns(S:S).Select Selection.Insert Shift:=xlToRight Range(S21).Select ActiveCell.FormulaR1C1 = Growth Range(S23).Select ActiveCell.FormulaR1C1 = =(RC[-2]-RC[-1])/RC[-2] Range(S23).Select Selection.NumberFormat = 0.00% Selection.AutoFill Destination:=Range(S23:S490) Range(S23:S490).Select End Sub In the above code, the range is set from S23 to S490.But there could be changes in the number of records in the future. how to make it more dynamic, keeping the S23 as constant but S490 to change depending on the records from RC[-2] and RC[-1]. i.e if columns RC[-2] and RC[-1] have 5000 records in future how make the above code 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* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
