RE: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula
Oops The below should read: Is A5 less than or equal to T5? - No - Go to the next statement Is A5 less than or equal to T6? - No - Go to the next statement Is A5 less than or equal to T7? - Yes - Stop. Answer: S7 Dave. From: davebonall...@hotmail.com To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula Date: Mon, 1 Aug 2011 12:50:31 +0800 Hi, Try this: =IF(A5=$T$5,$S$5,IF(A5=$T$6,$S$6,IF(A5=$T$7,$S$7,IF(A5=$T$8,$S$8,IF(A5=$T$9,$S$9,IF(A5=$T$10,$S$10,)) The multiple IF statement stops as soon as it finds a TRUE. So you don't need to use AND statements to create 'windows' The above asks: Is A5 greater or equal to T5? - No - Go to the next statement Is A5 greater or equal to T6? - No - Go to the next statement Is A5 greater or equal to T7? - Yes - Stop. Answer: S7 This could probably be easier done with VLOOKUP Regards - Dave. Date: Sun, 31 Jul 2011 17:50:12 -0700 Subject: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula From: velocity...@gmail.com To: excel-macros@googlegroups.com Any help in shortening or optimizing this formula would be great. Im limited by the nested if statements to match week numbers (column s) and week ended date (Column T), and continue to bog down the recalculations. It does work, but slows things down.I'm using it to determine if a date falls within a certain period in Column T =IF(A5=$T$5,$S$5,IF(AND(A5$T$5,A5=$T$6),$S$6,IF(AND(A5$T$6,A5=$T $7),$S$7,IF(AND(A5$T$7,A5=$T$8),$S$8,IF(AND(A5$T$8,A5=$T$9),$S $9,IF(AND(A5$T$9,A5=$T$10),$S$10,)) many thanks, Financeguy -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula
Hi, Try this: =IF(A5=$T$5,$S$5,IF(A5=$T$6,$S$6,IF(A5=$T$7,$S$7,IF(A5=$T$8,$S$8,IF(A5=$T$9,$S$9,IF(A5=$T$10,$S$10,)) The multiple IF statement stops as soon as it finds a TRUE. So you don't need to use AND statements to create 'windows' The above asks: Is A5 greater or equal to T5? - No - Go to the next statement Is A5 greater or equal to T6? - No - Go to the next statement Is A5 greater or equal to T7? - Yes - Stop. Answer: S7 This could probably be easier done with VLOOKUP Regards - Dave. Date: Sun, 31 Jul 2011 17:50:12 -0700 Subject: $$Excel-Macros$$ $$Excel-Macro$$ - Optimize Formula From: velocity...@gmail.com To: excel-macros@googlegroups.com Any help in shortening or optimizing this formula would be great. Im limited by the nested if statements to match week numbers (column s) and week ended date (Column T), and continue to bog down the recalculations. It does work, but slows things down.I'm using it to determine if a date falls within a certain period in Column T =IF(A5=$T$5,$S$5,IF(AND(A5$T$5,A5=$T$6),$S$6,IF(AND(A5$T$6,A5=$T $7),$S$7,IF(AND(A5$T$7,A5=$T$8),$S$8,IF(AND(A5$T$8,A5=$T$9),$S $9,IF(AND(A5$T$9,A5=$T$10),$S$10,)) many thanks, Financeguy -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ adding adjacent (contagious) cells only
Hi Haseeb, I of course haven't seen every answer to every question on this forum, but your formula solution to finding the number of groups is one of the best I've seen. Regards - Dave. Date: Thu, 21 Jul 2011 09:11:12 -0700 From: haseeb.avarak...@gmail.com To: excel-macros@googlegroups.com Subject: Re: RE: $$Excel-Macros$$ adding adjacent (contagious) cells only Thank you Dave for the last cell point the correction. I didn't notice the last column being Yes. I think Column U don't have to be blank, because we have an additional column (A) without Yes or No, so If we change the formula without including column U, like, =SUMPRODUCT(--(B2:T2=Yes),--(A2:S2Yes)) Or, =COUNTIFS(B2:T2,Yes,A2:S2,Yes) Regards, Haseeb -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ adding adjacent (contagious) cells only
Hi Azeema, Have a look at the attached to see if it meets your needs. The formula is really cumbersome, but it's all I can think of at the moment. Regards - Dave. From: aze...@gmail.com Date: Tue, 19 Jul 2011 23:44:14 -1000 Subject: $$Excel-Macros$$ adding adjacent (contagious) cells only To: excel-macros@googlegroups.com Dear Experts: Let me see if I can explain this to you. I am working on a research project which relates to time use from 6am to 12 pm, by different activities. (see attached data for a selected no of respondents for Unpaid Work only). I would like to calculate segments of time or unbroken chain of time spent on an activity. For example, if someone reported yes (for unpaid work) at 7 am then another yes at 12 pm and then at 3 pm, that is obviously 3 segments of time reported for Unpaid work. But if someone reports unpaid work at 7 am and it goes on till 10 am it is the same number of hours but only 1 segment! While I can calculate the amount of total time a person has spent on an activity, I cannot come up with the formula to calculate the number of segments for each row (respondent). Can someone help me count the SEGEMENTS of time? Many thanks in advance. Azeema -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 UnpaidWorkSegmants.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ adding adjacent (contagious) cells only
Hi Haseeb, A sort of masking. Simple and excellent! However, if a 'Yes' appears in the last column, the count is incorrect by 1. You could possibly include an If statement to test for this. Or you could modify the formula slightly: =SUMPRODUCT(--(A2:T2=Yes),--(B2:U2Yes)) This works, but requires Column U to be left blank, or at least not to contain any Yes's Regards - Dave. Date: Wed, 20 Jul 2011 17:42:59 -0700 From: haseeb.avarak...@gmail.com To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ adding adjacent (contagious) cells only Hello Azeema, Use this formula, say in V2 copy down. =COUNTIFS(A2:S2,Yes,B2:T2,Yes) This will only work XL2007 or later. If you want to work with XL 2003 or prior use SUMPRODUCT =SUMPRODUCT(--(A2:S2=Yes),--(B2:T2Yes)) See the attached HTH Haseeb -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ months and fraction of a month between two days
Hi, Fractions of a month are a bit subjective. Fractions of WHICH month, needs to be decided. Fractions of a 28-day month? 29- 30- or 31- day month? Or maybe a standardized 30.4375-day month? Regards - Dave On 12/07/2011, at 8:55 PM, Richard richard.m...@gmail.com wrote: Hi All, I would appreciate a vba function that will calculation the number of months and fraction of a month between 2 dates. i.e. January 1, 2011 to February 14, 2011 would be 1.5 months January 1, 2011 to February 15, 2011 would be 1.5335714 January 1, 2011 to March 15, 2011 would be 2.483871 Thank you in advance Rich -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Application.Caller
Hi Ashish, Exactly what I wanted. Didn't know about the .TextFrame part. Thanks! Regards - Dave. Date: Thu, 9 Jun 2011 11:10:06 +0530 Subject: Re: $$Excel-Macros$$ Application.Caller From: koul.ash...@gmail.com To: excel-macros@googlegroups.com below code will help you in extracting the text on images see if it helps you Rectangle 1 , 5-Point Star 2 name of images or boxes a = ActiveSheet.Shapes(Rectangle 1).TextFrame.Characters.Text b = ActiveSheet.Shapes(5-Point Star 2).TextFrame.Characters.Text On Thu, Jun 9, 2011 at 9:59 AM, Dave davebonall...@hotmail.com wrote: Hi group, I have 3 buttons on the worksheet which all fire the same macro. Each button has different text, and the macro examins this text, and uses it to run slightly differently depending on which button was used to fire it. To obtain the text on the button, I use: A = ActiveSheet.Buttons(Application.Caller).Characters.Text The variable 'A' then holds the text. But Excel buttons are a bit dull, so I was trying to brighten up the sheet by using rectangles instead, which I can format as I please. Although I could fire the macro from the rectangle, I couldn't find the syntax to return the text within the rectangle. Anyone know the syntax for this? Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com http://akoul.posterous.com/ akoul.wordpress.com My Linkedin Profile P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ get name of months from date
You can also format the cell Regards - Dave On 09/05/2011, at 9:17 AM, STDEV(i) setiyowati.d...@gmail.com wrote: Unfortunately Month Function returns Month Index ( 1 to 12 ) not Month NAME Assuming A1 containts date data : 12/31/2011 Formula in B1 =MONTH(A1) returns : 12 not December In vba you can used MonthName Function Sub AboutMonth() Range(B1) = Month(Range(A1)) Range(C1) = MonthName(Range(B1)) End Sub In worksheet there is no MonthName function CMIIW On Mon, May 9, 2011 at 2:44 AM, Nishant Sharma leonish...@gmail.com wrote: Use Month formula open excel and copy this in A1 04-04-2011 =month(A1) Thanks Nishant On Sun, May 8, 2011 at 4:48 PM, Rajesh K R rajeshkainikk...@gmail.com wrote: Hi Experts If I want to get the name of month from dates 1/4/11- April 5/5/11 - May how I can I do it with a formula or code 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ VLOOKUP(H17,[231.xls]Sheet1!$I$5:$K$20,H$15,0)
Hi Pravin, You could try using the INDIRECT function VLOOKUP(H17,INDIRECT([SC231WKA134.xls]Sheet1!$I$5:$K$20),H$15,0) Change the A1 ref to the cell that holds the book number you want Please note that I haven't tested this. I haven't tried VLOOKUP with INDIRECT before. Regards - Dave Date: Thu, 5 May 2011 18:04:26 +0530 Subject: Re: $$Excel-Macros$$ VLOOKUP(H17,[231.xls]Sheet1!$I$5:$K$20,H$15,0) From: noorain.ans...@gmail.com To: pravin.si...@gmail.com; excel-macros@googlegroups.com Dear Pravin, Please try it. =IFERROR(VLOOKUP(H17,[SCWK23134.xls]Sheet1!$I$5:$K$20,H$15,0),IFERROR(VLOOKUP(H17,[SC231WK35.xls]Sheet1!$I$5:$K$20,H$15,0),IFERROR(VLOOKUP(H17,[SC231WK36.xls]Sheet1!$I$5:$K$20,H$15,0),))) On Thu, May 5, 2011 at 12:47 PM, Pravin Kumar pravin.si...@gmail.com wrote: Folk I have different workbook with different name in different folder. the workbook name will be like SC231WK34, SC231WK35, SC231WK36 etc. all the sheet will be in the same format. is there any way to write vlookup where I can link the part of the name to any of cell. VLOOKUP(H17,[SC231WK34.xls]Sheet1!$I$5:$K$20,H$15,0) Thanks Pravin -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ SUMIFS
Hi,
Without your file, I can't test this, but the XL did accept the following as a
valid formula:
=SUMIFS('1-Diary'!$H$9:$H9954,'1-Diary'!$B$9:$B9954,Today(),'1-Diary'!$J$9:$J9954,HolidayN)
Regards - Dave.
Date: Tue, 3 May 2011 05:47:53 -0700
Subject: $$Excel-Macros$$ SUMIFS
From: gmccaff...@acutus.co.uk
To: excel-macros@googlegroups.com
Dear Experts
I want to insert the NOW() or TODAY() expression into formula below
instead of the 40666 value. However, it wont work when I do it. I've
tried a number of different syntax and cannot seem to get it.
=SUMIFS('1-DIARY'!$H$9:$H9954,'1-DIARY'!$B$9:$B9954, 40666,'1-
DIARY'!$J$9:$J9954,HolidayN)
Can it be done? Thanks in advance.
Regards
Gerry.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Help with formula
Hi, One way to do it would be: -Find the last cell in your data (eg A1000) -Multiply that cell's row number by 2 (eg 2000) -Paste that cell's data into the cell with the doubled row number (eg A2000) -Clear that cell (ie A1000) -Decrement the row by 1 (ie A999) -Loop to step 2 Regards - Dave. Date: Tue, 3 May 2011 14:13:26 -0700 Subject: $$Excel-Macros$$ Help with formula From: hemantda...@yahoo.com To: excel-macros@googlegroups.com Hi I have a data in cells and i want to reformat it with every alternate cell as empty cell in excel. so without any data loss every alternate cell will be empty. please give me the solution. 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ macro to add fill color to a column of selected range
You're welcome. Regards - Dave Date: Mon, 25 Apr 2011 17:29:30 -0700 Subject: Re: $$Excel-Macros$$ macro to add fill color to a column of selected range From: cje...@yahoo.com To: excel-macros@googlegroups.com That's excellent Dave. Exactly what I was looking for. 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ macro to add fill color to a column of selected range
Hi, Something like: Dim c as Range For each c in Selection If c.Column = 6 then c.Interior.Colorindex = 3 Next c Note: Colorindex 6 will give you red. Change the number to suit. Regards - Dave. Date: Sat, 23 Apr 2011 14:17:08 -0700 Subject: $$Excel-Macros$$ macro to add fill color to a column of selected range From: cje...@yahoo.com To: excel-macros@googlegroups.com I have a ~500 rows of data in columns A-G. Several contiguous rows of this data (columns A-G) will be selected. I would like my macro to fill column F of the selected cells with color without losing the original selection because these selected cells are used later in the macro. I've been able to fill all selected cells with color but I would like column F only. I might be able to increment through each row of the macro to test if that cell has been selected but I'm thinking that this method would cause a delay as it tested every row. I was looking for a more straightforward method. Thank you for any 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Ring-Fence macro's copy/paste, so can copy/paste without interference outside the macro
Hi Tom, Problem is, when you use copy/paste in a macro, it uses the same clip board as any other application that's running. The solution is probably to eliminate copy/paste from your macro. It's a very inefficient way to run, and there's nearly always a better and much quicker way to do the same thing. Regards - Dave On 14/04/2011, at 10:37 AM, tomfabtas...@hotmail.com tomfabtas...@hotmail.com wrote: Hi, I run an excel macro on a loop (Excel 2007), which runs for most of the day and includes copy/paste code. While this macro is running, in another instance of excel I need to copy and paste (manually, not using a macro). Sometimes when I manually copy/paste, I get the paste from the macro. So, the macro is doing a copy just after I manually do a copy. Is there anyway to ring-fence the macro, so that the copy/paste does not interfer with the other work I am doing ? Regards, 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ copy macro error
Hi Jorge, Your workbook didn't contain any macros. Regards - Dave Date: Tue, 5 Apr 2011 23:40:56 +0100 Subject: $$Excel-Macros$$ copy macro error From: leote.w...@gmail.com To: excel-macros@googlegroups.com Hi, can someone please tell what i did wrong, this is my first macro all by myself, but it gives me a error, i made a macro where i what to copy sheet teste to sheet destino but without range, so that when i introduce new values to column A,B,C and execute the macro it copys all values to destino, but it only copys last cell of A :S!can someone see what´s wrong with my code?1000 thanks! Sub Datacopy() Worksheets(teste).Cells(Rows.Count, A).End(xlUp).Copy With Worksheets(destino) .Cells(Rows.Count, A).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues End With End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ discussexcel : Macro: Column Number to Alphabetical reference
Excellent formula! Dave. From: setiyowati.d...@gmail.com Date: Sat, 26 Mar 2011 16:40:29 +0700 Subject: Re: $$Excel-Macros$$ discussexcel : Macro: Column Number to Alphabetical reference To: excel-macros@googlegroups.com =SUBSTITUTE(ADDRESS(1,A1,4),1,) =SUBSTITUTE(ADDRESS(1,16384,4),1,) On Fri, Mar 25, 2011 at 10:40 PM, Ayush jainayus...@gmail.com wrote: Hello everyone, Here is the UDF to convert a number into Alphabetical reference. If you pass 1 to the function, it will Return A. If you pass 26, It will return Z. If you pass 27 It will return AAand so on. http://www.discussexcel.com/learn-macros/vba-macros Let me know if you have better solution. Thanks and best regards Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ How to prevent from duplication in one work book
Hi Akbar, Have a look at the attached. Type Akbar anywhere on sheet 1 To do the vice-versa thing, enter similar code into the VBA Sheet 3 window. Regards - Dave. Date: Thu, 24 Mar 2011 11:36:15 +0500 Subject: $$Excel-Macros$$ How to prevent from duplication in one work book From: sh.talal.ak...@gmail.com To: excel-macros@googlegroups.com my question is i enterd the word Akbar in the sheet 1 and then i moved towards sheet 3 and there i m again going to enter Akbar at this time excel notify me that i have already entered the same word in sheet numer 1 at column - and row -. and also vise versa plz help me 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Where's Akbar.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Median If Formula
Hi Susan, =MEDIAN(IF(F5:F17=1,H5:H17)) This is right, but it's an array formula, so you have to enter it with Ctrl+Shift+Enter When you do this, it gives the right answer. Regards - Dave. Date: Wed, 23 Mar 2011 14:39:36 -0700 Subject: $$Excel-Macros$$ Median If Formula From: sunni...@gmail.com To: excel-macros@googlegroups.com Hi, See tab named Mar 05 in the attachment. I'm trying to get Excel 2007 to calculate the Median in data Columns H , cells 5 through 17, but only if it meets certain criteria of Task done in Column F, cells 5 to 17. My formula which should work, is =MEDIAN(IF(F5:F17=1,H5:H17)), but the answer is wrong , 40 but when I did a manual median formula =MEDIAN(H5,H9,H17) , of those cells in Column F that meet the criteria of 1, the answer is 55 When I place in around =1 the formula doesn't work at all. By removing the makes the formula work but it's not calcuating the median correctly. Any help with this would be excellent! ~Susan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Need coding help!
Hi Sandra, I may be a bit dim tonight, but it's not clear to me if the data sample you provided is the format you want, or the format you currently have. If this is the format you currently have, what format do you want? If this is the format you want, what format do you currently have? Regards - Dave. Date: Mon, 21 Mar 2011 14:09:29 -0700 Subject: $$Excel-Macros$$ Need coding help! From: zhshen...@gmail.com To: excel-macros@googlegroups.com Hello- I have a data set that has lists of names that recorded the person in position each year. For a study, I need to create a column for each year that records the years the person in that position. For example, if Johnson Smith was in the position in the first year, I recorded 1; the next year he was still there, so I gave 2. In the 3rd year, a new person was in the position, so I started from 1. ID Last First Year1 Last First Year2 Last First Year3 1 Smith Johnson 1 Smith Johnson 2 Gardner Sam 1 2 Sneider Mike 1 Sneider Mike 2 Sneider Mike 3 3 Clark Jennifer 1 White Joan 1 White Joan 2 4 Green Mark 1 Geen Mark 2 Carlson Aaron 1 The data set above is an example. Since I have a large list for about 20 years, I wonder if there is a way to code this procedure so that this can be done efficiently. Thank you, Sandra -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ While Looping, looking for cells that start with D
You're welcome Dave From: walpa...@hotmail.com To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ While Looping, looking for cells that start with D Date: Fri, 18 Mar 2011 15:55:08 -0600 Hi Dave, Thank you for your response. Sounds like your suggestion should work just fine. Best regards, Preston From: davebonall...@hotmail.com To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ While Looping, looking for cells that start with D Date: Fri, 18 Mar 2011 18:00:38 +0800 Hi, You can use an IF combined with LEFT, something like: IF Left(A2,1)=D Then (your process code here) Regards - Dave. Date: Thu, 17 Mar 2011 11:39:35 -0700 Subject: $$Excel-Macros$$ While Looping, looking for cells that start with D From: walpa...@hotmail.com To: excel-macros@googlegroups.com I am just starting to use VBA so I am a neophyte. I have data that shows units sold by product listed by store for over 40 stores. Each store has its own ID code and they all start with the letter D. When I encounter each new store I want to copy and past the units sold of each product to a pre-formatted spreadsheet. I think I want to do this by looping using the FOR EACH-NEXT construct. My question is: how do I write an instruction that says stop and evaluate the data (for copying pasting) each time a new store code is encountered? I think I need to write something to the effect that whenever a line starts with a D to stop and process? How would I go about doing this? Thanks. Preston -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ combobox criteria not working
Hi Santosh, You have declared number variables as Integer, which have a maximum allowance of (approx) +/- 32000. Dim A As Integer Dim B As Integer Dim c As Integer Try declaring them as Long Dim A As Long Dim B As Long Dim c As Long Regards - Dave. Date: Sat, 19 Mar 2011 20:22:18 +0530 Subject: $$Excel-Macros$$ combobox criteria not working From: santoshkumar.subu...@gmail.com To: excel-macros@googlegroups.com Hi Friends, Need a small help. Attached is a excel with a comand button which will populate a userform. In the userform there is a comand button called use combo which should calculate based on the combobox criteria. This is what not calculating properly. -- Regards Santosh santoshkumar.subu...@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 on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Remove text from a string
Hi, If your text is in A1, try this in B1: =LEFT(A1,SEARCH(~~,SUBSTITUTE(A1,@,~~,LEN(A1)-LEN(SUBSTITUTE(A1,@,-1) Regards - Dave Date: Fri, 18 Mar 2011 13:46:12 +0530 Subject: $$Excel-Macros$$ Remove text from a string From: deepakexce...@gmail.com To: excel-macros@googlegroups.com Hi Masters, My query is to remove all the characters after the last @ from the text. Example: The Raw test is deepak@gmail.comkaml...@yahoo.com The final text i want is deepak@gmail.comkamlesh Regards, Deepak Rawat -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ While Looping, looking for cells that start with D
Hi, You can use an IF combined with LEFT, something like: IF Left(A2,1)=D Then (your process code here) Regards - Dave. Date: Thu, 17 Mar 2011 11:39:35 -0700 Subject: $$Excel-Macros$$ While Looping, looking for cells that start with D From: walpa...@hotmail.com To: excel-macros@googlegroups.com I am just starting to use VBA so I am a neophyte. I have data that shows units sold by product listed by store for over 40 stores. Each store has its own ID code and they all start with the letter D. When I encounter each new store I want to copy and past the units sold of each product to a pre-formatted spreadsheet. I think I want to do this by looping using the FOR EACH-NEXT construct. My question is: how do I write an instruction that says stop and evaluate the data (for copying pasting) each time a new store code is encountered? I think I need to write something to the effect that whenever a line starts with a D to stop and process? How would I go about doing this? Thanks. Preston -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Remove text from a string
Hi LEN(A1)-LEN(SUBSTITUTE(A1,@,)) This part of the formula tells us how many @ there are in the string. It compares the length of the original A1 with the length of a modified A1 where the all @ are removed. In the case of your example, it's 2. We then substitute the 2nd instance of @ with ~~. I chose ~~ because it's very unlikely to appear in your data, but you could choose any unlikely combination of characters. Then we use search to find the position of the ~~ We then feed that number to the LEFT function as applied to A1, but have to subtract 1 to prevent the inclusion of the @ Please let me know if you need the explanation broken down any more. Regards - Dave. From: anil.bha...@tatacommunications.com To: excel-macros@googlegroups.com Date: Fri, 18 Mar 2011 16:05:23 +0530 Subject: RE: $$Excel-Macros$$ Remove text from a string Amazing Dave… very well answered… Could you please let us know how “~~” this is useful. Could you please explain or give me a link to understand this. Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Dave Bonallack Sent: Friday, March 18, 2011 3:28 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Remove text from a string Hi, If your text is in A1, try this in B1: =LEFT(A1,SEARCH(~~,SUBSTITUTE(A1,@,~~,LEN(A1)-LEN(SUBSTITUTE(A1,@,-1) Regards - Dave Date: Fri, 18 Mar 2011 13:46:12 +0530 Subject: $$Excel-Macros$$ Remove text from a string From: deepakexce...@gmail.com To: excel-macros@googlegroups.com Hi Masters, My query is to remove all the characters after the last @ from the text. Example: The Raw test is deepak@gmail.comkaml...@yahoo.com The final text i want is deepak@gmail.comkamlesh Regards, Deepak Rawat -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Re: User-defined type not defined - error
Hi Mrs Rum, I'd probably just use my Office CD to repair or reinstal XL Regards - Dave. Date: Thu, 17 Mar 2011 07:35:33 -0700 Subject: $$Excel-Macros$$ Re: User-defined type not defined - error From: mrs...@gmail.com To: excel-macros@googlegroups.com Please? Anyone? I can't even just rebuild the workbook because I'm getting the error in all of them. How do I find the code and fix it? Thanks again! On Mar 12, 3:49 pm, MrsRum mrs...@gmail.com wrote: Help! I'm getting this error in my spreadsheet. In my workbook. In all my workbooks. And I don't know why! I think my kids were playing around on my computer and touched something. I have not built any macros or code at all. How do I get rid of this? Every time I hit enter in a new cell the error pops up so it's not even like I can ignore it.. Any guidance would be appreciated. Mrs Rum. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ formula to add date in next column
Hi, After deciding that this was impossible, an idea came to me while I was in the shower. See the attached. B2 and B3 have data, so C2 and C3 should show the data entry date of 16 March 2011, no matter when the file is viewed. If you enter data into B4, the current date should appear in C4, and should not change from then on. I have only tested this in XL2003. There are limitations and a couple of tricks. Limitations: In your original post, you talk about 'updating' the data in Col A. In this worksheet, the date will only change if the cell in Col A originally has no data. ie, if A5 is blank, and you enter some data there, B5 will show the current date. But if A5 already has data, and you just over-write it with different data, B5 will remain unchanged. To actually change the date, the data in the Col A cell must be deleted, then new data entered. Tricks: In the Tools / Options Menu, Calculation Tab, I've ticked 'Iteration' and changed the Maximum iterations to 1. Also, Col B contains Conditional formatting to hide the erroneous dates of cells that don't have adjacent data in Col A. Not sure if this any use to you, but it was an interesting exercise. Regards - Dave. On Sun, Mar 13, 2011 at 1:53 AM, Hems coolh...@gmail.com wrote: Dear All, I want today's date in next column (column B) on the day when first column ( column A) was updated. for Ex. if A3 was updated on 14th Mar'11 then B3 should display that date. If i open this file on 16th Mar'11 then also B3 should display 14th Mar'11 and not 16th Mar'11. Appreciate i get formula for this issue rather then macro. Thanks in advance Kind Regards, Hemant -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Book1.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ formula to add date in next column
Hi Hemant, I don't think there's a non-macro solution for this. Regards - Dave. From: coolh...@gmail.com Date: Sun, 13 Mar 2011 23:36:21 +0530 Subject: Re: $$Excel-Macros$$ formula to add date in next column To: excel-macros@googlegroups.com Hey STDEV(i), Thanks a lot but i dont want macro, i want formula for it. Let me know if is there any formula for this. Regards, Hemant On Sun, Mar 13, 2011 at 1:04 PM, STDEV(i) setiyowati.d...@gmail.com wrote: put this code into sheets' module Private Sub Worksheet_Change(ByVal Target As Range) ' siti Vi villagera.g...@gmail.com ' jakarta, 11 mar 2011 ' If Target.Count = 1 Then If Target.Column = 1 Then If Target.Row 1 Then If Len(Target) 0 Then Target(1, 2) = Date End If End If End If End If End Sub On Sun, Mar 13, 2011 at 1:53 AM, Hems coolh...@gmail.com wrote: Dear All, I want today's date in next column (column B) on the day when first column ( column A) was updated. for Ex. if A3 was updated on 14th Mar'11 then B3 should display that date. If i open this file on 16th Mar'11 then also B3 should display 14th Mar'11 and not 16th Mar'11. Appreciate i get formula for this issue rather then macro. Thanks in advance Kind Regards, Hemant -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Need a Macro
Hi, One approach is to insert a new blank sheet, call it END and move it to become the last sheet in the workbook. Whenever adding new sheets, make sure they sit before the END sheet. If your SUM formula was, for example: =SUM(Sheet2:Sheet8!A1) change it to: =SUM(Sheet2:END!A1) All sheets between (and including) Sheet2 and END will be included, no matter how many there are. Hope this helps Regards - Dave. Date: Thu, 10 Mar 2011 16:16:37 -0800 Subject: $$Excel-Macros$$ Need a Macro From: karlaha...@hotmail.com To: excel-macros@googlegroups.com I have a tab that totals all of the other tabs in the spreadsheet. I need to be able to add new tabs and have the formulas in the totals tab automatically update. How do I do this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ OT
Hi Paul, Well said. I tried to let it go unchallenged, but since I had nothing better to do at the time... :) Dave. Date: Tue, 8 Mar 2011 19:12:46 +0530 Subject: Re: $$Excel-Macros$$ OT From: svyas0...@gmail.com To: excel-macros@googlegroups.com very nice speech Mr. Paul Sir. Regards Sumit Vyas On Tue, Mar 8, 2011 at 7:07 PM, Paul Schreiner schreiner_p...@att.net wrote: Dave, Hey! I'm an ENGINEER, not an ENGLISH major! Or a veterinarian for that matter! What kind of dog would jump over a fox anyway... Always seemed (seems?) suspicious (spurious?), if you ask me... lol. Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Mon, March 7, 2011 7:54:54 PM Subject: $$Excel-Macros$$ OT Hi Paul, Completely OT, but it has to be jumps, not jumped, otherwise there's no s in the sentence. -:) Dave. Date: Mon, 7 Mar 2011 06:13:04 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ ( ) for Array Must??? To: excel-macros@googlegroups.com It really depends on how you're using the array. Basically, using: Dim Arrayabc You're not declaring an array, you're declaring a single variable, type Variant. Now, later you can use Redim ArrayABC(30) and change the variable to an array. Or, curiously enough: ArrayABC = Split(The big brown dog jumped over the lazy fox, ) will Redim ArrayABC to whatever array size is required to store the string. (in this case, Ubound(ArrayABC) will be 8) If you were to use: Dim ArrayABC Dim ArrayDEF() Dim ArrayXYZ(3) . . . Redim ArrayABC(30) Redim ArrayDEF(30) Redim ArrayXYZ(30) ' Will not work, because the array was already defined. So, depending on how you plan to use it, you can either have the () or not. However, if you choose to use the (), you MUST (in some manner) define the array size before you can use the array. However: You CAN change a single variant to an array, and back again: Dim A A = The Big Brown Dog A = Split(The big brown dog jumped over the lazy fox, ) Erase A A = Jumped over the Lazy Fox hope this isn't too confusing. Paul From: hanumant shinde hanumant_5...@yahoo.co.in To: excel macros excel-macros@googlegroups.com Sent: Sat, March 5, 2011 2:31:03 PM Subject: $$Excel-Macros$$ ( ) for Array Must??? Hi friends, when we declare array is it must to give () i mean parantheses. can i declare array as dim arrayabc or i have to say dim arrayabc() i know that if we dont write any value in () its ok. but i am not sure if we can declare array without () -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page
$$Excel-Macros$$ OT
Hi Paul, Completely OT, but it has to be jumps, not jumped, otherwise there's no s in the sentence. -:) Dave. Date: Mon, 7 Mar 2011 06:13:04 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ ( ) for Array Must??? To: excel-macros@googlegroups.com It really depends on how you're using the array. Basically, using: Dim Arrayabc You're not declaring an array, you're declaring a single variable, type Variant. Now, later you can use Redim ArrayABC(30) and change the variable to an array. Or, curiously enough: ArrayABC = Split(The big brown dog jumped over the lazy fox, ) will Redim ArrayABC to whatever array size is required to store the string. (in this case, Ubound(ArrayABC) will be 8) If you were to use: Dim ArrayABC Dim ArrayDEF() Dim ArrayXYZ(3) . . . Redim ArrayABC(30) Redim ArrayDEF(30) Redim ArrayXYZ(30) ' Will not work, because the array was already defined. So, depending on how you plan to use it, you can either have the () or not. However, if you choose to use the (), you MUST (in some manner) define the array size before you can use the array. However: You CAN change a single variant to an array, and back again: Dim A A = The Big Brown Dog A = Split(The big brown dog jumped over the lazy fox, ) Erase A A = Jumped over the Lazy Fox hope this isn't too confusing. Paul From: hanumant shinde hanumant_5...@yahoo.co.in To: excel macros excel-macros@googlegroups.com Sent: Sat, March 5, 2011 2:31:03 PM Subject: $$Excel-Macros$$ ( ) for Array Must??? Hi friends, when we declare array is it must to give () i mean parantheses. can i declare array as dim arrayabc or i have to say dim arrayabc() i know that if we dont write any value in () its ok. but i am not sure if we can declare array without () -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ 22 days left - Launch of discussexcel.com
Hi Ayush, Perhaps you could play with the word 'Accelerate' Some suggestions would be: Excelerated Learning Excelerated Solutions Excelerating.com Exlceleration.com Excelerate.com Regards - Dave. From: jainayus...@gmail.com Date: Mon, 28 Feb 2011 11:07:31 +0530 Subject: Re: $$Excel-Macros$$ 22 days left - Launch of discussexcel.com To: excel-macros@googlegroups.com Thanks Akash, This is pity cool and short... I liked it. On Mon, Feb 28, 2011 at 10:42 AM, Akash $urkatha akash4...@yahoo.com wrote: Hey Ayush, What About 'SIMPLEXCEL'??? Regards, Aakash. --- On Mon, 2/28/11, Ayush Jain jainayus...@gmail.com wrote: From: Ayush Jain jainayus...@gmail.com Subject: Re: $$Excel-Macros$$ 22 days left - Launch of discussexcel.com To: excel-macros@googlegroups.com Cc: Dilip Pandey dilipan...@gmail.com Date: Monday, February 28, 2011, 10:12 AM Thank You Dilip...Awesome Tag Lines. I have shortlisted two taglines from all : 1) Excel Simplified 2) Helping the world to Excel in their professional life Group, Its not finalized, so you can submit more. Regards Ayush Jain On Sun, Feb 27, 2011 at 11:13 PM, Dilip Pandey dilipan...@gmail.com wrote: Hi Ayush, Few Taglines from my side:- Helping the world to Excel in their professional life Excelling in Excel Excel becomes Easy here..!! Best Regards, DILIPandey On 2/22/11, Ayush Jain jainayus...@gmail.com wrote: Dear members, I am too excited to share that discussexcel.com website development is in good progress and we are ready to launch the website on 4th Anniversary of our Google group. i.e. 16th -17th March. In the meantime, Can you advise a good tagline, punchline for website. My thoughts are : 1) building excel-lence 2) Excellence in Excel 3) Let's discuss and learn excel what do you think, Let me know Keep watching this space for more Regards Ayush Jain Group Manager. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Best regards, Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Best regards, Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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
RE: $$Excel-Macros$$ Help, please, with debugging a function
Hi, The logic appears to be a bit weird: If Not IsEmpty(rCell) Or HasDependents(rCell) = True Then Range(rCell.Address).Select Exit Sub End If If the first cell in the selected range is not empty, or has dependents, then that cell is selected, and the sub exits. What do you actually want the macro to do with what it finds? Regards - Dave. Sub Whats_In_Range() Dim rCell As Range For Each rCell In Selection.Cells If Not IsEmpty(rCell) Or HasDependents(rCell) = True Then Range(rCell.Address).Select Exit Sub End If Next rCell MsgBox Nothing found. End Sub Function HasDependents(ByRef CellPassedIn As Range) As Boolean Dim objRng As Range On Error Resume Next Set objRng = CellPassedIn.Dependents On Error GoTo 0 HasDependents = Not objRng Is Nothing End Function -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Refresh cell formula using VBA
Hi Aduh, I assume that you have the Calculation set to Manual. One way would be to use a selection change event that detects if the selected cell has a formula in it, then calculates just that cell. The following code goes into the VBA sheet window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count = 1 And Target.HasFormula = True Then Target.Calculate End Sub Clicking in any cell will updaye it if it has a formula. Let me know if I have understood you correctly. Regards - Dave. Date: Thu, 17 Feb 2011 12:52:37 +0700 From: squall.l...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Refresh cell formula using VBA Hi experts, Please help to solve my problem regarding refreshing cell containing formula/function using button (vba). My apologize if someone has already post the same problem before, but believe me it's my best attempt to search it beforehand to avoid double post ;) What I need is a method or code in VBA to seemlessly F2 the cell and hitting Enter Button to refresh my cell. Please find the attached example of my request. Thanks in advance, ~Aduh~ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Refresh cell formula using VBA
Hi, Sorry - didn't see your attachment. Link your button to the following macro, which can just live in a module: Sub refreshRange() Range(B40:B54).Calculate End Sub Alternatively, you could do away with the button and have the macro fire whenever you change B39. This macro would live in the VBA sheet window: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $B$39 Then For Each c In Range(B40:B54) If c.HasFormula = True Then c.Calculate Next c End If End Sub Regards - Dave Date: Thu, 17 Feb 2011 12:52:37 +0700 From: squall.l...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Refresh cell formula using VBA Hi experts, Please help to solve my problem regarding refreshing cell containing formula/function using button (vba). My apologize if someone has already post the same problem before, but believe me it's my best attempt to search it beforehand to avoid double post ;) What I need is a method or code in VBA to seemlessly F2 the cell and hitting Enter Button to refresh my cell. Please find the attached example of my request. Thanks in advance, ~Aduh~ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Import .csv match records
I think someone is using my name in vain - unless there are two Dave's in the world - and I don't know how that could be... Dave. Date: Thu, 17 Feb 2011 09:15:24 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Import .csv match records To: excel-macros@googlegroups.com Piece of cake... I can probably write it in 20 minutes. = Option Explicit Dim Col_Array_Code1, Col_Array_Code2, Col_Array_YR, Col_Array_MM Dim Col_Array_CoName, Col_Array_Dept, Col_Array_Qty, Col_Array_Amt Dim Code1, Code2, YR, MM Dim CoName, Dept, Qty, Amt Dim fso Public Const ForReading = 1, ForWriting = 2, ForAppending = 3 Sub ReadData() Dim CSVFile, R, f, str, StrArray CSVFile = P:\DAM\WI_JAN_2011.csv Set_Defaults Set fso = CreateObject(Scripting.FileSystemObject) Set f = fso.OpenTextFile(CSVFile, ForReading) Do While Not f.atendofstream RecCnt = RecCnt + 1 If (RecCnt Mod 100 = 0) Then Application.StatusBar = Searching Commercial Archives for: MatNo : RecCnt str = f.readline StrArray = Split(str, ,) Code1 = Trim(StrArray(Col_Array_Code1)) Code2 = Trim(StrArray(Col_Array_Code2)) YR = Trim(StrArray(Col_Array_YR)) MM = Trim(StrArray(Col_Array_MM)) CoName = Trim(StrArray(Col_Array_CoName)) Dept = Trim(StrArray(Col_Array_Dept)) Qty = Trim(StrArray(Col_Array_Qty)) Amt = Trim(StrArray(Col_Array_Amt)) Loop End Sub Sub Set_Defaults() Col_Array_Code1 = 0 Col_Array_Code2 = 1 Col_Array_YR = 2 Col_Array_MM = 3 Col_Array_CoName = 4 Col_Array_Dept = 5 Col_Array_Qty = 6 Col_Array_Amt = 7 End Sub === However, something you said makes me think it might not be as easy to PLACE the data. In the sheet for each month, you have 200 accounts and the combination of Code1 Code2 defines a record. You want to read the .csv file and copy the records to the appropriate accounts... That implies that the sheets in the monthly_totals workbook have a specific layout that you want to maintain. I need to know what that layout is so that I can find the appropriate account. I think I'm going to need a copy of the Monthly_totals.xls workbook. and, it would help if I had one or more of the .csv files. Otherwise, I have to spend more time making up fake data than actually writing code! Paul From: Dave davidstev...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, February 17, 2011 9:32:15 AM Subject: $$Excel-Macros$$ Import .csv match records I am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one for every month of the year January, February … December.. This file is called montly_totals.xls and is located in P:\DAM\WI_FTP. There are a total of 200 accounts set up in each of these monthly tabs. Each of these accounts starts with a code in A1 B1 . These are 4 character codes like below: They are sorted by Code 1. Code 1 + Code 2 uniquely identify a record. Code1 Code2 0845MTRA 1016 1017LEGL 16051605 1605OSLA 0001 0002 Every month I download a comma delimited file named as WI_JAN_2011, WI_FEB_2011 and so on till WI_DEC_2011. This file is located in P: \DAM \WI_FTP This file may contain 10 to 150 accounts with updated information for that specific month. They also have the same codes as above. The format of the comma delimited file is : Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount 0010,,11,01,Iron works,Grills , 00130,003448635, (they are all on one line) Currently every month I have to open each monthly tab and manually copy and paste the correct Information from the comma delimited file into the exact columns in my montly_totals.xls spreadsheet. The columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount I want a macro that would read this comma delimited file and insert these new monthly updated values to the correct account numbers. I was thinking that it could key on Code1 Code2 fields. Once there is a match between the codes i.e Code1+Code 2 in the montly_totals spreadsheet and the comma delimited file than the macro could insert the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) for that match in the columns C, D,E,F,G, going dow the rows. If there is no match than a new record should be inserted maintaing the sort order. Thanks in advance, Dave -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA
RE: $$Excel-Macros$$ A little OT
Hi Paul, Thanks for the detailed info. Not too wordy at all. I hadn't realised that there were 2 different syntaxes (syntii?) - Sheets(1) and Sheet1. That makes sense now. So then Sheets(1).Name will always return something, (though not always what you'd expect) because it is just the first sheet in the sheets array. But Sheet1.Name may produce an error, because Sheet1 may have been deleted. The code line that I quoted (Sheets(Sheets.Count).Select) always works because it is using the sheets array, not the sheet name or the sheet object. Thanks again. All very valuable knowledge. Regards - Dave. Date: Tue, 15 Feb 2011 05:00:59 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ A little OT To: excel-macros@googlegroups.com 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
$$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 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 at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link
RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns
Hi Paul, I'm really interested in the part of the macro that I have highlighted below (in case the highlighting doesn't travel well, I've marked each line with a *) I've not seen the 'Split' function before. I looked it up in the Help, but still couldn't make sense of it. If you have time, could you please explain that part? Regards - Dave. Date: Fri, 11 Feb 2011 06:15:45 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns To: excel-macros@googlegroups.com Curious... Actually, the Activecell object is not necessary. I changed it to: If Range(Ai) = then exit sub else and it worked just fine. BTW: I changed the loop to: Application.ScreenUpdating = False For R = 1 To 1000 If (Range(A R).Value = ) Then Exit Sub *StrArray = Split(Range(A R).Value, ;) *For C = 0 To UBound(StrArray) *Cells(R, C + 1).Value = StrArray(C) Next C Next R Application.ScreenUpdating = True and it worked almost instantaneously instead of all the screen flashing that goes on with TextToColumns. Paul From: Jorge Marques leote.w...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, February 10, 2011 6:46:37 AM Subject: $$Excel-Macros$$ Macro issue passing values from Cell to columns Hi, i have a macro i´ve done, but it it has a problem, when it passes the data from cells A to various columns it stops no cell 20 in the sheet2 e the another stops at another cell number, i have the file attached here with the code below, can´t seem to discover the error, taking baby steps at this!i think the issue is on the if statement, but if i take it out, it does the macro well but indicates error in the end! Sub Macro2() ' ' Macro2 Macro ' ' Keyboard Shortcut: Ctrl+p ' Dim i As Double i = 1 While ActiveSheet.Select If ActiveCell.Range(A i) = Then Exit Sub Else Range(A i).Select Selection.TextToColumns Destination:=Range(A i), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :=;, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ TrailingMinusNumbers:=True i = i + 1 Wend End Sub Thank you very much for your 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Macro issue passing values from Cell to columns
Hi Paul, Very helpful and clearly explained, thank-you. Can we use this, or something similar, to load a range (say A1:A20) into an array without using a loop? Sorry, but my knowledge of arrays in VBA is very limited. Regards - Dave. Date: Sat, 12 Feb 2011 09:20:50 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns To: excel-macros@googlegroups.com It's like the text-to-columns in Excel 2007. If you have a string: The Quick Brown Fox jumped over the lazy dog and you wanted to store the words into an array, If you're using Option Explicit (recommended) you have to declare the variable: Dim StrArray (but don't define an array SIZE) Next, split the string using a space ( ) as delimeter: StrArray = Split(The Quick Brown Fox jumped over the lazy dog, ) or: Str = (The Quick Brown Fox jumped over the lazy dog StrArray = Split(Str, ) The array is now: StrArray(0) = The Strarray(1) = Quick Strarray(2) = Brown Strarray(3) = Fox Strarray(4) = jumped Strarray(5) = over Strarray(6) = the Strarray(7) = lazy Strarray(8) = dog and of course ubound(StrArray) gives the upper bound of the array (8). I wrote a function LONG ago using this to accomplish what is now done with txt-to-columns in Excel2007! hope this helps, Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Sat, February 12, 2011 3:17:14 AM Subject: RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns Hi Paul, I'm really interested in the part of the macro that I have highlighted below (in case the highlighting doesn't travel well, I've marked each line with a *) I've not seen the 'Split' function before. I looked it up in the Help, but still couldn't make sense of it. If you have time, could you please explain that part? Regards - Dave. Date: Fri, 11 Feb 2011 06:15:45 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns To: excel-macros@googlegroups.com Curious... Actually, the Activecell object is not necessary. I changed it to: If Range(Ai) = then exit sub else and it worked just fine. BTW: I changed the loop to: Application.ScreenUpdating = False For R = 1 To 1000 If (Range(A R).Value = ) Then Exit Sub *StrArray = Split(Range(A R).Value, ;) *For C = 0 To UBound(StrArray) *Cells(R, C + 1).Value = StrArray(C) Next C Next R Application.ScreenUpdating = True and it worked almost instantaneously instead of all the screen flashing that goes on with TextToColumns. Paul From: Jorge Marques leote.w...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, February 10, 2011 6:46:37 AM Subject: $$Excel-Macros$$ Macro issue passing values from Cell to columns Hi, i have a macro i´ve done, but it it has a problem, when it passes the data from cells A to various columns it stops no cell 20 in the sheet2 e the another stops at another cell number, i have the file attached here with the code below, can´t seem to discover the error, taking baby steps at this!i think the issue is on the if statement, but if i take it out, it does the macro well but indicates error in the end! Sub Macro2() ' ' Macro2 Macro ' ' Keyboard Shortcut: Ctrl+p ' Dim i As Double i = 1 While ActiveSheet.Select If ActiveCell.Range(A i) = Then Exit Sub Else Range(A i).Select Selection.TextToColumns Destination:=Range(A i), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :=;, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ TrailingMinusNumbers:=True i = i + 1 Wend End Sub Thank you very much for your 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email
RE: $$Excel-Macros$$ Button programming
Hi, If you want the same functionality without macros, you'll have to remove all the buttons and replace them with cells containing hyperlinks. Let us know if you need assistance with this. Regards - Dave Date: Fri, 11 Feb 2011 20:18:55 +0500 Subject: $$Excel-Macros$$ Button programming From: azherbashirah...@gmail.com To: excel-macros@googlegroups.com Hi All, Please find the attached file need your help it can be open on every computer even computer has enable or disable micro i am unable to create such type of files please some one guide how i can create my own -- Regards Azher Bashir Ahmad -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Thanks Ashish, Works great. Regards - Dave. Date: Wed, 9 Feb 2011 10:32:26 +0530 Subject: Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64 From: koul.ash...@gmail.com To: excel-macros@googlegroups.com try this On Wed, Feb 9, 2011 at 10:27 AM, ashish koul koul.ash...@gmail.com wrote: @ dave Try this Wed, Feb 9, 2011 at 9:55 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Ashish, Thanks for sending the file. Excellent. But for some reason, selecting a cell with the left mouse button isn't recognised. I tried adding Rob's code, but it didn't work properly either. It only worked when using the left mouse button to 'OK' a previous message box. Regards - Dave Date: Wed, 9 Feb 2011 09:03:03 +0530 Subject: Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64 From: koul.ash...@gmail.com To: excel-macros@googlegroups.com hi dave, see if this helps On Wed, Feb 9, 2011 at 5:52 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Rob, I am interested in the trapping of a left mouse click, but couldn't understand it from your posts. Could you please attach a workbook with working code? Regards - Dave. Date: Tue, 8 Feb 2011 05:14:53 -0800 Subject: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64 From: robert.a.cama...@gmail.com To: excel-macros@googlegroups.com solved it: I had to add a bit mask (i think thats what its called). This returned true when I clicked on a cell: If CBool(GetAsyncKeyState(1) 128) Then MsgBox (left click trapped) End If (1) is the constant for left mouse click On Feb 7, 11:28 am, rcamarda robert.a.cama...@gmail.com wrote: Hi, I am trying to trap a left mouse click. I found lots of uses of GetAsyncKeyState, however, even something simple is not working. Private Declare Function GetAsyncKeyState Lib user32.dll (ByVal vKey As Long) As Integer Private Sub Worksheet_SelectionChange(ByVal Target As Range) If GetAsyncKeyState(VK_LBUTTON) Then MsgBox (left click trapped) End If If GetAsyncKeyState(VK_BACK) Then MsgBox (back space) End If If GetAsyncKeyState(VK_RETURN) Then MsgBox (enter key) End If End Sub Should I use a different library when using x64 or Office 2010?? TIA Rob -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 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$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Hi Rob, I am interested in the trapping of a left mouse click, but couldn't understand it from your posts. Could you please attach a workbook with working code? Regards - Dave. Date: Tue, 8 Feb 2011 05:14:53 -0800 Subject: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64 From: robert.a.cama...@gmail.com To: excel-macros@googlegroups.com solved it: I had to add a bit mask (i think thats what its called). This returned true when I clicked on a cell: If CBool(GetAsyncKeyState(1) 128) Then MsgBox (left click trapped) End If (1) is the constant for left mouse click On Feb 7, 11:28 am, rcamarda robert.a.cama...@gmail.com wrote: Hi, I am trying to trap a left mouse click. I found lots of uses of GetAsyncKeyState, however, even something simple is not working. Private Declare Function GetAsyncKeyState Lib user32.dll (ByVal vKey As Long) As Integer Private Sub Worksheet_SelectionChange(ByVal Target As Range) If GetAsyncKeyState(VK_LBUTTON) Then MsgBox (left click trapped) End If If GetAsyncKeyState(VK_BACK) Then MsgBox (back space) End If If GetAsyncKeyState(VK_RETURN) Then MsgBox (enter key) End If End Sub Should I use a different library when using x64 or Office 2010?? TIA Rob -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ HOW TO CONVERT THE AMOUNT IN TEXT
You're welcome, Regards - Dave From: rohan.j...@gmail.com Date: Mon, 7 Feb 2011 10:49:39 +0530 Subject: Re: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Hi Dave, wonderful its a great job Regards ROHAN On Sun, Feb 6, 2011 at 4:40 AM, Dave Bonallack davebonall...@hotmail.com wrote: You're welcome. Dave. Date: Sat, 5 Feb 2011 20:31:35 +0530 Subject: Re: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT From: rajeshkainikk...@gmail.com To: excel-macros@googlegroups.com Hi Dave Excellent work, I never thought that the conversion can be done by formula. Regards Rajesh Kainikkara On 2/5/11, Dave Bonallack davebonall...@hotmail.com wrote: Hi Solomon, Have a look at the attached. Most numbers-to-text conversions are done with VBA, but this one is done with formulas and functions. It uses a bunch of cells to do its workings. In your case I have put those cells in the range A50:K62. If you need that range for something else, then you'll have to move the whole block of cells to an unused part of the sheet. Hope this helps Regards - Dave. From: faithful1...@gmail.com Date: Sat, 5 Feb 2011 08:52:55 +0400 Subject: Fwd: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Can someone define formula because I dont know macros. Regards, Solomon -- Forwarded message -- From: solomon raju faithful1...@gmail.com Date: Thu, Feb 3, 2011 at 3:14 PM Subject: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Hi All, Please find the attached excel file and give me some formula to convert the amount in text. Regards, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT
Hi Solomon, Have a look at the attached. Most numbers-to-text conversions are done with VBA, but this one is done with formulas and functions. It uses a bunch of cells to do its workings. In your case I have put those cells in the range A50:K62. If you need that range for something else, then you'll have to move the whole block of cells to an unused part of the sheet. Hope this helps Regards - Dave. From: faithful1...@gmail.com Date: Sat, 5 Feb 2011 08:52:55 +0400 Subject: Fwd: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Can someone define formula because I dont know macros. Regards, Solomon -- Forwarded message -- From: solomon raju faithful1...@gmail.com Date: Thu, Feb 3, 2011 at 3:14 PM Subject: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Hi All, Please find the attached excel file and give me some formula to convert the amount in text. Regards, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 Expenses Claim Form(1).xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT
You're welcome. Dave. Date: Sat, 5 Feb 2011 20:31:35 +0530 Subject: Re: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT From: rajeshkainikk...@gmail.com To: excel-macros@googlegroups.com Hi Dave Excellent work, I never thought that the conversion can be done by formula. Regards Rajesh Kainikkara On 2/5/11, Dave Bonallack davebonall...@hotmail.com wrote: Hi Solomon, Have a look at the attached. Most numbers-to-text conversions are done with VBA, but this one is done with formulas and functions. It uses a bunch of cells to do its workings. In your case I have put those cells in the range A50:K62. If you need that range for something else, then you'll have to move the whole block of cells to an unused part of the sheet. Hope this helps Regards - Dave. From: faithful1...@gmail.com Date: Sat, 5 Feb 2011 08:52:55 +0400 Subject: Fwd: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Can someone define formula because I dont know macros. Regards, Solomon -- Forwarded message -- From: solomon raju faithful1...@gmail.com Date: Thu, Feb 3, 2011 at 3:14 PM Subject: $$Excel-Macros$$ HOW TO CONVERT THE AMOUNT IN TEXT To: excel-macros@googlegroups.com Hi All, Please find the attached excel file and give me some formula to convert the amount in text. Regards, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA
Hi, I think that mm will return 02, while m will return 2 Regards - Dave From: anil.bha...@tatacommunications.com To: excel-macros@googlegroups.com Date: Thu, 3 Feb 2011 14:02:16 +0530 Subject: RE: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA Hi Aamir, Please see the difference in formatting of text. Mmmm will give you February Mmm will give you Feb Mm will give you 2 Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Aamir Shahzad Sent: Wednesday, February 02, 2011 11:24 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA Dear Ashish, why use and why use 4 times. And please tell other Format text Regards, Aamir Shahzad On Wed, Feb 2, 2011 at 3:53 PM, ashish koul koul.ash...@gmail.com wrote: type this in cell f5 =TEXT(E5,) On Wed, Feb 2, 2011 at 1:09 PM, solomon raju faithful1...@gmail.com wrote: Hi All, How can we extract month in text. Can someone define some formula for this. Attached my question in clear. Regards, Solomon -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Lookup values in different sheets
Hi, IFERROR can be stacked, just like IF can be. Try this formula in C4, then copy down: =IFERROR(VLOOKUP(B4,$E$3:$F$3,2,0),IFERROR(VLOOKUP(B4,$H$3:$I$3,2,0),VLOOKUP(B4,$K$3:$L$3,2,0))) Regards - Dave Date: Tue, 25 Jan 2011 22:57:34 +0500 Subject: $$Excel-Macros$$ Lookup values in different sheets From: aamirshahza...@gmail.com To: excel-macros@googlegroups.com Hi experts, Can I find values in multiple sheets with vlookup function. sheet is attached. iferror function can be use but it can helpful only with 2 sheet, any guidance? 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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: Concatenate Value Problem
Hi Manish, Please attach the solution you have. Perhaps one of us may be able to speed it up. Regards - Dave Date: Tue, 18 Jan 2011 23:24:56 -0800 Subject: Re: $$Excel-Macros$$ Re: Concatenate Value Problem From: pansari.man...@gmail.com To: excel-macros@googlegroups.com Any Help Pls.. - Manish On Jan 18, 5:42 pm, Manish pansari.man...@gmail.com wrote: Thanks Manoj. It’s really what I was looking for. But it is taking too much time to process the result. Is it possible to make it faster ?? My excel getting stuck/Not Responding, while I change/add the data or copy the formula in another cell. Pls suggest. Thanks in advance. Regds, Manish Pansari On Jan 17, 12:28 pm, Manoj kumar manoj.vishwakarma...@gmail.com wrote: Hey Manish, Hope this might help you Cheers, Manoj Kumar On Mon, Jan 17, 2011 at 10:43 AM, Manish pansari.man...@gmail.com wrote: is it possible or not??? :( any help pls. - Manish Pansari On Jan 14, 11:21 am, Manish pansari.man...@gmail.com wrote: Dear Group Friends, I have two work sheet. Sheet1 contain bill number in column B, and sheet2 contain bill number and PRA No in column A and column B. My problem is, I want to concatenate the PRA No. with , for every unique bill Number. e.g. Sheet1 date are as Under: Bill No. FDN/01 FDN/02 FDN/03 FDN/04 Sheet2 data: Bill No. PRA No. FDN/01 M-01 FDN/03 M-02 FDN/02 M-03 FDN/04 M-04 FDN/01 M-05 FDN/02 M-06 FDN/01 M-07 FDN/04 M-08 FDN/02 M-09 FDN/03 M-10 Result required in Sheet1 Bill No. PRA Nos. FDN/01 M-01, M-05, M-07 FDN/02 M-03, M-06, M-09 FDN/03 M-02, M-10 FDN/04 M-04, M-08 I know the concatenate formula. but for that i have to apply it manually. Please assist me, how can i make it automatic. When I entered new PRA No. in sheet2, Sheet1 PRA Nos. get automatically update. Thanks for your regular help and suggestion. Regds,ManishPansari -- ------ Some important links for excel users: 1. Follow us on TWITTER 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 GG - Concatenate All Values_Manoj.xlsm 18KViewDownload- 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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
Hi Aamir, Have a look at the attached. Regards - Dave. Date: Tue, 18 Jan 2011 23:28:27 +0500 Subject: Re: $$Excel-Macros$$ Query From: aamirshahza...@gmail.com To: excel-macros@googlegroups.com Thanks for reply, now attached sheet is very clear. Aamir Shahzad On Tue, Jan 18, 2011 at 10:10 AM, ashish koul koul.ash...@gmail.com wrote: check the attachment see if it helps you On Tue, Jan 18, 2011 at 10:36 AM, Dave Bonallack davebonall...@hotmail.com wrote: Sorry Aamir, Can't understand the question. If your worksheet had a cell with the desired answer in it (entered manually) this may help me understand your request. Regards - Dave. From: aamirshahza...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Query Date: Mon, 17 Jan 2011 23:31:49 +0500 Dear experts, How to resolved attached query, can anybody tell formula not macro. 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel QUERY(1).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Query
Sorry Aamir, Can't understand the question. If your worksheet had a cell with the desired answer in it (entered manually) this may help me understand your request. Regards - Dave. From: aamirshahza...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Query Date: Mon, 17 Jan 2011 23:31:49 +0500 Dear experts, How to resolved attached query, can anybody tell formula not macro. 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ For Herman Gold
Hi Herman, Have a look at the attached. I've provided a solution that doesn't use helper columns, so the formula is a bit bulky. Copy it down as far as you need. If you have a very large data base, helper columns would probably make the calculation time shorter. Hope this helps Regards - Dave Date: Sat, 15 Jan 2011 18:54:57 +0530 Subject: Re: $$Excel-Macros$$ there has to be an easier way! From: vikask...@gmail.com To: excel-macros@googlegroups.com please provide data in excel.. On Sat, Jan 15, 2011 at 6:25 PM, herman gold gold.her...@gmail.com wrote: a1 has IN, b1 has date, and c1 has time a2 has OUT, b2 has date, and c2 has time On Jan 15, 12:49 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi, You need to tell us how this data is entered. Is each line 1 cell, 2 cells, or 3 cells? Regards - Dave Date: Fri, 14 Jan 2011 23:11:40 -0800 Subject: $$Excel-Macros$$ there has to be an easier way! From: gold.her...@gmail.com To: excel-macros@googlegroups.com Hi, my timecard dumps an excel file with IN 9/10/2010 8:50 OUT 9/10/2010 13:04 IN 9/10/2010 13:04 OUT 9/10/2010 17:08 IN 9/23/2010 9:08 OUT 9/23/2010 13:02 IN 9/24/2010 9:09 OUT 9/24/2010 13:08 IN 9/24/2010 13:08 OUT 9/24/2010 17:06 IN 9/30/2010 8:58 OUT 9/30/2010 13:00 Is there a way to show me the total time worked for each day? -- --- --- Some important links for excel users: 1. Follow us on TWITTER 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- One Team One Dream One Goal Warm Regards, Vikas Chauhan E-Mail :- vikask...@gmail.com,vikask...@rediffmail.com, 9911868518, We can't Spell S_ccess without U Life is Very beautiful !!! ¨`•.•´¨) Always `•.¸(¨`•.•´¨) Keep (¨`•.•´¨)¸.•´ Smiling! `•.¸.•´. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 Book21.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ there has to be an easier way!
Hi, You need to tell us how this data is entered. Is each line 1 cell, 2 cells, or 3 cells? Regards - Dave Date: Fri, 14 Jan 2011 23:11:40 -0800 Subject: $$Excel-Macros$$ there has to be an easier way! From: gold.her...@gmail.com To: excel-macros@googlegroups.com Hi, my timecard dumps an excel file with IN 9/10/2010 8:50 OUT 9/10/2010 13:04 IN 9/10/2010 13:04 OUT 9/10/2010 17:08 IN 9/23/2010 9:08 OUT 9/23/2010 13:02 IN 9/24/2010 9:09 OUT 9/24/2010 13:08 IN 9/24/2010 13:08 OUT 9/24/2010 17:06 IN 9/30/2010 8:58 OUT 9/30/2010 13:00 Is there a way to show me the total time worked for each day? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Formula of the week - share your best formula
Hi, The problem is not the sheet names, but the indirect refering to a date, which is really just a date code, not a date as displayed. Regards - Dave. Date: Thu, 13 Jan 2011 23:08:24 -0800 Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula From: noorain.ans...@gmail.com To: excel-macros@googlegroups.com CC: koul.ash...@gmail.com Dear Ashish, I am unable to find data through Vlookup+indirect when sheet name is Date wise like 14-Jan,15-Jan,16-Jan. Please see attached sheet.. -- Thanks regards, Noorain Ansari On Thu, Jan 13, 2011 at 9:49 PM, ashish koul koul.ash...@gmail.com wrote: hi 1 case sensitive lookup using index and match 2 vlookup indirect function Check the attachment. Given you the situations where I used them in the past On Thu, Jan 13, 2011 at 11:15 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Hi All, I suggest all of you don't say this function I like and then this one like that. Just sharing the function name won't help others to gain some knowledge. All the functions which is available in excel is having the same set of qualities unless it is used in perfect combination. So give brief about the function and its different type of usages which you came across in your past. Hope this makes sense. --- Ms.Exl.Learner --- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Formula of the week - share your best formula
Hi Noorian,
One option is to format the reference cell (with the date in it) as text, then
type in the date again, exactly as it appears on the appropriate sheet tab. You
also need to add single quotes around the sheet name, so the indirect function
looks like this:
INDIRECT('B6'!A:B) Without the single quotes, I think Excel reads the
dash as a subtraction sign.
I've attached your sheet with these modifications.
As to your question regarding case-sensitive VLOOKUP, I have given a slightly
shorter, non-array alternative using LOOKUP for you to have a look at.
Regards - Dave
Date: Fri, 14 Jan 2011 01:53:41 -0800
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula
From: noorain.ans...@gmail.com
To: excel-macros@googlegroups.com
Hi Dave,
How can solve it. Please give ur valauble suggestion..
Thanks,
Noorain Ansari
On Fri, Jan 14, 2011 at 1:34 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
The problem is not the sheet names, but the indirect refering to a date, which
is really just a date code, not a date as displayed.
Regards - Dave.
Date: Thu, 13 Jan 2011 23:08:24 -0800
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula
From: noorain.ans...@gmail.com
To: excel-macros@googlegroups.com
CC: koul.ash...@gmail.com
Dear Ashish,
I am unable to find data through Vlookup+indirect when sheet name is Date wise
like 14-Jan,15-Jan,16-Jan.
Please see attached sheet..
--
Thanks regards,
Noorain Ansari
On Thu, Jan 13, 2011 at 9:49 PM, ashish koul koul.ash...@gmail.com wrote:
hi
1 case sensitive lookup using index and match
2 vlookup indirect function
Check the attachment. Given you the situations where I used them in the past
On Thu, Jan 13, 2011 at 11:15 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com
wrote:
Hi All,
I suggest all of you don't say this function I like and then this one like
that. Just sharing the function name won't help others to gain some knowledge.
All the functions which is available in excel is having the same set of
qualities unless it is used in perfect combination.
So give brief about the function and its different type of usages which you
came across in your past.
Hope this makes sense.
---
Ms.Exl.Learner
---
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post 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
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Thanks regards,
Noorain
$$Excel-Macros$$ Formula
Hi, I would like to submit the following formula as one I like. It performs a case-sensitive Vlookup, and is non-array. =LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B) A1 contains the lookup value Sheet2!A:A is the lookup column Sheet2!B:B is the return column I hasten to add that this formula is not my own, but is a very neat solution to a common problem. Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Formula
Hi John, I opened your file, but don't get a #NUM error. I get '98' as expected. I tried it in XL 2007 and 2003. Regards - Dave. Date: Fri, 14 Jan 2011 14:28:41 -0500 Subject: Re: $$Excel-Macros$$ Formula From: johnasmit...@gmail.com To: excel-macros@googlegroups.com Dave, Why am I getting #NUM! with the formula? (See Attached) Thank you. John On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, I would like to submit the following formula as one I like. It performs a case-sensitive Vlookup, and is non-array. =LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B) A1 contains the lookup value Sheet2!A:A is the lookup column Sheet2!B:B is the return column I hasten to add that this formula is not my own, but is a very neat solution to a common problem. Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Same code, different dates
Hi David, Sounds like your spreadsheets are using different calendars systems. Excel can use the 1900 system or the 1904 system. 1900 is the default setting. In XL2003, go to Tools, Options, Calculations Tab. There make sure that the 1904 thingy is unchecked. Do the same for both workbooks. In XL2007, it's in Excel Options, Advanced Tab, down near the bottom. Hope this helps. Regards - Dave Date: Fri, 14 Jan 2011 12:02:21 -0800 Subject: $$Excel-Macros$$ Same code, different dates From: davidmstu...@gmail.com To: excel-macros@googlegroups.com Hello everyone, I have 2 spreadsheets. One with raw data, the other is the one i need the data in. When i copy the data across the dates change. In the original spreadsheet, the data code is 40556, and when viewed as a short date it is converted to 1/13/2011. However, the in the second spreadsheet the same cell has the same code in it, 40556, but when viewed in the short date format it is 1/14/2015 !!! This every annoying as the date is crucial to my work in the second spreadsheet. Can anyone explain what is going on and how to ensure the dates remain consistent? David -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Formula of the week - share your best formula
Hi Ayush, There seems to be some confusion. I thought the question related to our favourite formula, not favourite function. Please clarify. Regards - Dave. From: rohan.j...@gmail.com Date: Wed, 12 Jan 2011 16:01:19 +0530 Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula To: excel-macros@googlegroups.com CC: jainayus...@gmail.com Hi Ayush Ji, My formulas are CHOOSE - we can rank the candidates with choose code combination CHOOSE(CODE(B1)-64,Excellent,Best,Poor), if B1has the value A, B, or C. CELL(FILENAME,A1) - we can get instant result of the path of the current file with sheet name. REPT - This one give us a clear understanding of status Thanks Regards ROHAN 9818247278, 8860567680 On Wed, Jan 12, 2011 at 3:25 PM, Dilip Pandey dilipan...@gmail.com wrote: Hi Ayush, One of my Best formula is OFFSET. Reason:- It can be used in getting the desired value (located) anywhere in the spreadhseet, the desired Sum, Average etc. It is also used for creating dynamics ranges - which further can be used in formulas / conditional formatting. Best Regards, DILIPandey On Tue, Jan 11, 2011 at 10:04 PM, Ayush jainayus...@gmail.com wrote: Dear Group, Let's discuss excel formula this week. Each of us use many formulas in our excel reports , dashboard metrics etc. Please share your best formula and the reason for being the best. If all of you will share atleast one formula, Then I hope to cover all formulas in excel. :D My one of the best formula is IFERROR()Because it helps to avoid iserror formula and make your overall formula shorter..Its available from Excel 2007 version and thereafter only. I appreciate your active participation especially from our Top posters : Dilip, Dave, Paul, Ashish Kaul and all. Thanks everyone. Thanks best regards, Ayush Jain Group Manager Microsoft MVP -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Re: Formula to add date
Hi, Not sure if I'm missing the point here, but what about just typing the date into your first cell, then press enter. From there, select your first cell and drag the copy-down handle down as far as you want. XL defaults to incrementing the date one day at a time. Regards - Dave. Date: Wed, 12 Jan 2011 19:34:05 -0800 Subject: $$Excel-Macros$$ Re: Formula to add date From: springer...@gmail.com To: excel-macros@googlegroups.com I am no whiz kid with Excel but use it all the time. I usually just put a date in a cell drop to the next one and add 1. A1 would be the date formated how I want, A2 would be =A1+1. I then copy and paste that down the column or acroos a row. I have one spreadsheet that covers the year. I just use a simple =A()+1 and then click on the last date from the previous sheet. Not real high tech and because the months have different #'s of days there is a little fiddling around to make it how you want it but it works pretty easily. I am sure there is some better way to do it, but that is the thing about excel, the more I learn, the more I realize how little I know. On Jan 12, 12:20 am, Susan susan.m.ander...@comcast.net wrote: Hi, I'm looking for a formula that will add all dates of the month. It will auto-populate for me the next day in the month. Column A 01/01/2011 01/02/2011 01/03/2011 Etc, etc I am creating templates at work that all 12 templates need dates according to the month for column A: 01/01/2011 to 01/31/2011. Instead of manually typing all of the dates in column A1 to A31. Is there a formula to put in column A1 that will start with 01/01/2011 and I drag it down to finish at 01/31/2011. I tried =DATE(year,month,day)+1. It didn't work. Thanks much! ~Susan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Linking spreadsheets using INDIRECT
Hi, If you are using xl2007 or more, you can use the IFERROR function. In your case, I think the syntax would go something like this: =IFERROR(INDIRECT(A1D$65)*1,Linked Workbook not open!) But I don't have XL2007 here to check it. Hope this helps. Regards - Dave. Date: Mon, 10 Jan 2011 02:08:37 -0800 Subject: $$Excel-Macros$$ Linking spreadsheets using INDIRECT From: johnharw...@arrakis.es To: excel-macros@googlegroups.com I have an Excel spreadsheet which compiles a summary report by linking to many spreadsheets, whose file names are computed according to the date, e.g. If Cell A1 contains '[Data_2010_December]Inputs'! the link formula might be = +INDIRECT(A1D$65)*1 That works fine provided that file Data_2010_December.xls is open; if it is not open the result is #REF! Is there a way of getting around this, so that the links are live and values are preserved? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ How to count distinct values on the base of multiple criteria
Hi Kalyan, The attached works, with the following limitations: That the data in Col A is always the same (as in your sample data) That any given BP Name is not repeated in more than 1 Depot (as in your sample data) If either of the above limitations are unacceptable, then it's back to the drawing board. Regards - Dave. Date: Thu, 6 Jan 2011 18:40:43 +0530 Subject: Re: $$Excel-Macros$$ How to count distinct values on the base of multiple criteria From: kalx...@gmail.com To: excel-macros@googlegroups.com CC: jainayus...@gmail.com; schreiner_p...@att.net exactly Ayush, I have already tried Paul's solution but I didn't get desired result. I am attaching the file again with expected result Please help Thanks in advance Kalyan On Thu, Jan 6, 2011 at 6:13 PM, Ayush jainayus...@gmail.com wrote: Right Paul, but your formula is counting duplicate values also.This user need to count only distinct values. correct ? Best Regards, Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts How to count only unique values(2).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Number sequentially, skipping blank cells
Hi, Have a look at the attached. I've started with just '1' in the first cell, then a formula after that, copied down to about row 30. Copy it down as far as you need. The appropriate sequential number will appear when you enter data into Col B cells. It is also self-correcting if you delete data from Col B. Regards - Dave Date: Thu, 6 Jan 2011 15:01:19 -0800 Subject: $$Excel-Macros$$ Number sequentially, skipping blank cells From: scoobysnac...@gmail.com To: excel-macros@googlegroups.com Does anyone have a formula or vbs to accomplish the sequential numbering as listed here? The rows with no information would get a nill and the next cell would not break the number sequence. 1 5t67 2 g786 3 1234r 4 ty765 5 24567u 6 78ytr89 7 56ytre 8 re45789 9 2ewqs78 10 45iyunhg -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts sequential.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Custom UDF
Hi, A UDF (User Defined Function) is like any other function. It can only return a value. It cannot do anything else. To change the background colour of a cell, use Conditional formatting or a regular macro. Regards - Dave. Date: Wed, 5 Jan 2011 06:50:53 +0530 Subject: Fwd: $$Excel-Macros$$ Custom UDF From: anjulav...@gmail.com To: excel-macros@googlegroups.com Can any one answer the below or only simple questions are answered here just to become MVP -- Forwarded message -- From: Anju anjulav...@gmail.com Date: Mon, Jan 3, 2011 at 12:44 PM Subject: $$Excel-Macros$$ Custom UDF To: excel-macros@googlegroups.com Hi Team, Can anyone give me a Custom UDF which changes the cells back ground color. I have read that UDF cannot change the physical characteristic of a cell...but are there any exceptions. Please Help 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ how to get month Occurrence in no.
Thanks! Not my own though. Learnt it on another group. Happy to pass it on. Regards - Dave Date: Tue, 28 Dec 2010 17:44:51 +0530 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: dilipan...@gmail.com To: excel-macros@googlegroups.com Impressive ..!! Good show Dave, Siti, STDEV :) Best Regards, DILIPandey On Tue, Dec 28, 2010 at 4:13 PM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, A shorter version would be: =MONTH(--(1A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word April or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE(1 A1 2010),M) On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young rohan.j...@gmail.com wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Nested IF functions?
Hi, To just do the 3 levels you asked about, try: =IF(B16500,3.5,IF(B161000,2.75,2.25)) Regards - Dave. Date: Tue, 28 Dec 2010 20:18:30 +0530 Subject: Re: $$Excel-Macros$$ Nested IF functions? From: dilipan...@gmail.com To: excel-macros@googlegroups.com Hi J D, á It would be really appreciable if you can share a test file on this.á Help us to help you :) á Best Regards, DILIPandey On Tue, Dec 28, 2010 at 7:36 AM, J D springer...@gmail.com wrote: I am a concrete estimator who is trying to create some cut and paste Estimate line items I can just grab from one spread sheet and paste in to my estimate. I was refining one and tried to use the IF function to pick the number of labor hours based on sq footage. áMore s.f. and we are more productive therefore less hours per s.f. I was trying to say if less that 500 s.f. then .035 hrs/s.f. á500 to 1,000 then .0275 hrs/s.f. 1,000 s.f. then .0225 hours/s.f. I could get one working but wanted to know how to get more going choises out of one formula. áAttached is the assembly and the formula below it. Form, place and finish ~ (__) s.f. of 4 broom swept finish concrete sidewalk. áConcrete to be 3,500 psi and reinforced with 6 x 6 , w1.4/ w1.4 wwf. á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á $4,410.00 á á á áArea á á990 á á s.f. á áPerimeter á á á 114 á á l.f. Concrete á á á á990 á á 0.33 á á1.03 á á336.501 12.463 á13 á á á145 á á 1885 Labor á 13 á á á0.4 á á 5.2 á á á á á á á á á á 5.2 á á 40 á á á208 wwf á á 990 á á 40 á á á24.75 á á á á á á á á á 25 á á á7 á á á 175 2 SB á 990 á á 0.26 á á257.4 á á á á á á á á á 260 á á 0.49 á á127.4 Labor á 990 á á 0.003 á 2.97 á á á á á á á á á á3 á á á 40 á á á120 Poly á á990 á á 1800 á á0.55 á á á á á á á á á á0.6 á á 85 á á á51 Labor á 990 á á 0.002 á 1.98 á á á á á á á á á á2 á á á 40 á á á80 Expansion á á á 990 á á 0.25 á á247.5 á 100 á á 2.475 á 2.5 á á 31.64 á 79.1 Labor á 250 á á 0.01 á á2.5 á á á á á á á á á á 2.5 á á 40 á á á100 Curing á640 á á 350 á á 1.828571429 á á 5 á á á 0.365714286 á á 0.4 á á 55 á á á22 Labor á 990 á á 0.003 á 2.97 á á á á á á á á á á3 á á á 40 á á á120 2 x 4 á 114 á á 16 á á á7.125 á á á á á á á á á 8 á á á 5.81 á á46.48 Labor á 114 á á 0.33 á á37.62 á 0.2 á á 7.524 á 7.6 á á 40 á á á304 Finish á990 á á 100 á á 9.9 á á 2.75 á á27.225 á27.3 á á40 á á á1092 á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á4409.98 =IF(B16500,3.5,2.75) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Thanks Regards, DILIP KUMAR PANDEYá á MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ how to get month Occurrence in no.
Hi, XL is always looking for the opportunity to convert something to a date. In my region, where we use day, month, year, and with A1 containing the word 'July': =--(1A1) XL reads this as the 1st day of July, then adds the current year as a default. It returns 40360, which if the cell is formatted as date, shows 1 July 2010 Interestingly, =--(A11) also works. XL reads this as July 2001, and provides a default day of 1, returning the same date as above. In either event, now that XL has been provided an actual date, we can apply the MONTH thingy, which then returns 7 Regards - Dave. Date: Wed, 29 Dec 2010 12:12:16 +0500 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: sajidmansooral...@gmail.com To: excel-macros@googlegroups.com Really Impressive! Let me know how this formula works On Tue, Dec 28, 2010 at 3:43 PM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, A shorter version would be: =MONTH(--(1A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word April or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE(1 A1 2010),M) On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young rohan.j...@gmail.com wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ how to get month Occurrence in no.
Hi, A shorter version would be: =MONTH(--(1A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word April or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE(1 A1 2010),M) On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young rohan.j...@gmail.com wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Reference sheet number in macro
Hi Susan,
Not sure if this is the problem, but I thought I'd mention it;
When using a sheet name, the syntax is:
Worksheets(Base Scenario) or just Sheets(Base Scenario)
But when using the sheet number, neither quotes, nor the word 'Sheet' are not
used inside the brackets.
So it just becomes:
Worksheets(12) or just Sheets(12)
Regards - Dave.
Date: Tue, 14 Dec 2010 08:44:10 -0800
Subject: $$Excel-Macros$$ Reference sheet number in macro
From: cow...@aol.com
To: excel-macros@googlegroups.com
Have a macro to refresh a formula, however, if the sheet named Base
Scenario gets renamed, It won't change the macro. If I replace
theBase Scenario reference in the macro's parenthesis with
Sheet12, (Sheet12 is its reference before the name change) it
creates a #REF error. Any thoughts? Thanks in advance for any ideas.
Pete
Sub RefreshFormulaInSummary_15()
Sheet11.Select
Range(N15) = =SUM('Base Scenario'!AC3403:AC3409)*1000
end sub
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Error
Hi Vebhav, If the adjacent column you want is column 7, then try: Selection.AutoFill Destination:=Range(Cells(2, 8), Cells(Cells(Rows.Count, 7).End(xlUp).Row, 8)) I don't have a sample sheet, so I can't test it. Let me know if it works. If the adjacent column is not column 7, then change the '7' in the code line for the appropriate number. Regards - Dave. Date: Mon, 13 Dec 2010 14:42:04 +0530 Subject: Re: $$Excel-Macros$$ Error From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com Dave, Before that i used the concatenate function, so i want to autofill upto the adjacent column. Thanks, Vebhav Jain On Mon, Dec 13, 2010 at 8:32 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Vebhav, You seem to be trying to autofill a range based on where the range currently ends. Are you sure you don't want to fill Col 8 as far down as data in an adjacent column? Regards - Dave. Date: Mon, 13 Dec 2010 07:40:24 +0530 Subject: $$Excel-Macros$$ Error From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com Hi All, Please help me out in rectifying the below error. Selection.AutoFill Destination:=Range(Cells(2, 8), Cells(Rows.Count, 8).End(xlUp)) Regards Vebhav Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ how to protect unprotect the sheet with same button
Hi, Try this. Sub Macro6() If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Else ActiveSheet.Protect End Sub Regards - Dave. Date: Wed, 8 Dec 2010 20:49:58 +0530 Subject: $$Excel-Macros$$ how to protect unprotect the sheet with same button From: rajeshkainikk...@gmail.com To: excel-macros@googlegroups.com Hi Experts i am want to give an edit button in my worksheet, for protecting unprotecting the sheet how can i use it. I try this code but not working,pls correct it Sub Macro6() If ActiveSheet.Protect = True Then ActiveSheet.Unprotect Else If ActiveSheet.Protect = False Then ActiveSheet.Protect End If End If 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ how to protect unprotect the sheet with same button
Hi Rajesh, You're welcome. Regards - Dave. Date: Thu, 9 Dec 2010 20:23:05 +0530 Subject: Re: $$Excel-Macros$$ how to protect unprotect the sheet with same button From: rajeshkainikk...@gmail.com To: excel-macros@googlegroups.com Hi Dave The code is working well, Thank u very much Regards Rajesh kainikkara On 12/9/10, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Try this. Sub Macro6() If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Else ActiveSheet.Protect End Sub Regards - Dave. Date: Wed, 8 Dec 2010 20:49:58 +0530 Subject: $$Excel-Macros$$ how to protect unprotect the sheet with same button From: rajeshkainikk...@gmail.com To: excel-macros@googlegroups.com Hi Experts i am want to give an edit button in my worksheet, for protecting unprotecting the sheet how can i use it. I try this code but not working,pls correct it Sub Macro6() If ActiveSheet.Protect = True Then ActiveSheet.Unprotect Else If ActiveSheet.Protect = False Then ActiveSheet.Protect End If End If 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Error in Macro Code
Hi Vebhav, Is there any data after the range you mention? For example, if today's data is Range A1:B330, is there any data in cells A331:B65000? If the cells below the data is blank, then we should be able to help you. Also, the other highlighted range, H2:H341; how does that relate to the first range, since it contains a different number of cells? Is it always 11 cells more than the range of data in Columns A B? Regards - Dave. Date: Wed, 8 Dec 2010 13:47:33 +0530 Subject: Re: $$Excel-Macros$$ Error in Macro Code From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com I want the highlighed code to be in a standard format. The data which i get daily it keep varies and i get struck at range(A1:B334). when i recorded this macro at that time it was A1 :B334 and next day it changed to A1:B321, Next day i ran the macro...not able to execute.i hope you understood my query. On Wed, Dec 8, 2010 at 9:45 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Vebhav, You say you need to change the highlighted code daily, but you don't say what you change it to, or on what basis it is changed. We need to know this if we have any chance of helping you automate the process. Regards - Dave. Date: Wed, 8 Dec 2010 06:51:01 +0530 Subject: Re: $$Excel-Macros$$ Error in Macro Code From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com Can someone look into this and suggest me. On Tue, Dec 7, 2010 at 10:33 PM, vebhav jain vebhav.j...@gmail.com wrote: Hello, Can you please help me in making this code in a standard format, instead of changing the code daily manually of the highlighted text. Thanks in Advance. Sub macro1() ' ' Macro1 Macro ' ' Dim Z As Long Range(H2).Select ActiveCell.FormulaR1C1 = =CONCATENATE(RC[-6],RC[-5],RC[-1]) Range(H2).Select Selection.AutoFill Destination:=Range(H2:H341) Columns(H:H).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns(B:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:B1).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Clear ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Add Key:=Columns( _ B:B), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets(kmhpmebck1).Sort .SetRange Range(A1:B334) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 Range(B3).Select Range(Selection, Selection.End(xlDown)).Select Selection.Font.Bold = False Selection.Replace What:=total, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns(B:B).Select Selection.TextToColumns Destination:=Range(B1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1)), TrailingMinusNumbers:= _ True Range(A1).Select ActiveCell.FormulaR1C1 = Balance Range(B1).Select ActiveCell.FormulaR1C1 = Portfolio Range(C1).Select ActiveCell.FormulaR1C1 = CCY Range(D1).Select ActiveCell.FormulaR1C1 = Date Range(A1:D1).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-21 Application.CutCopyMode = False Selection.Cut Range(E1).Select ActiveSheet.Paste Range(H1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveSheet.Paste Range(F1).Select Range(Selection, Selection.End(xlDown)).Select Columns(F:G).Select Selection.Cut Range(B1).Select ActiveSheet.Paste Range(E1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range(D1).Select ActiveSheet.Paste Selection.NumberFormat = #,##0.00 Range(D25).Select Selection.End(xlDown).Select Range(B2).Select Selection.End(xlDown).Select Z = ActiveCell.Row + 0 Range(B Z).Value = Grand Total ActiveCell.Select Selection.Font.Bold = True Selection.End(xlUp).Select Range(A1:D1).Select Selection.Font.Bold = True Range(D2).Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = #,##0.00_ ;[Red
RE: $$Excel-Macros$$ Error in Macro Code
Hi Vebhav, You say you need to change the highlighted code daily, but you don't say what you change it to, or on what basis it is changed. We need to know this if we have any chance of helping you automate the process. Regards - Dave. Date: Tue, 7 Dec 2010 22:33:00 +0530 Subject: $$Excel-Macros$$ Error in Macro Code From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com Hello, Can you please help me in making this code in a standard format, instead of changing the code daily manually of the highlighted text. Thanks in Advance. Sub macro1() ' ' Macro1 Macro ' ' Dim Z As Long Range(H2).Select ActiveCell.FormulaR1C1 = =CONCATENATE(RC[-6],RC[-5],RC[-1]) Range(H2).Select Selection.AutoFill Destination:=Range(H2:H341) Columns(H:H).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns(B:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:B1).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Clear ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Add Key:=Columns( _ B:B), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets(kmhpmebck1).Sort .SetRange Range(A1:B334) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 Range(B3).Select Range(Selection, Selection.End(xlDown)).Select Selection.Font.Bold = False Selection.Replace What:=total, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns(B:B).Select Selection.TextToColumns Destination:=Range(B1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1)), TrailingMinusNumbers:= _ True Range(A1).Select ActiveCell.FormulaR1C1 = Balance Range(B1).Select ActiveCell.FormulaR1C1 = Portfolio Range(C1).Select ActiveCell.FormulaR1C1 = CCY Range(D1).Select ActiveCell.FormulaR1C1 = Date Range(A1:D1).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-21 Application.CutCopyMode = False Selection.Cut Range(E1).Select ActiveSheet.Paste Range(H1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveSheet.Paste Range(F1).Select Range(Selection, Selection.End(xlDown)).Select Columns(F:G).Select Selection.Cut Range(B1).Select ActiveSheet.Paste Range(E1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range(D1).Select ActiveSheet.Paste Selection.NumberFormat = #,##0.00 Range(D25).Select Selection.End(xlDown).Select Range(B2).Select Selection.End(xlDown).Select Z = ActiveCell.Row + 0 Range(B Z).Value = Grand Total ActiveCell.Select Selection.Font.Bold = True Selection.End(xlUp).Select Range(A1:D1).Select Selection.Font.Bold = True Range(D2).Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = #,##0.00_ ;[Red]-#,##0.00 Range(D2).Select Range(Selection, Selection.End(xlDown)).Select End Sub Thanks, Vebhav Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at
RE: $$Excel-Macros$$ Error in Macro Code
Hi Vebhav, You say you need to change the highlighted code daily, but you don't say what you change it to, or on what basis it is changed. We need to know this if we have any chance of helping you automate the process. Regards - Dave. Date: Wed, 8 Dec 2010 06:51:01 +0530 Subject: Re: $$Excel-Macros$$ Error in Macro Code From: vebhav.j...@gmail.com To: excel-macros@googlegroups.com Can someone look into this and suggest me. On Tue, Dec 7, 2010 at 10:33 PM, vebhav jain vebhav.j...@gmail.com wrote: Hello, Can you please help me in making this code in a standard format, instead of changing the code daily manually of the highlighted text. Thanks in Advance. Sub macro1() ' ' Macro1 Macro ' ' Dim Z As Long Range(H2).Select ActiveCell.FormulaR1C1 = =CONCATENATE(RC[-6],RC[-5],RC[-1]) Range(H2).Select Selection.AutoFill Destination:=Range(H2:H341) Columns(H:H).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns(B:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:B1).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Clear ActiveWorkbook.Worksheets(kmhpmebck1).Sort.SortFields.Add Key:=Columns( _ B:B), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets(kmhpmebck1).Sort .SetRange Range(A1:B334) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 Range(B3).Select Range(Selection, Selection.End(xlDown)).Select Selection.Font.Bold = False Selection.Replace What:=total, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns(B:B).Select Selection.TextToColumns Destination:=Range(B1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1)), TrailingMinusNumbers:= _ True Range(A1).Select ActiveCell.FormulaR1C1 = Balance Range(B1).Select ActiveCell.FormulaR1C1 = Portfolio Range(C1).Select ActiveCell.FormulaR1C1 = CCY Range(D1).Select ActiveCell.FormulaR1C1 = Date Range(A1:D1).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-21 Application.CutCopyMode = False Selection.Cut Range(E1).Select ActiveSheet.Paste Range(H1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveSheet.Paste Range(F1).Select Range(Selection, Selection.End(xlDown)).Select Columns(F:G).Select Selection.Cut Range(B1).Select ActiveSheet.Paste Range(E1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range(D1).Select ActiveSheet.Paste Selection.NumberFormat = #,##0.00 Range(D25).Select Selection.End(xlDown).Select Range(B2).Select Selection.End(xlDown).Select Z = ActiveCell.Row + 0 Range(B Z).Value = Grand Total ActiveCell.Select Selection.Font.Bold = True Selection.End(xlUp).Select Range(A1:D1).Select Selection.Font.Bold = True Range(D2).Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = #,##0.00_ ;[Red]-#,##0.00 Range(D2).Select Range(Selection, Selection.End(xlDown)).Select End Sub Thanks, Vebhav Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our
RE: $$Excel-Macros$$ Own Formule in Pivot Table
Hey, not dumb. Easy mistake to make. I make it all the time. That's probably why I spotted it. Regards - Dave. From: harmeet.hew...@gmail.com Date: Fri, 3 Dec 2010 19:10:18 +1100 Subject: Re: $$Excel-Macros$$ Own Formule in Pivot Table To: excel-macros@googlegroups.com Hi Dave, Thanks for the correction and sorry for being so dumb :D. Warm Regards, Harmeet Singh IT Analyst McKinsey Company http://www.facebook.com/Harmeeet On Fri, Dec 3, 2010 at 1:16 PM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Harmet, your formula is using relative values for the SUM(B2:B8) section, and as a result, has created errors while copying down. Formula in D2 should look like this: =B2/SUM($B$2:$B$8) Then copy down. Regards - Dave. From: harmeet.hew...@gmail.com Date: Fri, 3 Dec 2010 11:36:59 +1100 Subject: Re: $$Excel-Macros$$ Own Formule in Pivot Table To: excel-macros@googlegroups.com CC: sunscel...@gmail.com here you go Warm Regards, Harmeet Singh IT Analyst McKinsey Company http://www.facebook.com/Harmeeet On Thu, Dec 2, 2010 at 11:43 PM, Ramkesh Maurya sunscel...@gmail.com wrote: Hi All, Please tell me how i can insert my own formule in Excel. e.g 2 is 5.71% of 35 the Grand Total, in next column I want to sum the percentage of 2( i.ee 5.71) + percentage of 3 (i.e. How much 3 is fraction of 35 ?) Item Quantity Cumuletive Sum A 2 5.71% B 3 C 4 D 5 E 6 F 7 G 8 35 Waiting for reply-- Ramkesh 9990260398 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Own Formule in Pivot Table
Hi, Harmet, your formula is using relative values for the SUM(B2:B8) section, and as a result, has created errors while copying down. Formula in D2 should look like this: =B2/SUM($B$2:$B$8) Then copy down. Regards - Dave. From: harmeet.hew...@gmail.com Date: Fri, 3 Dec 2010 11:36:59 +1100 Subject: Re: $$Excel-Macros$$ Own Formule in Pivot Table To: excel-macros@googlegroups.com CC: sunscel...@gmail.com here you go Warm Regards, Harmeet Singh IT Analyst McKinsey Company http://www.facebook.com/Harmeeet On Thu, Dec 2, 2010 at 11:43 PM, Ramkesh Maurya sunscel...@gmail.com wrote: Hi All, Please tell me how i can insert my own formule in Excel. e.g 2 is 5.71% of 35 the Grand Total, in next column I want to sum the percentage of 2( i.ee 5.71) + percentage of 3 (i.e. How much 3 is fraction of 35 ?) Item Quantity Cumuletive Sum A 2 5.71% B 3 C 4 D 5 E 6 F 7 G 8 35 Waiting for reply-- Ramkesh 9990260398 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$
Hi, Thanks Anund. Anindya, are you using XL2003 or 2007? When you want to conditional format a whole block of data like the table I sent you, XL is very helpful and you only need to enter the formula once. First you need to select the whole table. You will notice that when you select a group of cells, the cell you begin with is highlighted in a different way. So when you select the cells in our table, you will probably start in cell A2, dragging down and across to cell D29. This highlights the whole table, but with A2 highlighted differently, which becomes the key cell for our conditional format formula. Write the formula as if you just wanted A2 formatted, and XL will put the formula into all the other cells in the selection, changing it as necessary. Very cool! The format you want, is only applied if the formula is TRUE. So, looking at our formula: =Month($D2)=$G$2 This asks, Is the month of the date in cell D2, equal to the number you selected in the drop-down in G2? If yes (TRUE) then apply the formatting. You say you are a beginner in Excel, so if you're not sure why the dollar signs ($) are in the formula, just ask. They are important. Or ask anything else you need to know. Regards - Dave. Date: Mon, 29 Nov 2010 22:43:17 -0800 Subject: Re: $$Excel-Macros$$ From: anand...@gmail.com To: excel-macros@googlegroups.com Hi, Just saw your query and the solution both. Doing this would be simple though. Then you have formed a separate drop down column in G2 putting the months as reference - *How did you do this particular step??* This step is done through data validation. In Excel 2007 you can find this in Data tab on the ribbon. In 2003 version it can be found in Data menu. Select List in the Allow: drop down box and in the source you type in 1,2,3,412 click on OK and you will have a drop down box *It also seems that you have done conditional formatting in the main table. But how is that only the month which am sorting is showing red in colour??You need to explain me this step too. Correctly guessed it is conditional formatting in the main table. In the Select a rule type select use a formula for cells to format in the formula type =Month($D2)=$G$2 in the format section choose the font and fill type for the cell and you are done. In case you need further explanation feel free to reach me at anand...@gmail.com. Hope that helps, @Dave : Perfect solution. Neat and clean work, my appreciations for the solution Regards Anand Kumar On Nov 29, 8:58 pm, Anindya Roy anind...@gmail.com wrote: Thanks a million Dave. You have just served it on a platter for me. Simply awesome. But would request you to explain the steps if possible as I am a begginer in excel. What I could decipher is that You have formed a separate column and have put the formula. Then you have formed a separate drop down column in G2 putting the months as reference - *How did you do this particular step??* *It also seems that you have done conditional formatting in the main table. But how is that only the month which am sorting is showing red in colour??You need to explain me this step too. * *Regards* *Anindya * On Mon, Nov 29, 2010 at 2:44 PM, Dave Bonallack davebonall...@hotmail.comwrote: Hi, Not quite sure what you mean by sort out names but have a look at the attached. You can select the month you want by using the drop-down in cell G2 (1 to 12) The main table contains Conditional Formatting, while Col E contains formulas. Regards - Dave. -- Date: Sun, 28 Nov 2010 23:15:14 +0530 Subject: $$Excel-Macros$$ From: anind...@gmail.com To: excel-macros@googlegroups.com Suppose I have a file which contains names of the candidates along with their date of birth. Now if i want to sort out names which have birthday falling in the month of november. How do I do that. Please note that DOB is mm/dd/ format. S.No Beneficiary Name Ward No DOB 1 Ambarish Ghosh 11 12/8/1985 2 Anirban Chakraborty 50 7/13/1991 3 Anasuya Das 21 6/21/1981 4 Ananya Dasgupta 37 8/28/1984 5 Asish Majhi 41 2/11/1990 6 Amit Kumar Sharma 27 2/12/1984 7 Abhijit Golui 37 9/30/1989 8 Bidisha Manik 12 7/19/1985 9 Baishakhi Mallick 9 4/15/1986 10 Biswajit Paul 10 9/18/1984 11 Barnali Roy 19 11/11/1976 12 Dipanjan Karar 18 1/20/1988 13 Jesmine Mondal 39 12/18/1985 14 Kabita Mukherjee 38 8/24/1984 15 Kabita Roy Bagchi 41 8/12/1980 16 Kalpana Dey 48 5/6/1983 17 Kartick Ghosh 48 11/17/1988 18 Kuntal Mukherjee 50 5/5/1982 19 Madhumita Das 39 6/4/1985 20
RE: $$Excel-Macros$$ playing with several timers with vba excel 2000 ...
You're welcome. Regards - Dave. Date: Sun, 7 Nov 2010 06:25:05 -0800 Subject: Re: $$Excel-Macros$$ playing with several timers with vba excel 2000 ... From: jos...@gmail.com To: excel-macros@googlegroups.com Hi Dave ! first of all, thanks a lot for your response and your example, it works like a charme ! I'm sorry about the month for responding to you ! again : thanks a lot, it will a super example to learn more about timers ! José On 11 oct, 11:29, Dave Bonallack davebonall...@hotmail.com wrote: Hi Alfred, Please don't apologise for your English. We are quite tolerant here. Have a look at the attached. First click on the 'Subject' buttons. You will notice that clicking any one of them puts a Yes in it's Row, and changes the other Rows to No. Use these buttons to select which subject is the first to be discussed. I have used seconds, not minutes, for test purposes. But you can change back to minutes later. You can enter whatever values you want in Cells B3, B4 and B5, but I suggest you leave them as they are for now. The value in Cell B2 is the entire length of the meeting, and is just the sum of B3, B4 and B5 Click the Start button. and watch what happens. You can click on a different Subject button at any time to change which Subject is being timed, despite the fact the cursor has changed. The bar graph effect is done by Conditional Formatting. Each colour cell represents 1/20 of the subjects allotted time. One small problem - if you want the main timer to stop it before the end of the meeting, select any unused cell and type something (eg: 1) Thanks for the interesting question, and let us know how it goes. Regards - Dave. Date: Sun, 10 Oct 2010 17:14:40 -0700 Subject: $$Excel-Macros$$ playing with several timers with vba excel 2000 ... From: jos...@gmail.com To: excel-macros@googlegroups.com Hi to all ! first of all, sorry for my so poor english, i'm a poor poor poor frenchy guy ! i'm trying to dev à meeting time manager ... here is my project : in e meeting, you have several subjects, and, of course each subjects have there own time... And, of course, one meeting have his global time. for exemple meeting EXCEL 3 houres. and for example subject 1 : macros : 1 houre subject 2 : cells : 30 minutes subject 3 : automation : 1h30 well, I write those lines in a sheet for each line (meeting EXCEL, subj 1, subj 2 subj 3) i would like to créate a sort of buton tu start à timer, AND, créate a sort of progress bar (for each line) with a label, and the width propriety to show the time progress of course, the general time meeting (first line) can't be stopped. and, for all the subjects, the pilot of the meeting can decide wich subject will be treated or not and purhaps another order ... If a excel guru can help me, it will be fantastic for me. of course, I must dev on my own that little app, but, if i can read some examples on : - using timers - using progress bar with timers. - creating labels if a cell is not blank (for exemple if I put tatatatat column A line 6, then I love to know how to create à buton on line 6 column B, and some Label maned like the content of line6 colA.) well. very very very thanks for having read my horrible english problem, without google translate ! lol José from France -- -- Some important links for excel users: 1. Follow us on TWITTER 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/pages/discussexcelcom/160307843985936?v=wall;... Timer.xls 39KAfficherTélécharger -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ help required
Hi Girish, Can we use VBA? Regards - Dave. Date: Wed, 3 Nov 2010 12:36:23 +0530 Subject: Re: $$Excel-Macros$$ help required From: girishkumar832...@gmail.com To: excel-macros@googlegroups.com; davebonall...@hotmail.com Hi Dave, Pls. find attached file. i have added formulas in respective columns. Left formula i started with =left(A2,13) then 12, 11, 10. ending =left(A2,3) i dont want to use left vlookup function for getting data, i want direct formula for getting data. i dont mind for answer questions feel free to ask. thanks in advance Girish On Wed, Nov 3, 2010 at 7:59 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Girish, in USED VLOOKUP HERE column i used Vlookup function taken table _array as SYSTEM DATA This is not a formula. I need the actual formula you used so I can see what you are trying to achieve. I don't mind persisting with this, but you will need to keep answering my questions. I don't like guessing. Regards - Dave. Date: Tue, 2 Nov 2010 19:28:15 +0530 Subject: Re: $$Excel-Macros$$ help required From: girishkumar832...@gmail.com To: excel-macros@googlegroups.com; davebonall...@hotmail.com Hi Dave thanks for your reply actually i removed formula because file size is big in USED HERE LEFT FUNCTION i used left function like =left(A2,13) and pasted it till end and in USED VLOOKUP HERE column i used Vlookup function taken table _array as SYSTEM DATA AND pasted till and after the in filter i seleted #n/a and again i edited the left function Like =left(A2,12) and then again vlookup Like wise decreasing Left Num_chars and vlookup i got all realtive data in USED VLOOKUP HERE column but this very lengthy process and taking too much time for calculating vlookup and data also very huge so i need any easy logical function having same answer as in Column USED VLOOKUP HERE hope u understand Thanks in advance Girish On Tue, Nov 2, 2010 at 10:08 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Your sample sheet gives insufficient and contradictory info. Column B has a header which says USED HERE LEFT FUNCTION but there is no left function used there - the data is just entered as text, and the length of text varies, which means that the left function probably wasn't used. Column C has a header which says USED VLOOKUP HERE but again the data is just text. Column D has a header which says Your Logical Function Pls but you give no indication as to what you want the logical function to do. We would love to help you, but it's just not possible with this info. Regards - Dave. Date: Mon, 1 Nov 2010 19:37:37 +0530 Subject: Fwd: $$Excel-Macros$$ help required From: girishkumar832...@gmail.com To: excel-macros@googlegroups.com Dear Experts, Any updates!!! -- Forwarded message -- From: girish kumar girishkumar832...@gmail.com Date: Thu, Oct 21, 2010 at 2:52 PM Subject: Re: $$Excel-Macros$$ help required To: excel-macros@googlegroups.com Hi Can anyone help me on this On Tue, Oct 19, 2010 at 3:06 PM, girish kumar girishkumar832...@gmail.com wrote: hi experts, its a rolling up method for example if raw cdr having No 93854512115 then formula should find name for this no if didnt then it roll up one no and then find again (like 9385451211) till finding name it will rolling up hope now u understand Girish On Tue, Oct 19, 2010 at 1:24 PM, Jai jaihumtu...@gmail.com wrote: Please check the attachment, On Tue, Oct 19, 2010 at 10:32 AM, girish kumar girishkumar832...@gmail.com wrote: Hi Krishna, see file attached, in that second sheet u see in b column i used Left function and in c column Vlookup left function i used =left(A2.13) and vlookup answer is #N/A Because In System Data sheet Left function answer is not there if i decrease the no's in left function i ll get the answer likewise till i get answer i have to decrease the no in left function hope u understand now data will be very huge and it take long time to vlookup calculate so try to give any logical function. Thanks in advance Girish On Mon, Oct 18, 2010 at 9:18 PM, krishna mummina lovemekris...@gmail.com wrote: Dear Girish, Sorry I didn't understand what you are looking for, Please give an Example, what you want exactly? May be i have some communication problem to understand your query. Thanks, Excelkid On Mon, Oct 18, 2010 at 7:52 PM, girish kumar girishkumar832...@gmail.com wrote: Dear Experts, need one urgent help from you guys i have attached one file having 2 sheets named system data Raw CDR i need a formula for getting name in sheet Raw CDR for exam sheet system data having no 93 Afghanistan Other and Raw Cdr having No 93854512115 for getting destination name in sheet raw CDR i m using 1st left function like =left(A2,13) and again giving vllokup formula as mentioned in sheet if data not found, then i decrease the no's
RE: $$Excel-Macros$$ help required
Hi Girish, in USED VLOOKUP HERE column i used Vlookup function taken table _array as SYSTEM DATA This is not a formula. I need the actual formula you used so I can see what you are trying to achieve. I don't mind persisting with this, but you will need to keep answering my questions. I don't like guessing. Regards - Dave. Date: Tue, 2 Nov 2010 19:28:15 +0530 Subject: Re: $$Excel-Macros$$ help required From: girishkumar832...@gmail.com To: excel-macros@googlegroups.com; davebonall...@hotmail.com Hi Dave thanks for your reply actually i removed formula because file size is big in USED HERE LEFT FUNCTION i used left function like =left(A2,13) and pasted it till end and in USED VLOOKUP HERE column i used Vlookup function taken table _array as SYSTEM DATA AND pasted till and after the in filter i seleted #n/a and again i edited the left function Like =left(A2,12) and then again vlookup Like wise decreasing Left Num_chars and vlookup i got all realtive data in USED VLOOKUP HERE column but this very lengthy process and taking too much time for calculating vlookup and data also very huge so i need any easy logical function having same answer as in Column USED VLOOKUP HERE hope u understand Thanks in advance Girish On Tue, Nov 2, 2010 at 10:08 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Your sample sheet gives insufficient and contradictory info. Column B has a header which says USED HERE LEFT FUNCTION but there is no left function used there - the data is just entered as text, and the length of text varies, which means that the left function probably wasn't used. Column C has a header which says USED VLOOKUP HERE but again the data is just text. Column D has a header which says Your Logical Function Pls but you give no indication as to what you want the logical function to do. We would love to help you, but it's just not possible with this info. Regards - Dave. Date: Mon, 1 Nov 2010 19:37:37 +0530 Subject: Fwd: $$Excel-Macros$$ help required From: girishkumar832...@gmail.com To: excel-macros@googlegroups.com Dear Experts, Any updates!!! -- Forwarded message -- From: girish kumar girishkumar832...@gmail.com Date: Thu, Oct 21, 2010 at 2:52 PM Subject: Re: $$Excel-Macros$$ help required To: excel-macros@googlegroups.com Hi Can anyone help me on this On Tue, Oct 19, 2010 at 3:06 PM, girish kumar girishkumar832...@gmail.com wrote: hi experts, its a rolling up method for example if raw cdr having No 93854512115 then formula should find name for this no if didnt then it roll up one no and then find again (like 9385451211) till finding name it will rolling up hope now u understand Girish On Tue, Oct 19, 2010 at 1:24 PM, Jai jaihumtu...@gmail.com wrote: Please check the attachment, On Tue, Oct 19, 2010 at 10:32 AM, girish kumar girishkumar832...@gmail.com wrote: Hi Krishna, see file attached, in that second sheet u see in b column i used Left function and in c column Vlookup left function i used =left(A2.13) and vlookup answer is #N/A Because In System Data sheet Left function answer is not there if i decrease the no's in left function i ll get the answer likewise till i get answer i have to decrease the no in left function hope u understand now data will be very huge and it take long time to vlookup calculate so try to give any logical function. Thanks in advance Girish On Mon, Oct 18, 2010 at 9:18 PM, krishna mummina lovemekris...@gmail.com wrote: Dear Girish, Sorry I didn't understand what you are looking for, Please give an Example, what you want exactly? May be i have some communication problem to understand your query. Thanks, Excelkid On Mon, Oct 18, 2010 at 7:52 PM, girish kumar girishkumar832...@gmail.com wrote: Dear Experts, need one urgent help from you guys i have attached one file having 2 sheets named system data Raw CDR i need a formula for getting name in sheet Raw CDR for exam sheet system data having no 93 Afghanistan Other and Raw Cdr having No 93854512115 for getting destination name in sheet raw CDR i m using 1st left function like =left(A2,13) and again giving vllokup formula as mentioned in sheet if data not found, then i decrease the no's in left function as 12, 11, 10 till i get the destintaion name problem is data will be very huge and take more time to calculate so friends if u can find any logical function for this it'll help me very much thanks in advance Girish -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http
RE: $$Excel-Macros$$ Parsing a range to an array
Hi, You can test for a non-contiguous selection with the following line of code: A = Selection.Areas.Count Use this in an If statement - eg If Selection.Areas.Count 1 then msgbox You can't use non-contiguous cells.: Exit Sub Hope this helps. Regards - Dave. Date: Thu, 28 Oct 2010 00:28:29 -0700 Subject: $$Excel-Macros$$ Parsing a range to an array From: mr.frog.to@googlemail.com To: excel-macros@googlegroups.com Hi Everyone, I am building a series of wizards for handling some internal processes at my office. We are extracting data from from '3rd party' data sources into excel worksheets. To further process this data by hand is slow, so I was asked to write a few macros etc to speed things up. The issue I am facing is occuring when the user selects the specific cells they want to use for a particular wizard / process. When the user selects contiguous cells, cells that are next to each other, then all is fine when using the processes. I am letting the user choose the cells via the ref object on a user form. The user sometimes chooses cells that are not next to each other, cells in the same column but not right next to each other for example. This causes me a problem in handling the data and breaks my code. I am grabbing the data as follows: varXRange() = Range(refXRange) This normally retuns me an array in R1C1 type 'layout', and is extremely useful for processing large volumes of data at speed. What I am trying to do is to find a way to achieve the same, but with non contiguous ranges. I was thinking of simply searching the string that the ref object returns for commas, then process each chunk separately adding it to an array, but was wondering if someone already has an approach to solving this? Any thoughts would be appreciated Cheers The Frog -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Finding student whose marks are improving over three consecutive tests
dgbonall...@hotmail.com Date: Sat, 23 Oct 2010 06:08:09 -0700 Subject: Re: $$Excel-Macros$$ Finding student whose marks are improving over three consecutive tests From: vaaibhavjhav...@gmail.com To: excel-macros@googlegroups.com Yes this solved the case. Can you please give me your email id so that i can communicate directly? I have a long database now. On Oct 21, 6:10 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Have a look at the attached to see if it does what you need. Regards - Dave. Date: Wed, 20 Oct 2010 20:04:57 -0700 Subject: $$Excel-Macros$$ Finding student whose marks are improving over three consecutive tests From: vaaibhavjhav...@gmail.com To: excel-macros@googlegroups.com Hello I have got sample data as follows: Marks in a single subject Student Class Marks Test A X 20 First B XI 25 First C V 12 First A X 25 Second B XI 23 Second C V 12 Second D VII 34 Second A X 75 Third B XI 34 Third C V 87 Third D VII 12 Third Now I want some ability that I am able to find the list of all students who have got higher marks continuously i.e. Third Test MarksSecond TestFirst Test. Further I want a functionality that when I add marks of Fourth Test in the sheet above then I shall need names of students whose marks are increasing from last 3 tests i.e. Fourth TestThird TestSecond Test. In this case First test marks are irrelevant and so on for fifth, sixth, seventh tests... -- --- --- Some important links for excel users: 1. Follow us on TWITTER 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/pages/discussexcelcom/160307843985936?v=wall;... Students.xls 29KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ playing with several timers with vba excel 2000 ...
Hi Alfred, Please don't apologise for your English. We are quite tolerant here. Have a look at the attached. First click on the 'Subject' buttons. You will notice that clicking any one of them puts a Yes in it's Row, and changes the other Rows to No. Use these buttons to select which subject is the first to be discussed. I have used seconds, not minutes, for test purposes. But you can change back to minutes later. You can enter whatever values you want in Cells B3, B4 and B5, but I suggest you leave them as they are for now. The value in Cell B2 is the entire length of the meeting, and is just the sum of B3, B4 and B5 Click the Start button. and watch what happens. You can click on a different Subject button at any time to change which Subject is being timed, despite the fact the cursor has changed. The bar graph effect is done by Conditional Formatting. Each colour cell represents 1/20 of the subjects allotted time. One small problem - if you want the main timer to stop it before the end of the meeting, select any unused cell and type something (eg: 1) Thanks for the interesting question, and let us know how it goes. Regards - Dave. Date: Sun, 10 Oct 2010 17:14:40 -0700 Subject: $$Excel-Macros$$ playing with several timers with vba excel 2000 ... From: jos...@gmail.com To: excel-macros@googlegroups.com Hi to all ! first of all, sorry for my so poor english, i'm a poor poor poor frenchy guy ! i'm trying to dev à meeting time manager ... here is my project : in e meeting, you have several subjects, and, of course each subjects have there own time... And, of course, one meeting have his global time. for exemple meeting EXCEL 3 houres. and for example subject 1 : macros : 1 houre subject 2 : cells : 30 minutes subject 3 : automation : 1h30 well, I write those lines in a sheet for each line (meeting EXCEL, subj 1, subj 2 subj 3) i would like to créate a sort of buton tu start à timer, AND, créate a sort of progress bar (for each line) with a label, and the width propriety to show the time progress of course, the general time meeting (first line) can't be stopped. and, for all the subjects, the pilot of the meeting can decide wich subject will be treated or not and purhaps another order ... If a excel guru can help me, it will be fantastic for me. of course, I must dev on my own that little app, but, if i can read some examples on : - using timers - using progress bar with timers. - creating labels if a cell is not blank (for exemple if I put tatatatat column A line 6, then I love to know how to create à buton on line 6 column B, and some Label maned like the content of line6 colA.) well. very very very thanks for having read my horrible english problem, without google translate ! lol José from France -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Timer.xls Description: Binary data
RE: $$Excel-Macros$$ Re: Need next number with a twist
Hi, For every unique value of col A - C and col F, then the number is increased by 1 from col G unless there is already a number for that combo in col H, then it gets increased by 1 from that number in col H I think I understand the first part of the sentence, but the meaning of the second part escapes me. unless there is already a number for that combo in col H, then it gets increased by 1 from that number in col H It appears that there is always a number in Col H, but is it a number for that combo ? I can't tell, since it is just a number, and not derived by a formula. Regards - Dave. Date: Mon, 4 Oct 2010 13:47:08 -0700 From: n8dine4ma...@yahoo.com Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist To: excel-macros@googlegroups.com Does anyone have any thoughts on this? I've attached the spreadsheet again if anyone wants to take a look. Is there a way maybe to concatenate some cells and then write a formula? From: None n8dine4ma...@yahoo.com To: excel-macros@googlegroups.com Sent: Thu, September 30, 2010 2:52:14 PM Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist Joshua, Thank you so much for your response. Unfortunately this isn't working. The formula in col I is referencing col H which is where I originally wanted the formula. There will be no data in col H unless I can't get a formula to work in there, then it will be manually entered. Can you help me with how the formula should look in cell H2 so that it will return the same numbers I manually entered there to show what the formula result should be? Thank you. Nadine From: Joshua Leuthauser leu...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:32:44 PM Subject: $$Excel-Macros$$ Re: Need next number with a twist Build a key in column g. The formula for the key should be: =a2b2c2f2 Populate that down for all of the rows. Basically what I built says -- look at the column of keys, if you don't find a match then take the max of all transactions used thus far and increment by one. If you do find a match, use the same transaction number that was used by that key (the combination of a-c2 f2). You'll notice that it doesn't reserve the next transaction that should be used, it just assigns the next transaction to whichever key shows up that is unique. After you have your column built with the key, here is the formula I put in I2: =IF(ISNA(VLOOKUP(G2,G1:H1,2,FALSE)),MAX(H:H) +1,VLOOKUP(G2,G1:H1,2,FALSE)) Fill that down the remainder of column I and you have a working formula that will either give the same transaction number if a key match is found otherwise give you the next available transaction number. On Sep 28, 10:16 pm, None n8dine4ma...@yahoo.com wrote: Hi there. I need some help finding the next number but there's a twist. Attached is a file that shows what I need. There's a sheet titled Transactions where the data is continually added to and the order cannot be changed so sorting the data is out of the question. The next sheet is called Need Formula and this is where I need a formula to be entered into cells H2 - H24 for this sample spreadsheet. The actual file will have more rows. The formula needs to look at the number in cell G2 and increment it by one UNLESS, and here's the trick, there is already a number in this column (H) for the combination of cells A2-C2 and F2. The sample will give you a better idea as I've entered the end result in column H already. I just need a formula that will give me the same result. Thank you so much for your help. next numbers.xls 43KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link
RE: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10
And from me too, Ayush, congratulations! Very good service you provide here. Lots of work and time behind the scenes. Thank-you. Dave. From: shubhangidesa...@gmail.com Date: Sun, 3 Oct 2010 16:46:13 +0530 Subject: Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 2010 To: excel-macros@googlegroups.com Congratulations Mr Ayush We r proud to have u here... On Sun, Oct 3, 2010 at 3:56 PM, ashish koul koul.ash...@gmail.com wrote: congrats ayush. On Sun, Oct 3, 2010 at 3:47 PM, Ayush jainayus...@gmail.com wrote: Dear Group, I am proud to announce that I am awarded Microsoft Most Valuable Professional MVP on October 01, 2010 for providing technical expertise in Excel technical communities for past one year. http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-announced-october-2010.aspx This is a prestigious award from Microsoft given every Quarter to the people who share their deep knowledge, real-world experience, and impartial, objective feedback to help people enhance the way they use technology. My deep thanks to each group member for all your support and contribution to this group. Best regards, Ayush Jain Microsoft MVP -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Macro hangs
Hi Paul, This code is excellent! I've looked up the Help for the Dictionary object, which clarified it's use quite well. At first I wondered why you had decided to work backwards, up through the code; then realised (I think) that it's so you can delete a row (Delete Shift:=xlUp) without having to account for the lost row number as you would if you were working down the data. Very cool! Wish I'd thought of that. Thanks also for the notations, which really helped me understand your process. And thanks for your time. Regards - Dave. Date: Wed, 29 Sep 2010 05:15:54 -0700 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro hangs To: excel-macros@googlegroups.com Dave, keep in mind that Excel can do several HUNDRED comparisons each SECOND. If you're adding an Application.wait for only ONE second EACH LINE for 13,000 lines, you're adding 13,000 SECONDS, or 216 minutes, or 3.6 hours of WAIT TIME! so, I suspect that you're not hanging, but simply waiting a LONG time. and, during the seconds of waiting, the escape characters used to interrupt aren't being received. Now.. personally, I like using excel dictionaries to store unique data. I've done some pretty elaborate things. I wrote a script to compare the fields and sum the columns. It runs (on my machine) in 1 minute, 19 seconds... try this (watch for wrapping from email): it also displays a status line in the status bar. Sub DeleteDuplicateDict() Dim RowCnt, R, Datainx, stat, msg Dim Dict_E, Dict_F Dim tstart, tstop, TMin, TSec, TElapsed tstart = Timer Application.ScreenUpdating = False Set Dict_E = CreateObject(Scripting.Dictionary) Set Dict_F = CreateObject(Scripting.Dictionary) stat = Dict_E.RemoveAll stat = Dict_F.RemoveAll ' Count the number of rows in sheet RowCnt = ActiveCell.SpecialCells(xlLastCell).Row 'Starting in the last row, process upwards For R = RowCnt To 2 Step -1 If (R Mod 500 = 0) Then Application.StatusBar = Processing: R Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value If (Datainx X X) Then 'If the data row is not blank If (Not Dict_E.exists(Datainx)) Then 'new data, add new record to dictionaries Dict_E.Add Datainx, ActiveSheet.Cells(R, E).Value Dict_F.Add Datainx, ActiveSheet.Cells(R, F).Value Else 'Existing records, update dictionaries Dict_E.Item(Datainx) = Dict_E.Item(Datainx) + ActiveSheet.Cells(R, E).Value Dict_F.Item(Datainx) = Dict_F.Item(Datainx) + ActiveSheet.Cells(R, F).Value Rows(R).Delete Shift:=xlUp End If End If Next R ' Count rows remaining RowCnt = Application.WorksheetFunction.CountA(Range(A:A)) For R = 2 To RowCnt If (R Mod 500 = 0) Then Application.StatusBar = Updating: R of RowCnt Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value 'update rows with Dictionary values If (Dict_E.exists(Datainx)) Then ActiveSheet.Cells(R, E).Value = Dict_E.Item(Datainx) ActiveSheet.Cells(R, F).Value = Dict_F.Item(Datainx) Else Cells(R, A).Select MsgBox Missing data for row: R End If Next R 'display processing time tstop = Timer TMin = 0 TElapsed = tstop - tstart TMin = TElapsed \ 60 TSec = TElapsed Mod 60 msg = msg Chr(13) Chr(13) If (TMin 0) Then msg = msg TMin mins msg = msg TSec sec MsgBox msg Application.StatusBar = False Application.ScreenUpdating = True End Sub Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:14:06 AM Subject: $$Excel-Macros$$ Macro hangs Hi group, I'm hoping someone can help me with the attached workbook. I've written a macro that makes XL freeze. The need is to check the data for duplicates based on Columns B, C D. If duplicates are found, their totals in Columns E F are to be sumed, then the duplicate row deleted. I concatonate Cells B2, C2 D2, then compare that with a concatonation of cells B3, C3 D3, then B4, C4 D4, and so on to the end of the data, dealing with duplicates as they come up. Then I start again with row 3, and so on until all the data is checked. The macro takes a long time to run, so I report progress in Cells G1 and H1. Whenever I run this macro, it never gets past about line 10 before XL freezes, and I have to use the Windows Task Manager to close it. There may be a better way of doing this, but my question is, why does it cause XL to freeze
RE: $$Excel-Macros$$ Visible Row Below Freeze Pane
Hi, How would I prove row 32 is the first visible row through VBA A = Activewindow.VisibleRange.Row If i then wanted to make row 50 the first visible row, how could it be done through VBA Range(A2).Select ActiveWindow.SmallScroll Down:=48 There's probably a better way of doing the second one, but this is all I could think of tonight. Regards - Dave. Date: Tue, 28 Sep 2010 16:08:14 -0700 Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane From: spa...@corbetteer.co.uk To: excel-macros@googlegroups.com With the top row frozen and the sheet scrolled down so that the first visible row below row 1 is row 32. How would I prove row 32 is the first visible row through VBA If i then wanted to make row 50 the first visible row, how could it be done through VBA -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Visible Row Below Freeze Pane
Hi, A simpler alternative for your second requirement: ActiveWindow.ScrollRow = 50 Regards - Dave. Date: Tue, 28 Sep 2010 16:08:14 -0700 Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane From: spa...@corbetteer.co.uk To: excel-macros@googlegroups.com With the top row frozen and the sheet scrolled down so that the first visible row below row 1 is row 32. How would I prove row 32 is the first visible row through VBA If i then wanted to make row 50 the first visible row, how could it be done through VBA -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Macro hangs
Hi Paul, Thanks for spending time on this. I saw another post of yours about using dictionaries which got my attention at the time, but then I got distracted by something shiny, and the moment was lost. I'll have a look at your reply (below) in detail on the weekend. But, being a curious lad, I'd like to know why my current macro hangs XL. The macro has a progress report row counter which updates cell G1 every time a row is checked, so I know when the macro has actually stopped running. Also, when I then press Escape, the whole screen sort of fades to white. If I could inspire you to share my curiosity, perhaps you could open my workbook and run it (with the timer section disabled), and let me know if you have any ideas on what I've done wrong. But if your macro really does do 13000 lines in just over a minute (and I have no reason to doubt you) then I will definitely be impressed, and dictionaries may even rise above SumProduct (in my eyes). Regards - Dave. Date: Wed, 29 Sep 2010 05:15:54 -0700 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro hangs To: excel-macros@googlegroups.com Dave, keep in mind that Excel can do several HUNDRED comparisons each SECOND. If you're adding an Application.wait for only ONE second EACH LINE for 13,000 lines, you're adding 13,000 SECONDS, or 216 minutes, or 3.6 hours of WAIT TIME! so, I suspect that you're not hanging, but simply waiting a LONG time. and, during the seconds of waiting, the escape characters used to interrupt aren't being received. Now.. personally, I like using excel dictionaries to store unique data. I've done some pretty elaborate things. I wrote a script to compare the fields and sum the columns. It runs (on my machine) in 1 minute, 19 seconds... try this (watch for wrapping from email): it also displays a status line in the status bar. Sub DeleteDuplicateDict() Dim RowCnt, R, Datainx, stat, msg Dim Dict_E, Dict_F Dim tstart, tstop, TMin, TSec, TElapsed tstart = Timer Application.ScreenUpdating = False Set Dict_E = CreateObject(Scripting.Dictionary) Set Dict_F = CreateObject(Scripting.Dictionary) stat = Dict_E.RemoveAll stat = Dict_F.RemoveAll ' Count the number of rows in sheet RowCnt = ActiveCell.SpecialCells(xlLastCell).Row 'Starting in the last row, process upwards For R = RowCnt To 2 Step -1 If (R Mod 500 = 0) Then Application.StatusBar = Processing: R Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value If (Datainx X X) Then 'If the data row is not blank If (Not Dict_E.exists(Datainx)) Then 'new data, add new record to dictionaries Dict_E.Add Datainx, ActiveSheet.Cells(R, E).Value Dict_F.Add Datainx, ActiveSheet.Cells(R, F).Value Else 'Existing records, update dictionaries Dict_E.Item(Datainx) = Dict_E.Item(Datainx) + ActiveSheet.Cells(R, E).Value Dict_F.Item(Datainx) = Dict_F.Item(Datainx) + ActiveSheet.Cells(R, F).Value Rows(R).Delete Shift:=xlUp End If End If Next R ' Count rows remaining RowCnt = Application.WorksheetFunction.CountA(Range(A:A)) For R = 2 To RowCnt If (R Mod 500 = 0) Then Application.StatusBar = Updating: R of RowCnt Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value 'update rows with Dictionary values If (Dict_E.exists(Datainx)) Then ActiveSheet.Cells(R, E).Value = Dict_E.Item(Datainx) ActiveSheet.Cells(R, F).Value = Dict_F.Item(Datainx) Else Cells(R, A).Select MsgBox Missing data for row: R End If Next R 'display processing time tstop = Timer TMin = 0 TElapsed = tstop - tstart TMin = TElapsed \ 60 TSec = TElapsed Mod 60 msg = msg Chr(13) Chr(13) If (TMin 0) Then msg = msg TMin mins msg = msg TSec sec MsgBox msg Application.StatusBar = False Application.ScreenUpdating = True End Sub Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:14:06 AM Subject: $$Excel-Macros$$ Macro hangs Hi group, I'm hoping someone can help me with the attached workbook. I've written a macro that makes XL freeze. The need is to check the data for duplicates based on Columns B, C D. If duplicates are found, their totals in Columns E F are to be sumed, then the duplicate row deleted. I concatonate Cells B2, C2 D2, then compare that with a concatonation of cells B3, C3 D3, then B4, C4 D4, and so on to the end of the data
RE: $$Excel-Macros$$
Hi Ramkesh, This can be done with an event macro, but not, I think, with worksheet formulas and/or functions. Are you allowed to use macros in this workbook? Regards - Dave. Date: Tue, 28 Sep 2010 23:40:09 +0530 Subject: $$Excel-Macros$$ From: sunscel...@gmail.com To: excel-macros@googlegroups.com Dear All, Xl Mania(s) I really appreciate this group for learning I have lrarnet a lot of tricks from this group which enabled me to come of with flying colours.Today i am posting my first query briefing in below lines--- 1- I fill some text in column A (say) 2- I want that the date of filling data should appear in column B (beside the column A) Now condition is - 3 -Date in column B should not be updated by re-calculation nature of Excel or user I know that anybody will help me so thanks in advance ! -- Ramkesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$
A further question: Do you want the date in Col B to enter only when the adjacent cell in Col A receives text for the first time, or any time the adjacent Col A cell is changed? Regards - Dave. Date: Tue, 28 Sep 2010 23:40:09 +0530 Subject: $$Excel-Macros$$ From: sunscel...@gmail.com To: excel-macros@googlegroups.com Dear All, Xl Mania(s) I really appreciate this group for learning I have lrarnet a lot of tricks from this group which enabled me to come of with flying colours.Today i am posting my first query briefing in below lines--- 1- I fill some text in column A (say) 2- I want that the date of filling data should appear in column B (beside the column A) Now condition is - 3 -Date in column B should not be updated by re-calculation nature of Excel or user I know that anybody will help me so thanks in advance ! -- Ramkesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Column Data in Rows Table
Hi, Sorry, but someone else will have to take this one. Regards - Dave. From: jai.ca...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Column Data in Rows Table Date: Sun, 19 Sep 2010 00:43:18 +0530 Hi Dave, I also have the similar issue. Name A B C D E AA 10 14 11 13 14 AB 13 13 14 14 12 AC 13 10 14 11 14 AD 14 10 10 13 13 The solution you provided is AA 10 AA 14 AA 11 AA 13 AA 14 AB 13 AB 13 AB 14 AB 14 AB 12 AC 13 AC 10 AC 14 AC 11 AC 14 AD 14 AD 10 AD 10 AD 13 AD 13 but I need this:- AA A 10 AA B 14 AA C 11 AA D 13 AA E 14 AB A 13 AB B 13 AB C 14 AB D 14 AB E 12 AC A 13 AC B 10 AC C 14 AC D 11 AC E 14 AD A 14 AD B 10 AD C 10 AD D 13 AD E 13 - Original Message - From: Dave Bonallack To: excel-macros@googlegroups.com Sent: Wednesday, September 08, 2010 2:32 PM Subject: RE: $$Excel-Macros$$ Column Data in Rows Table Hi Sayyad, Have a look at the attached. Click the button on sheet 2. Regards - Dave Date: Tue, 7 Sep 2010 23:30:20 -0700 Subject: $$Excel-Macros$$ Column Data in Rows Table From: anamika2...@gmail.com To: excel-macros@googlegroups.com Hi , I have a case where I get the data in following format Name A B C D E AA 10 14 11 13 14 AB 13 13 14 14 12 AC 13 10 14 11 14 AD 14 10 10 13 13 I need to convert the above format to the data in following format. The no columns no of rows in the data vary, can you help me to get this done AA A 10 AA B 14 AA C 11 AA D 13 AA E 14 AB A 13 AB B 13 AB C 14 AB D 14 AB E 12 AC A 13 AC B 10 AC C 14 AC D 11 AC E 14 AD A 14 AD B 10 AD C 10 AD D 13 AD E 13 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ Replacing a specific tag/text based on a value
Hi, The answer to all your questions is Yes. But what we need in order to offer specific help, is a framework - at least a sort of skeleton of a workbook, with as much info as you know how to put in. From there, we can probably help you little by little. Regards - Dave. Date: Fri, 17 Sep 2010 19:20:30 -0700 Subject: $$Excel-Macros$$ Replacing a specific tag/text based on a value From: valsab...@gmail.com To: excel-macros@googlegroups.com Hi All, I am very new to excel macros so I really need help here. I have a test plan which is in excel and I want to utilize this test plan irrespective of the customer the testing is for. Every customer has its own terminologies say for a particular event one customer might be calling it EVENTA while another EVENTB. So what I am thinking is to have some kind of unique tag each of these terms and some setup in macro where I can define that if its customer A then wherever you see $$EVENT$$ go and replace it with EVENTA else EVENTB. Also another case is that there are some test cases that are valid for one customer while some arent. So when I am testing for customer A I want to hide customer B specific test cases. Can I achieve the above scenario in excel? If yes can I have some pointers that might help me? Thanks, Vals! -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
RE: $$Excel-Macros$$ Stock Monitoring from Raw data
Hi Kalyan, Have a look at the attached. The solution I've come up with is rather cumbersome, but I couldn't think of how to shorten it. Regards - Dave. Date: Sat, 18 Sep 2010 16:16:27 +0530 Subject: $$Excel-Macros$$ Stock Monitoring from Raw data From: kalx...@gmail.com To: excel-macros@googlegroups.com CC: dilipan...@gmail.com; noorain.ans...@gmail.com; kumar.bemlmum...@gmail.com; davebonall...@hotmail.com; brunobbr...@gmail.com; koul.ash...@gmail.com; talk2mar...@gmail.com Dear Experts, I am facing a problem to lookup data from another sheet. Please help me. Details is in attached file. Thanks in advance Kalyan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 Stk Monitoring(1).xlsx Description: Binary data
RE: $$Excel-Macros$$ Need most recent date
Thanks Paul Dave. Date: Wed, 15 Sep 2010 05:02:08 -0700 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Need most recent date To: excel-macros@googlegroups.com alternately, you could sort your data by invoice#,date (descending) then do a vlookup. It would pick the first date, which would be the most recent. I like Dave's suggestion... It's interesting because it takes advantage of the fact that using (Othersheet!A2:A100=A2) is a logical test that returns 1=true, 0=false then, since excel stores dates as numbers (today, 9/15/2010 is 40436 days since 1/1/1900) false * 40436 = 0 * 40436 = 0 true * 40436 = 1 * 40436 = 40436, when formatted as a date = 9/15/2010 I like it... Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wed, September 15, 2010 12:39:40 AM Subject: RE: $$Excel-Macros$$ Need most recent date Hi, I am assuming that your first unique invoice numberis in A2. Format B2 of the same sheet as Date, then enter the following: =Sumproduct(Max((OtherSheet!A2:A100=A2)*(OtherSheet!B2:B100))) Then copy down to the end of your unique data. If you can't get this to work, post again with a sample workbook. Regards - Dave. Date: Tue, 14 Sep 2010 17:33:44 -0700 From: n8dine4ma...@yahoo.com Subject: $$Excel-Macros$$ Need most recent date To: excel-macros@googlegroups.com I have a list of dates in one sheet. Col A = Invoice Number Col B = Payment Date There will be multiple rows for the same invoice number as payments are made against it until it is paid in full. Some of these rows will contain dates and some won't. The next sheet has the unique list of invoice numbers (meaning they don't repeat). I need a formula that will look at the first sheet and give me the most recent date if there is one. This is in Excel 2003. Thanks so much. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
RE: $$Excel-Macros$$ Need most recent date
Hi, I am assuming that your first unique invoice numberis in A2. Format B2 of the same sheet as Date, then enter the following: =Sumproduct(Max((OtherSheet!A2:A100=A2)*(OtherSheet!B2:B100))) Then copy down to the end of your unique data. If you can't get this to work, post again with a sample workbook. Regards - Dave. Date: Tue, 14 Sep 2010 17:33:44 -0700 From: n8dine4ma...@yahoo.com Subject: $$Excel-Macros$$ Need most recent date To: excel-macros@googlegroups.com I have a list of dates in one sheet. Col A = Invoice Number Col B = Payment Date There will be multiple rows for the same invoice number as payments are made against it until it is paid in full. Some of these rows will contain dates and some won't. The next sheet has the unique list of invoice numbers (meaning they don't repeat). I need a formula that will look at the first sheet and give me the most recent date if there is one. This is in Excel 2003. Thanks so much. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
RE: $$Excel-Macros$$ create a mcro in xls that launches other macros
Hi, Yes, you can use Call eg: Sub Run5Macros() Call Macro1Name Call Macro2Name Call Macro3Name Call Macro4Name Call Macro5Name End Sub Regards - Dave. Date: Mon, 13 Sep 2010 01:02:15 -0700 Subject: $$Excel-Macros$$ create a mcro in xls that launches other macros From: federico.mazz...@ikea.com To: excel-macros@googlegroups.com hi all, is it possible to create a macro that launches others 5 macros? someone can help me? many thanks. Fede -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
RE: $$Excel-Macros$$ Entering Data in Excel
Hi, 20 digits is too much for XL, which I think only handles 16 digits accurately. If you really need accuracy to that degree, you'll have to go elsewhere, or start splitting the number over 2 cells, which makes the maths quite complex. If you Google Big numbers in Excel you'll see lots of questions, and a few answers as well. Regards - Dave. Date: Mon, 13 Sep 2010 17:41:19 +0530 Subject: Re: FW: $$Excel-Macros$$ Entering Data in Excel From: vijumob...@gmail.com To: excel-macros@googlegroups.com You all might have see that even when i format the cell/column as number, it dont show me the exact number i do enter there. Vijay On Mon, Sep 13, 2010 at 11:35 AM, SUMIT VYAS svyas0...@gmail.com wrote: Dear, Right Click Cell Format Number Tab Select Number decimal place is 0 ok Regards Sumit Vyas +919755549483 On Sat, Sep 11, 2010 at 8:00 PM, Saurabh Patel saurabh.pa...@in.aegisglobal.com wrote: Change the cell format to Number by right clicking on cell. Regards, Saurabh Patel -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of viju mobile Sent: Saturday, September 11, 2010 5:39 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Entering Data in Excel Hi All, I am trying to input 20 digit number in a column. But it does not enter it correctly. Please guide me. Regards, Vijay -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 DISCLAIMER:The information contained in this electronic message and in any attachments to this message is confidential, legally privileged and intended only for the person or entity to which this electronic message is addressed. If you are not the intended recipient, please notify the system manager and you are hereby notified that any distribution, copying, review, retransmission, dissemination or other use of this electronic transmission or the information contained in it is strictly prohibited. Please accordingly also note that any views or opinions presented in this email are solely those of the author and may not represent those of the Company or bind the Company. This message has been scanned for viruses and dangerous content by Mail Scanner, and is believed to be clean. The Company accepts no liability for any damage caused by any virus transmitted by this email. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 -- The posting are downloaded from various sites so the
RE: $$Excel-Macros$$ Excel 97 - Windows 7
Hi, I have not heard that XL97 won't run on Windows 7, and would be very suprised if it were so. Regards - Dave. Date: Sun, 12 Sep 2010 11:43:15 -0700 Subject: $$Excel-Macros$$ Excel 97 - Windows 7 From: spa...@corbetteer.co.uk To: excel-macros@googlegroups.com Currently running Excel 97 on XP, everything works just fine. I am about to buy a new computer that will arrive loaded with Windows 7 I understand the Office97 / Excel 97 will not work on Windows 7, is this correct? I have no wish or desire to buy office 2007, complete waste of money since excel 97 does everything that I need. If I have to purchase Office 2007. Will all my current Excel workbooks (some are very heavy with VBA scripts) work correctly.Excel 97 - Windows 7 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
RE: $$Excel-Macros$$ Macro to reshade every other row after column sort
Hi, I assume your shading is currently done manualy. It could easily done with VBA or even more easily with Conditional formatting, as long as you aren't currently using it for something else. Select all cells in the block Open the conditional formatting dialogue box Select 'Formula is' Enter: =MOD(ROW(),2)=0 (to shade even rows) or =MOD(ROW(),2)=1 (to shade odd rows) Set the shading format you want OK Hope this helps. Regards - Dave. Date: Wed, 8 Sep 2010 13:56:09 -0700 Subject: $$Excel-Macros$$ Macro to reshade every other row after column sort From: heislerk...@gmail.com To: excel-macros@googlegroups.com I have four worksheets (WS1, WS2, etc.), and on each sheet a dynamic name ranged (WS1Data, WS2Data, etc.). These named ranges always begin on B8 but the columns and rows they span will vary based on the data (so I can't use an absolute reference). The rows in the range are alternatively shaded. Each column (for each range) has an invisible rectangle that, when clicked, sorts the rows in ascending or descending order, based on the values in the column. I did this using the Sort Data with Invisible Rectangles method (http://www.contextures.com/xlSort02.html). My problem is that when I sort the rows, the alternate shading gets all messed up. So I'd like to add a call (ShadeAlternativeRows) at the end of the Sort macro to reshade the newly sorted rows. The code I have works great for the first range (WS1Data), because it specifically references that range. How can I change this code so it references the range on the active sheet? Thanks. Code below. ### Public Sub ShadeAlternateRows() Dim r As Long Set curWks = ActiveSheet With curWks ' Automatically select the range to apply alternate shading With Range(WS1Data) ' remove any previous shading .Interior.ColorIndex = xlColorIndexNone ' Shade every second row lt gray (i.e., color index = 15) For r = 2 To .Rows.Count Step 2 .Rows(r).Interior.ColorIndex = 15 Next r End With End With End Sub ### -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com 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
