$$Excel-Macros$$ Entering Formulas
I tried variations on the following: With Worksheets(Call List) .Range(L2).Formula = =LEFT(C2,FIND( ,C2)+2,99 .Range(M2).Formula = =IF(ISERROR(LEFT(MID(C1,FIND( ,C1)+1,99),1)*1),,LEFT(MID(C1,FIND( ,C1)+1,99),FIND( ,MID(C1,FIND( ,C1)+1,99 .Range(N2).Formula = =IF(M21,CONCATENATE(L2, ,M2),L2) .Range(O2).Formula = =MID(C2,LEN(L2)+2,99) End With But theyre unacceptable to Excel 2007. There must be a simple solution, but I can't find it in the Help file. Would someone please explain what I need to do? Thanks Jazzzbo -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ Fwd:
Dave, I think Praveen was looking for the count from right to left. Your example formula results with 17 not 8. However, building on your formula, you can simply subtract from the length of the cell. Here is counting from right to left: =LEN(A1)+1-FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))- LEN(SUBSTITUTE(A1, , Praveen, let us know what works for you. On Jul 2, 4:37 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi Praveen, It's a bit long-winded, but here's a formula to find the position of the last space in Cell A1: =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(A1, , Regards - Dave. Date: Fri, 2 Jul 2010 19:48:01 +0530 Subject: $$Excel-Macros$$ Fwd: From: bhspra...@gmail.com To: excel-macros@googlegroups.com Hi Friends, Is there any way to find the position of the space in a word from right side. Ex: United States of America - here the position of the space from right side is 8. -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER 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 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 athttp://groups.google.com/group/excel-macros/subscribe _ New, Used, Demo, Dealer or Private? Find it at CarPoint.com.auhttp://clk.atdmt.com/NMN/go/206222968/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ How to increase size and color of text in user form ?
Dear Krisha , Thank you very much. Worawat 2010/7/2 krishna mummina lovemekris...@gmail.com CLICK ON THE TEXT, YOU WILL GET THE PROPERTIES OR ELSE, RIGHT CLICK ON THAT AND PROPERTIES, THERE YOU WILL FIND WHAT YOU WANT. On 7/2/10, worawat kh worawat...@gmail.com wrote: Dear Group , How to increase size and color of text in user form ? Thanks, Worawat -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 -- Moo Moo' MyBlog http://ExcelBeginner.BlogSpot.Com Excel Tip http://MySTTAtrtip.BlogSpot.Com My trip picture -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ Fwd:
hi dave can you explain the formula how it is working =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN( SUBSTITUTE(A1, , On Fri, Jul 2, 2010 at 10:29 PM, praveen kumar bhspra...@gmail.com wrote: Here with have attached one pdf file. from that if i copy paste data to excel then it will show country name numbers together like this 1 United States 14,093,310 2 China 7,909,261 I want to seprate this country names numbers here. If have found out the position of the space from the right can use mid other formula to get the numbers separately. I hope this will makes you clear about my objective. Also let me if there is any other way to do the same. Thanks in advance On Fri, Jul 2, 2010 at 9:38 PM, krishna mummina lovemekris...@gmail.com wrote: Hi praveen, we can find the position and could you please tell me why you need it. Bye On 7/2/10, praveen kumar bhspra...@gmail.com wrote: Hi Friends, Is there any way to find the position of the space in a word from right side. Ex: United States of America - here the position of the space from right side is 8. -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ Require Froumula for change data in some other format
Thanks Paul!! Its working absolutely fine. is there any formula to perfome the same. Deepak -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 Formulas
Hi, =LEFT(C2,FIND( ,C2)+2,99 This is incorrect. It's missing a closing bracket, and the ,99 is wrong. What are you trying to acheive? Regards - Dave. Date: Fri, 2 Jul 2010 20:00:54 -0700 Subject: $$Excel-Macros$$ Entering Formulas From: jazz...@shaw.ca To: excel-macros@googlegroups.com I tried variations on the following: With Worksheets(Call List) .Range(L2).Formula = =LEFT(C2,FIND( ,C2)+2,99 .Range(M2).Formula = =IF(ISERROR(LEFT(MID(C1,FIND( ,C1)+1,99),1)*1),,LEFT(MID(C1,FIND( ,C1)+1,99),FIND( ,MID(C1,FIND( ,C1)+1,99 .Range(N2).Formula = =IF(M21,CONCATENATE(L2, ,M2),L2) .Range(O2).Formula = =MID(C2,LEN(L2)+2,99) End With But theyre unacceptable to Excel 2007. There must be a simple solution, but I can't find it in the Help file. Would someone please explain what I need to do? Thanks Jazzzbo -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 _ Need a new place to live? Find it on Domain.com.au http://clk.atdmt.com/NMN/go/157631292/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ SumIF Sumproduct
Hi Nandine, Please find attached excel for solution. Here you have to concatenate the values of column A, B, C and then compare it. You will get an idea from attached excel. Regards, Swapnil. On Tue, Jun 29, 2010 at 9:23 PM, Nadine S n8dine4ma...@yahoo.com wrote: I'm resending since I haven't gotten a response. I'm hoping that it's because it may have gotten lost in the shuffle instead of there being no formula for what I want to do. :) Thanks. -- *From:* Nadine S n8dine4ma...@yahoo.com *To:* excel-macros@googlegroups.com *Sent:* Mon, June 28, 2010 3:37:38 PM *Subject:* $$Excel-Macros$$ SumIF Sumproduct RowCol ACol BCol CCol D 2 alphanumerica number2 charactersCurrency 3 alphanumerica number2 charactersCurrency There are lots of rows. I need a formula in Cell E2 that will look through all the rows in columns A, B, C and, if it finds the same combination as those same columns in row 1, will return the sum amount in Col D. 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 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 SumIF Sumproduct_Solution.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Fwd:
Hi, A1 = United States of America =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(A1, , Firstly, we need to find out how many spaces there are in the text. We do this by finding the text length of the original text, Len(TRIM(A1)) then the text length of the text with all the spaces removed LEN(SUBSTITUTE(A1, ,)). Then find the difference LEN(TRIM(A1))-LEN(SUBSTITUTE(A1, ,)). In our case, 3. So the last instance of the space character is the 3rd one. The SUBSTITUTE function takes 4 arguements. 1: The original text, 2: text string you don't want, 3: text string you want instead, and 4: the optional arguement of what instance. So in our case, we want to replace the 3rd instance of the space character with something (anything) unique; for this, we use CHAR(1) (whatever that is). SUBSTITUTE(A1, ,CHAR(1),3) So replacing the 3rd instance of the space character with Char(1) we get United States ofCHAR(1)America Now we just use the FIND function to return the position of the CHAR(1) character. Cool Huh! Regards - Dave. Date: Sat, 3 Jul 2010 10:52:12 +0530 Subject: Re: $$Excel-Macros$$ Fwd: From: koul.ash...@gmail.com To: excel-macros@googlegroups.com hi dave can you explain the formula how it is working =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN( SUBSTITUTE(A1, , On Fri, Jul 2, 2010 at 10:29 PM, praveen kumar bhspra...@gmail.com wrote: Here with have attached one pdf file. from that if i copy paste data to excel then it will show country name numbers together like this 1 United States 14,093,310 2 China 7,909,261 I want to seprate this country names numbers here. If have found out the position of the space from the right can use mid other formula to get the numbers separately. I hope this will makes you clear about my objective. Also let me if there is any other way to do the same. Thanks in advance On Fri, Jul 2, 2010 at 9:38 PM, krishna mummina lovemekris...@gmail.com wrote: Hi praveen, we can find the position and could you please tell me why you need it. Bye On 7/2/10, praveen kumar bhspra...@gmail.com wrote: Hi Friends, Is there any way to find the position of the space in a word from right side. Ex: United States of America - here the position of the space from right side is 8. -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
Re: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing
i = ActiveWorkbook.Sheets.Count Does ActiveWorkbook.Sheets.Count return an integer or a pointer to an object in which case i can't be of both type. Then how for i ... should be ? Thx Pascal On Jul 3, 4:20 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi, I don't think you can say For i = 1 to i since i is undefined. Regards - Dave Date: Fri, 2 Jul 2010 11:52:06 -0700 Subject: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing From: bpascal...@gmail.com To: excel-macros@googlegroups.com Hi, Could you please tell me why this code is not working? From a programming background, i see no reasons why the code is not giving worksheets names through this for loop : Option Explicit Public Sub SheetList() Dim W As Worksheet Dim i As Integer Range(A1).Select For i = 1 To i = ActiveWorkbook.Sheets.Count Cells(i, 1) = W(i).Name Next i End Sub Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials 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 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 athttp://groups.google.com/group/excel-macros/subscribe _ Browse profiles for FREE! Meet local singles online.http://clk.atdmt.com/NMN/go/150855801/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ Fwd:
Thanks a lot Dave. On Sat, Jul 3, 2010 at 2:22 PM, Dave Bonallack davebonall...@hotmail.comwrote: Hi, A1 = *United States of America* =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(A1, , Firstly, we need to find out how many spaces there are in the text. We do this by finding the text length of the original text, *Len(TRIM(A1))* then the text length of the text with all the spaces removed *LEN(SUBSTITUTE(A1, ,))*. Then find the difference *LEN(TRIM(A1))*-*LEN(SUBSTITUTE(A1, ,))*. In our case, 3. So the last instance of the space character is the 3rd one. The SUBSTITUTE function takes 4 arguements. 1: The original text, 2: text string you don't want, 3: text string you want instead, and 4: the optional arguement of what instance. So in our case, we want to replace the 3rd instance of the space character with something (anything) unique; for this, we use CHAR(1) (whatever that is). *SUBSTITUTE(A1, ,CHAR(1),3)* So replacing the 3rd instance of the space character with Char(1) we get *United States ofCHAR(1)America* Now we just use the FIND function to return the position of the CHAR(1) character. Cool Huh! Regards - Dave. ** ** -- Date: Sat, 3 Jul 2010 10:52:12 +0530 Subject: Re: $$Excel-Macros$$ Fwd: From: koul.ash...@gmail.com To: excel-macros@googlegroups.com hi dave can you explain the formula how it is working =FIND(CHAR(1),SUBSTITUTE(A1, ,CHAR(1),LEN(TRIM(A1))-LEN( SUBSTITUTE(A1, , On Fri, Jul 2, 2010 at 10:29 PM, praveen kumar bhspra...@gmail.comwrote: Here with have attached one pdf file. from that if i copy paste data to excel then it will show country name numbers together like this 1 United States 14,093,310 2 China 7,909,261 I want to seprate this country names numbers here. If have found out the position of the space from the right can use mid other formula to get the numbers separately. I hope this will makes you clear about my objective. Also let me if there is any other way to do the same. Thanks in advance On Fri, Jul 2, 2010 at 9:38 PM, krishna mummina lovemekris...@gmail.com wrote: Hi praveen, we can find the position and could you please tell me why you need it. Bye On 7/2/10, praveen kumar bhspra...@gmail.com wrote: Hi Friends, Is there any way to find the position of the space in a word from right side. Ex: United States of America - here the position of the space from right side is 8. -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Regards, praveen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ First sips of Vba for Excel : Worksheet listing
Hi, ActiveWorkbook.Sheets.Count returns an integer. But I think you need: For i = 1 To ActiveWorkbook.Sheets.Count Cells(i, 1) = W(i).Name Next i But this can cause problems if a sheet has been deleted. The macro will fault if it calls a sheet that doesn't exist. You could use: i=1 For each s in Sheets Sheets(YourSheet).Cells(i, 1) = s.Name i = i + 1 Next s Change YourSheet to the sheet name you on which want to record your sheet names. Regards - Dave. Date: Sat, 3 Jul 2010 02:05:42 -0700 Subject: Re: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing From: bpascal...@gmail.com To: excel-macros@googlegroups.com i = ActiveWorkbook.Sheets.Count Does ActiveWorkbook.Sheets.Count return an integer or a pointer to an object in which case i can't be of both type. Then how for i ... should be ? Thx Pascal On Jul 3, 4:20 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi, I don't think you can say For i = 1 to i since i is undefined. Regards - Dave Date: Fri, 2 Jul 2010 11:52:06 -0700 Subject: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing From: bpascal...@gmail.com To: excel-macros@googlegroups.com Hi, Could you please tell me why this code is not working? From a programming background, i see no reasons why the code is not giving worksheets names through this for loop : Option Explicit Public Sub SheetList() Dim W As Worksheet Dim i As Integer Range(A1).Select For i = 1 To i = ActiveWorkbook.Sheets.Count Cells(i, 1) = W(i).Name Next i End Sub Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials 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 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 athttp://groups.google.com/group/excel-macros/subscribe _ Browse profiles for FREE! Meet local singles online.http://clk.atdmt.com/NMN/go/150855801/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 _ If It Exists, You'll Find it on SEEK. Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ First sips of Vba for Excel : Worksheet listing
Hi, Following is the correct code: Public Sub SheetList() Range(A1).Select For i = 1 To ActiveWorkbook.Sheets.Count Cells(i, 1) = ActiveWorkbook.Sheets(i).Name Next End Sub There is syntax error in your code in for loop And ActiveWorkbook.Sheets.Count this line will return integer Regards, Swapnil On Sat, Jul 3, 2010 at 12:22 AM, goofy_heron bpascal...@gmail.com wrote: Hi, Could you please tell me why this code is not working? From a programming background, i see no reasons why the code is not giving worksheets names through this for loop : Option Explicit Public Sub SheetList() Dim W As Worksheet Dim i As Integer Range(A1).Select For i = 1 To i = ActiveWorkbook.Sheets.Count Cells(i, 1) = W(i).Name Next i End Sub Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com 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
$$Excel-Macros$$ Need Help !!
I have piece of MACRO as below,
Sub LaunchHMRCMARK()
Dim NTCUSER As String
MARKPATH = InputBox(Enter HMRCMARK PATH - )
'Filename = InputBox(Enter XML file name here - )
Set oshell = CreateObject(WScript.Shell)
oshell.Run cmd, 9
Sleep 100
oshell.SendKeys (cd )
oshell.SendKeys MARKPATH
oshell.SendKeys ({ENTER})
oshell.SendKeys (Set PATH=C:\Program Files\Java\jre1.5.0_07\bin;%PATH%)
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
oshell.SendKeys ({ENTER})
Sleep 100
oshell.SendKeys (hmrccsp)
Sleep 100
oshell.SendKeys ( )
'oshell.SendKeys FILEPATH
'Sleep 100
'oshell.SendKeys (\)
'oshell.SendKeys Filename
'oshell.SendKeys ({ENTER})
End Sub
Once I run this, it generates a set of XML Code into Command prompt (DOS)
window.
I would like to extract this text string and store it on Excel,
My question is, will MACRO be able to do this?
TA
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.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$$ First sips of Vba for Excel : Worksheet listing
Even a little shorter. Public Sub SheetList() For i = 1 To Sheets.Count Cells(i, 1).Value = Sheets(i).Name Next i End Sub On Jul 3, 3:53 am, Swapnil Palande swapnilp1...@gmail.com wrote: Hi, Following is the correct code: Public Sub SheetList() Range(A1).Select For i = 1 To ActiveWorkbook.Sheets.Count Cells(i, 1) = ActiveWorkbook.Sheets(i).Name Next End Sub There is syntax error in your code in for loop And ActiveWorkbook.Sheets.Count this line will return integer Regards, Swapnil On Sat, Jul 3, 2010 at 12:22 AM, goofy_heron bpascal...@gmail.com wrote: Hi, Could you please tell me why this code is not working? From a programming background, i see no reasons why the code is not giving worksheets names through this for loop : Option Explicit Public Sub SheetList() Dim W As Worksheet Dim i As Integer Range(A1).Select For i = 1 To i = ActiveWorkbook.Sheets.Count Cells(i, 1) = W(i).Name Next i End Sub Pascal -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials 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 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- 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 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
$$Excel-Macros$$ Re: Converting absolute row reference to named range
I don't know what the range is that you are referring to but: Sub Macro2() Range(A1:A3).Select ActiveWorkbook.Names.Add Name:=MyRange, RefersToR1C1:= _ =Sheet1!R1C1:R3C1 Application.Goto Reference:=MyRange End Sub It is the Goto that I think you want Thanks, David On Jul 2, 1:28 pm, Kurt heislerk...@gmail.com wrote: I'm converting a lot of VBA code from using absolute references to named ranges. Here is an example I'm not sure how to revise. The original code references rows 2 to 259 in Column A. I have since named A2:A259 MyRange but the original code refers to iRow = 2 To 259. How can I change this to the equivalent of 'iRow = MyRange Below is the original code: ### Sheets(NameOfSheet).Select Cells.Select Range(A1).Activate ' column that contains the data to be evaluated; I'll replace A1 with MyRange Selection.EntireRow.Hidden = False For iRow = 2 To 259 ' rows that contain the data to be evaluated ' Not sure how to change the above to refer to the range If IsError(Cells(iRow, 2)) Then ' not sure how to change the reference to iRow, 2 Rows(iRow).Hidden = True End If If Not (IsError(Cells(iRow, 2))) Then If Cells(iRow, 2).Value = 0 Then Rows(iRow).Hidden = True End If End If Next ### Thanks for any pointers. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
$$Excel-Macros$$ Re: VLOOKUP PPT
Hi Neil, Thank you for sharing the useful file. Best Regards, Ayush Jain Group Manager. On Jul 3, 9:41 pm, neil johnson neil.jh...@googlemail.com wrote: Hi Everyone, This is ppt of vlookup . I am very lucky , I am the member of this team. I am sending attached PPT . Thanks and Regards vlookup.ppt 1782KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
$$Excel-Macros$$ EXRACT WHOLE TEXT AND NUMBER
DEAR SIR PLS ME I NEED EXRACT WHOLE TEXT AND NUMBER DIFFERENT CELLS WITH ONE FUNCTION OR TWO REGRADS KAVI NOTE -HOW ATTACH A SAMPLE FILE THIS GROUP -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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
