Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?
rng =OFFSET(Foglio1!$A$1,,,50,50) regards r 2011/4/24 karan karankan...@gmail.com Hi Thanks for the formula. It does help, but while trying it on a new sheet its not working it gives an error #NAME? On Apr 19, 9:07 pm, ashish koul koul.ash...@gmail.com wrote: see if it helps On Tue, Apr 19, 2011 at 7:14 PM, karan kanuga karankan...@gmail.com wrote: Hi, Can any1 pls let me know how do i separate the text and nos in a given sentence. PFA the sheet where i need to separate the nos and text. 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. extract_nos_from_text(1).xls 42KViewDownload- 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$$ How to Separate Text and Nos in a sentence ?
hi karan unfortunately with very small numbers is small rounding errors. I attach a sample file regards r 2011/4/25 roberto mensa robb@gmail.com rng =OFFSET(Foglio1!$A$1,,,50,50) regards r 2011/4/24 karan karankan...@gmail.com Hi Thanks for the formula. It does help, but while trying it on a new sheet its not working it gives an error #NAME? On Apr 19, 9:07 pm, ashish koul koul.ash...@gmail.com wrote: see if it helps On Tue, Apr 19, 2011 at 7:14 PM, karan kanuga karankan...@gmail.com wrote: Hi, Can any1 pls let me know how do i separate the text and nos in a given sentence. PFA the sheet where i need to separate the nos and text. 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. extract_nos_from_text(1).xls 42KViewDownload- 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 extract+nos+from+text.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?
with the arrayformula: text in A2 definited name rng =OFFSET(Foglio1!$A$1,,,50,50) in B2: =MOD(LARGE(ISERROR(--MID(aSUBSTITUTE($A$2,.,0),ROW(rng),1))*ISERROR(--MID(SUBSTITUTE($A$2,.,0),ROW(rng)+COLUMN(rng),1))*IF(ISNUMBER(--MID(SUBSTITUTE($A$2, ,#)#,ROW(rng),COLUMN(rng))),90-ROW(rng)+MID($A$2,ROW(rng),COLUMN(rng))/10^6),COLUMN(A1))*10^6,10^6) ctrl+shift+enter and drop right regards r 2011/4/20 r robb@gmail.com as an alternative to solving of siti Vi Function GetNumbers_r(S As String, Index As Integer) Dim mc Dim RE As Object Set RE = CreateObject(vbscript.regexp) RE.Global = True RE.Pattern = (?:0|[+-]? _ (?:(?!0)\d+(?:\.\d+)? _ |[0]+(?:\.\d+)?)) S = Replace(S, ,, ) GetNumbers_r = Val(RE.Execute(S)(Index - 1)) End Function regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 : Formula : Extract email address from sentence in cell
ops ... set_c as definited name: =ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_ the array romula not with name is: =IF(ISERROR(SEARCH(@,A2)),,MID( LEFT(A2,SEARCH(@,A2)),MAX(IF(ISERROR(SEARCH(MID( A2,ROW(OFFSET($A$1,,,SEARCH(@,A2))),1),ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_)),ROW(OFFSET($A$1,,,SEARCH(@,A2 )+1,1000)MID(A2,SEARCH(@,A2)+1,MIN(IF(ISERROR(SEARCH(MID(A2 ,ROW(OFFSET($A$1,SEARCH(@,A2),,1000)),1),ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_)),ROW(OFFSET($A$1,SEARCH(@,A2),,1000-SEARCH(@,A2)-(MID(A2,MIN(IF(ISERROR(SEARCH(MID(A2 ,ROW(OFFSET($A$1,SEARCH(@,A2),,1000)),1),ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_)),ROW(OFFSET($A$1,SEARCH(@,A2),,1000))-1)),1)=.)-1)) regards r https://sites.google.com/site/e90e50fx/home/estrarre-indirizzo-mail-da-testo 2011/3/27 siti Vi villager.g...@gmail.com Dear Mr. Ayush Please try =TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND( ,A2 ,FIND(@,A2))-1), ,REPT( ,99)),99)) or (Array Formula:) =MID(LEFT(A2,FIND( ,A2 ,FIND(@,A2))),COUNT(SEARCH( *@,A2,ROW($1:$103)))+1,99) thank you and best regards siti Vi On Sat, Mar 26, 2011 at 9:52 PM, Ayush jainayus...@gmail.com wrote: Hello Group, Learn to extract email address from the string in the cell. Visit : http://www.discussexcel.com/functions Feel free to share ideas you might have for the same task. I will appreciate it. 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 estrai indirizzo mail-1.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ macro for adding comments to cells
look here http://www.contextures.com/xlcomments03.html#Picture regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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 for adding comments to cells
try Sub test() Dim rng As Excel.Range Dim v As Excel.Range Set rng = [a1:a10] For Each v In rng write_comment v.Offset(, 1), v Next End Sub Sub write_comment(rngc As Excel.Range, rngt As Excel.Range) If TypeName(rngc.Comment) = Nothing Then rngc.AddComment.Text End If rngc.Comment.Text CStr(rngt.Value) End Sub regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post 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$$ Re: Outlook Folder Items Information
look here if that's what you mean: http://www.erlandsendata.no/english/index.php?d=envbaolecontroloutlook Sub ListAllItemsInInbox() regards r 2010/12/13 Santosh Vishwakarma eclerxsant...@gmail.com Hi Can any one let me know how to get the information about outlook folder items (Inbox) and get them in recordset. The below link may help. Please Check let me know. http://books.google.co.in/books?id=FCNNMbukwsYCpg=PA113lpg=PA113dq=dao+can+return+information+about+exchangesource=blots=gmNbCegRJwsig=C-RkZJgm8yh0AnI-hyeoQGXZ9DQhl=enei=00IGTfSpKZDsrQfXqfyQDwsa=Xoi=book_resultct=resultresnum=1ved=0CBYQ6AEwAA#v=onepageq=dao%20can%20return%20information%20about%20exchangef=false Rgds Santosh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 Code
replace Range(A1:B334) with Selection regards r 2010/12/6 vebhav jain vebhav.j...@gmail.com Hi All, I am facing error when the range data changes from (B334) and i need to adjust manually the total number, so can you suggest any better way. Thanks, Vebhav -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 image/gif
Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right
In B1 =INDIRECT(ACOLUMN(A1)) regards r 2010/12/6 0 1 hhholme...@gmail.com The source document (sample.xls) contains these data: A 1 2 3 4 In my destination workbook, I would like to put a reference in A1 so when I copy the formula into B1, C1, etc., I get this: A 1 2 3 etc. Instead I get: A 0 0 0 etc. I've tried every variation of =sample.xls!$A$1 and none of them do it. Is there another way? This is only needed one time but I'm trying to avoid having to manually edit the formula across 20 columns, which is how many the formula needs to be copied into. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Bold a Text with Help Of Formula
2010/11/29 Chandra Shekar chandrashekarb@gmail.com Hi, Pls check this site it might help you. http://www.mrexcel.com/forum/showthread.php?t=20611 Thanks Chandra Shekar get.cell returns (not set) information about the formatting, location, or contents of a cell. A function used in a formula can return a value. It cannot make any changes to the workbook. There are some exceptions: Merge UnMerge AddComment ClearComments InsertIndent regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Guidance needed for intercompany reco...stmt..
without a sample sheet with your data is not understood your request regards r 2010/11/29 Jagadeesh B S jagg...@gmail.com Hello all, Could someone please help me for my requirement Thanks in advance Jagadeesh B S On Fri, Nov 26, 2010 at 12:44 PM, Jagadeesh B S jagg...@gmail.com wrote: Hello all I need to prepare the reconciliation report for my company and send it to all the person who is responsible for their a/c. I will be filling the date in the reconsheet only for column E and now I need to send only the a/c and the details for which they are handling. I need formulas to populate the values and a macro to integrate Excel and mailbox so that I can send it to all the people with the data highlighted in the box above. Please guide me to write the formulas for myself. Thanks for looking into it and helping me. Thanks in advance Jagadeesh B S 9963025705 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$
=OFFSET($B$1,SMALL(IF(MONTH($D$2:$D$29)=3,ROW($B$2:$B$29)),ROW(A1))-1,) CTRL+SHIFT+ENTER drag down regards r 2010/11/28 Anindya Roy anind...@gmail.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 Madhumita Sardar 39 5/3/1986 21 Moumita Debnath 34 12/23/1988 22 Poly Mukherjee 45 9/16/1988 23 Papia Nath 49 10/21/1979 24 Rituparna Pal 22 12/6/1983 25 Rini Banerjee 33 4/21/1988 26 Shrabani Dey 40 8/5/1975 27 Subhajit Sheet 49 3/10/1991 28 Soma Ghosh 24 12/19/1979 Thanks Regards Anindya Roy -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$
of course is: =OFFSET($B$1,SMALL(IF(MONTH($D$2:$D$29)=11,ROW($B$2:$B$29)),ROW(A1))-1,) regards r 2010/11/29 roberto mensa robb@gmail.com =OFFSET($B$1,SMALL(IF(MONTH($D$2:$D$29)=3,ROW($B$2:$B$29)),ROW(A1))-1,) CTRL+SHIFT+ENTER drag down regards r 2010/11/28 Anindya Roy anind...@gmail.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 Madhumita Sardar 39 5/3/1986 21 Moumita Debnath 34 12/23/1988 22 Poly Mukherjee 45 9/16/1988 23 Papia Nath 49 10/21/1979 24 Rituparna Pal 22 12/6/1983 25 Rini Banerjee 33 4/21/1988 26 Shrabani Dey 40 8/5/1975 27 Subhajit Sheet 49 3/10/1991 28 Soma Ghosh 24 12/19/1979 Thanks Regards Anindya Roy -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Bold a Text with Help Of Formula
with UDF you can not: look here: http://spreadsheetpage.com/index.php/oddity/a_user_define_function_cant_change_the_worksheet_oh_yeah/ regards r 2010/11/28 Rahul Kamal Gandhi myname.ra...@gmail.com Guys, Can anyone Help me to Bold the text with the help of Formula Eg. A1 Cell Contains ABC I Want to write the formula in any cell, lets take Cell B1 =AnyFormula(A1) And My output should be *ABC* But in Bold Format Any Formula or Code. Please Guys I am Struck Regards, Rahul Gandhi +91-9711772297 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ copy range to other sheet but split it to 3 column
2010/11/26 عمر omar27...@gmail.com Yes yes Very god But I want it by code Sub test() Dim rng1 As Excel.Range Dim rng2 As Excel.Range On Error Resume Next Set rng1 = Intersect(ActiveSheet.UsedRange, Range(A:A)) Set rng1 = Application.InputBox( _ Select series, _ Input, rng1.Address, , , , , 8) If rng1 Is Nothing Then Exit Sub On Error GoTo 0 If rng1.Columns.Count 1 Then Exit Sub On Error Resume Next Set rng2 = Application.InputBox( _ Select range output, _ Output, rng1(1).Offset(, 1).Address, , , , , 8) If rng2 Is Nothing Then Exit Sub On Error GoTo 0 Set rng2 = rng2(1) traslation rng1, rng2 End Sub Sub traslation( _ rngI As Excel.Range, _ rngO As Excel.Range, _ Optional c As Long = 3) Dim r As Excel.Range, lRow As Long Dim lCol As Long For Each r In rngI rngO.Offset(lRow, lCol).Value = r.Value lCol = lCol + 1 If lCol = c Then lCol = 0 lRow = lRow + 1 End If Next End Sub regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Speed up Excel Start Up (FASTER EXCEL)
hi Ashish, There are problems to use VBA.Environ(appdata) ? regards r 2010/11/25 OpenExcel.com 26may.1...@gmail.com Hi Friends, Visit http://www.excelitems.com/2010/11/speed-up-excel-start-up-faster-excel.html on http://www.excelitems.com Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.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/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$$ Speed up Excel Start Up (FASTER EXCEL)
2010/11/25 OpenExcel.com 26may.1...@gmail.com A reference to 'Visual Basic for Applications' must be made in order to access VBA Object model. yes of course. regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Speed up Excel Start Up (FASTER EXCEL)
perhaps I was misunderstood because of my bad English, i don't know where is the problem, I would not have asked ... is correct? where come you want to run the script? something goes wrong permissions? regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Ok Robert, then what is the problem? Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 5:13 pm, roberto mensa robb@gmail.com wrote: 2010/11/25 OpenExcel.com 26may.1...@gmail.com A reference to 'Visual Basic for Applications' must be made in order to access VBA Object model. yes of course. regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Speed up Excel Start Up (FASTER EXCEL)
hi Ashish in my Italian version it does not work. It has to be changed Application Data into Dati Applicazioni. While sub test runs properly: Sub test() Dim s As String Dim fso, f, e Set fso = CreateObject(Scripting.FileSystemObject) s = VBA.Environ(appdata) s = fso.BuildPath(s, Microsoft) s = fso.BuildPath(s, Excel) If fso.FolderExists(s) Then Set f = fso.GetFolder(s) For Each e In f.Files If fso.GetExtensionName(e) = xlb Then Debug.Print e.Path End If Next End If End Sub maybe you should be more explicit because I have not yet understood. regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Hey Friend, The script needs to be run in 'Application Data -- Microsoft -- Excel'. If you can go here manually then VBA code must run fine. Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 6:23 pm, roberto mensa robb@gmail.com wrote: perhaps I was misunderstood because of my bad English, i don't know where is the problem, I would not have asked ... is correct? where come you want to run the script? something goes wrong permissions? regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Ok Robert, then what is the problem? Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 5:13 pm, roberto mensa robb@gmail.com wrote: 2010/11/25 OpenExcel.com 26may.1...@gmail.com A reference to 'Visual Basic for Applications' must be made in order to access VBA Object model. yes of course. regards r -- --- --- Some important links for excel users: 1. Follow us on TWITTER 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;. .. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Gnatt Chart
the best download excel gant that i know is here: http://www.riolab.org/index.php?option=com_contentview=articleid=76:diagramma-di-ganttcatid=47:excelItemid=68 for the page traslate in english: http://translate.google.it/translate?js=nprev=_thl=itie=UTF-8layout=2eotf=1sl=ittl=enu=http%3A%2F%2Fwww.riolab.org%2Findex.php%3Foption%3Dcom_content%26view%3Darticle%26id%3D76%3Adiagramma-di-gantt%26catid%3D47%3Aexcel%26Itemid%3D68 regards r 2010/11/25 Chandra Shekar chandrashekarb@gmail.com Hi, I have downloaded gnatt chart from web. Please can anyone explain how to do this chart in steps. Thanks Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Speed up Excel Start Up (FASTER EXCEL)
I see you've changed the page I would have used even s =VBA.Environ (appdata) fso.BuildPath s = (s, Microsoft) fso.BuildPath s = (s, Excel) since excel until 2003 version also runs on mac regards r 2010/11/25 roberto mensa robb@gmail.com hi Ashish in my Italian version it does not work. It has to be changed Application Data into Dati Applicazioni. While sub test runs properly: Sub test() Dim s As String Dim fso, f, e Set fso = CreateObject(Scripting.FileSystemObject) s = VBA.Environ(appdata) s = fso.BuildPath(s, Microsoft) s = fso.BuildPath(s, Excel) If fso.FolderExists(s) Then Set f = fso.GetFolder(s) For Each e In f.Files If fso.GetExtensionName(e) = xlb Then Debug.Print e.Path End If Next End If End Sub maybe you should be more explicit because I have not yet understood. regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Hey Friend, The script needs to be run in 'Application Data -- Microsoft -- Excel'. If you can go here manually then VBA code must run fine. Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 6:23 pm, roberto mensa robb@gmail.com wrote: perhaps I was misunderstood because of my bad English, i don't know where is the problem, I would not have asked ... is correct? where come you want to run the script? something goes wrong permissions? regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Ok Robert, then what is the problem? Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 5:13 pm, roberto mensa robb@gmail.com wrote: 2010/11/25 OpenExcel.com 26may.1...@gmail.com A reference to 'Visual Basic for Applications' must be made in order to access VBA Object model. yes of course. regards r -- --- --- Some important links for excel users: 1. Follow us on TWITTER 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;. .. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Speed up Excel Start Up (FASTER EXCEL)
sorry is: s =VBA.Environ (appdata) fso.BuildPath s = (s, Microsoft) fso.BuildPath s = (s, Excel) fso.BuildPath s = (s, excel*.xlb) regards r 2010/11/25 roberto mensa robb@gmail.com I see you've changed the page I would have used even s =VBA.Environ (appdata) fso.BuildPath s = (s, Microsoft) fso.BuildPath s = (s, Excel) since excel until 2003 version also runs on mac regards r 2010/11/25 roberto mensa robb@gmail.com hi Ashish in my Italian version it does not work. It has to be changed Application Data into Dati Applicazioni. While sub test runs properly: Sub test() Dim s As String Dim fso, f, e Set fso = CreateObject(Scripting.FileSystemObject) s = VBA.Environ(appdata) s = fso.BuildPath(s, Microsoft) s = fso.BuildPath(s, Excel) If fso.FolderExists(s) Then Set f = fso.GetFolder(s) For Each e In f.Files If fso.GetExtensionName(e) = xlb Then Debug.Print e.Path End If Next End If End Sub maybe you should be more explicit because I have not yet understood. regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Hey Friend, The script needs to be run in 'Application Data -- Microsoft -- Excel'. If you can go here manually then VBA code must run fine. Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 6:23 pm, roberto mensa robb@gmail.com wrote: perhaps I was misunderstood because of my bad English, i don't know where is the problem, I would not have asked ... is correct? where come you want to run the script? something goes wrong permissions? regards r 2010/11/25 OpenExcel.com ashishj...@openexcel.com Ok Robert, then what is the problem? Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 5:13 pm, roberto mensa robb@gmail.com wrote: 2010/11/25 OpenExcel.com 26may.1...@gmail.com A reference to 'Visual Basic for Applications' must be made in order to access VBA Object model. yes of course. regards r -- --- --- Some important links for excel users: 1. Follow us on TWITTER 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;... -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ copy range to other sheet but split it to 3 column
try the file ... you can changed the number of columns for the split regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 serie_n_columns.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Re: Macro Commands to Create Pivot Table in Excel
hi Ashish, there are some differences in methods between 2003 and 2007 versions. The method Create of PivotCaches is not present into 2003 version and replaces the method Add. regards r 2010/11/24 OpenExcel.com ashishj...@openexcel.com HI Raj, You can use the below mention code: Focus on arguments and understand them, it's easy, you'll never forget. ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=Sheet2!R2C2:R6C6, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:=Sheet2!R12C2, _ TableName:=PivotTable1, _ DefaultVersion:=xlPivotTableVersion12 i Provide Source Data ii Provide Pivot Table version - 12 is for Excel 2007 and 11 is for Excel 2003. iii Provide Pivot Table Destination iv Provide Table Name for future operations and reference These all are the compulsory options. Try the code and let us know, if further help required. Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 24, 2:12 pm, Raj Mahapatra rajafs...@gmail.com wrote: hi Group, Can any one send me the macro commands to create pivot table in excel. Thanks Regards Rajesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Fwd: Need Change!
try this function changed Function ProperCase_r( _ ByVal s As String) As String '__ '¯¯ 'https://sites.google.com/site/e90e50/vbscript/regexp '__ '¯¯ Dim v, i As Long, x Dim re As Object Set re = CreateObject(vbscript.regexp) re.Global = True s = VBA.StrConv(s, vbProperCase) v = Split(an|a|the|or|and|on|over|abore| _ under|blow|between|near|beside|among, |) For Each x In v re.Pattern = \b VBA.StrConv(x, vbProperCase) \b s = re.Replace(s, x) Next Debug.Print s re.Pattern = _ (^|[.!?(]\b|[.!?(]\s+\b|\r\b|\f\b|\n\b)(.) ProperCase_r = _ re.Replace(s, $1§$2§) v = Split(ProperCase_r, _ §, , vbTextCompare) For i = 1 To UBound(v) Step 2 v(i) = VBA.StrConv(v(i), vbUpperCase) Next ProperCase_r = Join(v, ) End Function regards r 2010/11/13 amrahs k amrahs...@gmail.com Hi, First I would like to say thank to you. The script that you had given to me works fine though I have a constraint as- The below code is does not handle ( in the input. Please look into the below example. *Input: CASE MANAGEMENT CONFERENCE QUESTIONNAIRE (LOCAL RULE 12.7)* *Output: Case Management Conference Questionnaire (local Rule 12.7)* It does not handle the open paranthesis (. As a result the word LOCAL has been converted in local. Kindly look into this and help me to solve this. Function ProperCase_r( _ ByVal s As String) As String '__ '¯¯ 'https://sites.google.com/site/e90e50/vbscript/regexp '__ '¯¯ Dim v, i As Long, x Dim re As Object Set re = CreateObject(vbscript.regexp) re.Global = True s = VBA.StrConv(s, vbProperCase) v = Split(an|a|the|or|and|on|over|abore| _ under|blow|between|near|beside|among, |) For Each x In v re.Pattern = \b VBA.StrConv(x, vbProperCase) \b s = re.Replace(s, x) Next Debug.Print s re.Pattern = _ (^|[.!?]\b|[.!?]\s+\b|\r\b|\f\b|\n\b)(.) ProperCase_r = _ re.Replace(s, $1§$2§) v = Split(ProperCase_r, _ §, , vbTextCompare) For i = 1 To UBound(v) Step 2 v(i) = VBA.StrConv(v(i), vbUpperCase) Next ProperCase_r = Join(v, ) End Function Sub test() Dim s As String s = AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR MsgBox ProperCase_r(s) End Sub *Thanks,* *Sharma* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/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$$ Fwd: User form
2010/11/9 neil johnson neil.jh...@googlemail.com Hi, I make textbox in user form. The label of this texbox email . When i enter the email address with out using or forget @ and Dot(.). It should give me msg . please use valid email Id . Wht will be code of this . Please find the attache sheet. Private Sub CommandButton1_Click() If isMail(Me.TextBox1.Value) Then 'email ok Else MsgBox mail not valid End If End Sub Function isMail(sMail As String) As Boolean Dim re As Object Set re = CreateObject(vbscript.regexp) re.Pattern = ^\w+([-+.]\w+) _ *...@\w+([-.]\w+) _ *\.\w+([-.]\w+)*$ isMail = re.test(sMail) End Function regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Fwd: Village
Thanks for the link. But is there a way to get an english translation of the site? The link takes me directly to the page and to a language I do not understand :) I use Mozilla Firefox (If this is any help) :) Thanks Ashish hello Ashish, you can use traslate google: http://translate.google.it/translate?hl=itsl=ittl=enu=http%3A%2F%2Fsites.google.com%2Fsite%2Fe90e50%2Frandom-topics%2Fgiocareconleimmaginiimmaginarexcel However the thing that you are interested in the two files (version 2003 and 2007) with which you can create colorful sheets (version 2003) from a special graphic image. You can download them from the bottom of the page regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Re: Help Required!
the sub test is just one example of how to use the function into a routine. Sub test() Dim s As String s = AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR s = ProperCase_r(s) Debug.Print s End Sub r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Re: Help Required!
I don't understand which linguage do you use? In visual basic as I have proposed is a function you can pass any text and use its result regards r 2010/10/28 amrahs k amrahs...@gmail.com Hi, This works great. Thanks for your efforts and help. Though I have a small need as well. The code gives the output through message box. Due to this I am unable to copy the value (i.e) Output. Also the code is only execute for the input AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR. What I need to have is to applicable for any kind of inputs (i.e) the input value is not constant it may change as per the user requirement. Please help me to overcome this and many thanks in advance. Regards, Sharma On Wed, Oct 27, 2010 at 9:49 PM, r robb@gmail.com wrote: try this function: Function ProperCase_r( _ ByVal s As String) As String '__ '¯¯ 'https://sites.google.com/site/e90e50/vbscript/regexp '__ '¯¯ Dim v, i As Long, x Dim re As Object Set re = CreateObject(vbscript.regexp) re.Global = True s = VBA.StrConv(s, vbProperCase) v = Split(an|a|the|or|and|on|over|abore| _ under|blow|between|near|beside|among, |) For Each x In v re.Pattern = \b VBA.StrConv(x, vbProperCase) \b s = re.Replace(s, x) Next Debug.Print s re.Pattern = _ (^|[.!?]\b|[.!?]\s+\b|\r\b|\f\b|\n\b)(.) ProperCase_r = _ re.Replace(s, $1§$2§) v = Split(ProperCase_r, _ §, , vbTextCompare) For i = 1 To UBound(v) Step 2 v(i) = VBA.StrConv(v(i), vbUpperCase) Next ProperCase_r = Join(v, ) End Function Sub test() Dim s As String s = AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR MsgBox ProperCase_r(s) End Sub regards r On 27 Ott, 11:25, amrahs k amrahs...@gmail.com wrote: Hi Team, I want a macro in MS word that will do the following. It will first convert the upper case into title case and then replace every connecting word with lower case. *Example:* AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR *Required Output:* Agreement. The Adoption the Adult or Married Minor *Note:* The connecting word “THE” has been replaced by “the” however the macro left the first connecting word “THE” that followed by dot (.) Your help would be greatly appreciated. Thanks, Sharma -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/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$$ Fwd: Practice
try: Private Sub CommandButton1_Click() Dim item, rng As Excel.Range For item = 0 To ListBox1.ListCount - 1 If Me.ListBox1.Selected(item) = True Then Set rng = Sheet2.Range(A Rows.Count). _ End(xlUp).Offset(1, 0) rng.Value = Me.ListBox1.List(item, 0) rng.Offset(, 1) = Me.ListBox1.List(item, 1) End If Next End Sub regards r 2010/10/26 neil johnson neil.jh...@googlemail.com Hi Everyone, I need your help. I make listbox using user form in excel . in listbox there is data from sheet 3 . now i transfer selected data to sheet 2 . it is working but not in proper manner . when i select data it shows data in cell A range not completely . Please help me 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
Re: $$Excel-Macros$$ csv macro to convert to .txt
if you want to work with csv files closed, please open the csv file with Notepad, copy and paste the contents here. regards r 2010/10/21 CAN cdelano...@gmail.com I have a .CSV file see sample below which I need macro to convert to .txt file in an specific format. Orginal file will be like this. PropertyID Account#Date Time Net Funded 1212178 2 10/10/2010 11:02481 1212CAS 311 10/10/2010 20:2375 1212CAS 312 10/10/2010 8:30 158.43 1212CCP 5951B4 10/10/2010 7:51 209.94 1212CCP 5951B4 10/10/2010 8:30 43.7 1212CWG 401 10/10/2010 20:45748.72 1212EWP 1152535L10/10/2010 12:1667.22 1212EWP 1331716C10/10/2010 16:4867.22 1212EWP 1332412C10/10/2010 9:48 69 1212EWP 1332812C10/10/2010 11:0884.25 1212EWP 1352215P10/10/2010 19:36134.44 1212GWC 306 10/10/2010 9:15 275 1212GWC 604 10/10/2010 8:34 265.16 1212POR 107 10/10/2010 9:14 666 1212SSN 205 10/10/2010 18:21124.15 1212SSN 205 10/10/2010 9:00 249.07 1212VSM 103 10/10/2010 9:57 185 1212VSM NC 10/10/2010 10:14393 1212LP3 1490023 10/10/2010 15:5750 1212LP3 1503023 10/10/2010 12:57162.61 For fields in the order listed above PropertyID, Account#, Date Time ,Net Funded Notice that third field has date and time combined in the same cell. (sorry but that's how it is generated). For the output file I only need the date. The outfile needs to be in .txt as follows 1212178 2101020echeck00048100 1212CAS 00311101020echeck7500 1212CAS 00312101020echeck00158643 1212CCP 0005951B4101020echeck00020994 1212CCP 0005951B4101020echeck4370 1212CWG 00401101020echeck00074872 1212EWP 01152535L101020echeck6722 1212EWP 01331716C101020echeck6722 1212EWP 01332412C101020echeck6900 1212EWP 01332812C101020echeck8425 1212EWP 01352215P101020echeck00013444 1212GWC 00306101020echeck00027500 1212GWC 00604101020echeck00026516 1212POR 00107101020echeck00066600 1212SSN 00205101020echeck00012415 1212SSN 00205101020echeck00024907 1212VSM 00103101020echeck00018500 1212VSM 000NC101020echeck00039300 1212LP3 001490023101020echeck5000 1212LP3 001503023101020echeck00016261 The format is as follows: Property ID follow by 6 blank spaces Beginning in location 13 it will have the Account #. Field must is alphanumeric with a total of 13 digits. (zero filled to the left ) After Account #, goes the date in format YYMMDD (notice that no spaces will be required between field 2 (Account #) and field 3 (Date). After Date the words echeck (standard for all records). After the word echeck there are 4 blank spaces. The last field after the 4 blank spaces is Net Funded (numeric, 8 digits, no decimals ) I would really, really be greatful and super happy to anyone that can do this macro as explained. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Re: Quite a bit of help needed for inexperienced MS Excel 2003 user.
2010/10/22 Ashish Jain ashishj...@openexcel.com 3. In cell A10, use the formula below(any version of excel): =If(A9=0.5,2,If(A9=0.8,3,If(A9=0.8,4,3))) an alternative to the formula of Ashish Jain for point 3. =MAX(FREQUENCY(A9,{0.5;0.8})*{2;3;4}) regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 with DoEvents to show progress form, please?
try this soluction: 'in the class moduleuserform2 'showmodale=false Private Sub CommandButton1_Click() Dim d d = Now UserForm1.Show vbModeless 'your code 'for example Do Loop Until Now (d + TimeValue(00:00:10)) Unload UserForm1 End Sub 'in the class moduleuserform1 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True End If End Sub regards r https://sites.google.com/site/e90e50/user/excel-grafico-semaforo 2010/10/20 Ed from AZ prof_ofw...@yahoo.com I've got a pretty long-running macro inside a UserForm, and I wanted to open a second form just to show that things are still working and not crashed. It's pretty simple - four labels and I cycle around turning one at a time a different color. I call it from the main form with Private Sub CommandButton1_Click() DoEvents frmWaiting.Show vbModeless DoEvents Unfortunately, once the code drops into the second form, the main code in the first form never executes. I thought DoEvents was supposed to let the running code drop through to allow both forms to run their code. Obviously, I'm very incorrect!! 8( How can I make this happen correctly? Ed (XL2007, Vista Pro) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Dynamically assign values to an array using ComboBoxes
2010/10/20 RemyMaza remym...@gmail.com That works fantastic. It has some problems with how my form works though. Let's say for all 7 combo-boxes, the user can only select values in order. So if I need 4 devices added, I can't pick from the first two and then jump to combo box 5 and 6 to complete the form. I need to force the entries to flow in order. How would I do that or is that possible? Thanks for all of your help! Matt if I understand, it is more difficult ... try this soluction ... maybe there is simpler solution ... you can adjust the order and number of combobox, you must add a class module with the name cCombo: ' 'in standard module Public vOrder ' 'in userform class module Option Explicit Dim myCombo() As New cCombo Sub iniz_u() vOrder = Array(ComboBox5, ComboBox2, ComboBox3, ComboBox4, _ ComboBox1, ComboBox6, ComboBox7, ) End Sub Private Sub UserForm_Initialize() Dim c As MSForms.Control Dim v, l As Long, b As Boolean 'for example add same value to all combobox v = Array(1, 2, 3, 4, 5, 6, 8, 9) iniz_u For Each c In Me.Controls If TypeName(c) = ComboBox Then c.List = v ReDim Preserve myCombo(l) Set myCombo(l).CC = c 'If b Then c.Enabled = c.Name = vOrder(0) 'Else 'b = True 'End If l = l + 1 End If Next End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Dim i For i = 0 To UBound(myCombo) Set myCombo(i) = Nothing Next End Sub ' 'in the class module cCombo Option Explicit Public WithEvents CC As MSForms.ComboBox Private Sub CC_Change() If CC.ListIndex -1 Then enable_combo CC.Name End If End Sub Sub enable_combo(scombo As String) Dim i As Long, x As Long Dim c As MSForms.Control For i = 0 To UBound(vOrder) If vOrder(i) = scombo Then Exit For End If Next For Each c In UserForm1.Controls If TypeName(c) = ComboBox Then If c.Name = vOrder(i + 1) Then c.Enabled = True Else c.Enabled = False End If End If Next End Sub I worked fast ... but in italy is night :-) regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Dynamically assign values to an array using ComboBoxes
attack the sample file regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 selezione_combo_in_serie.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ vba excel understanding objects : worksheet
you need to use: Property Worksheets As Sheets of Excel.Workbook Public Sub CopyShNamesFromWkbToWkb2() Dim i As Long Dim wkb As Excel.Workbook Dim wkb2 As Excel.Workbook Set wkb = Workbooks(Nouveau_Feuille_Excel_1.xls) Set wkb2 = Workbooks(Classeur1.xls) 'Dim ws As Object 'Set ws = Worksheets For i = 1 To ws.Count wkb.Sheets(3).Cells(i, 4) = wkb2.Worksheets(i).Name'///A Next i End Sub regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ vba excel understanding objects : worksheet
For i = 1 To wkb.Worksheets.Count Fix this line regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel quiz
2010/9/22 Ayush jainayus...@gmail.com Dear Group, A small quiz for you...let see who answers it first and correct A raise to the power B equals C example 2 raise to the power 5 equals 32 Now you have to tell me how would you calculate the third variable if you have only two variables ... Calculate A if B C is given =C3^(1/B3) Calculate B if A C is given =LOG(C7;A7) Calculate C if A B is given =A1^B1 You have to provide excel formula only...no VBA allowed Regards, Ayush Jain Group Manager regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ PLEASE UNPROTECT THE SHEET OF ATTACHED FILE
look this link: https://sites.google.com/site/e90e50/vba/rimuoverepasswordexcel regards r 2010/9/13 vikas gupta vikas.63...@gmail.com Dear All I have a protected excel file can any one unprotect it regards vikas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ extract only product name
alternative to solving Aindril and if the code is not always located at the end try the UDF: Function re_alpha_numeric(ByVal s As String, _ Optional bGlobal As Boolean) As String Dim re As Object Set re = CreateObject(vbscript.regexp) re.Global = bGlobal re.Pattern = [a-zA-Z]*\d+[a-zA-Z]+|[a-zA-Z]+\d+[a-zA-Z]* re_alpha_numeric = re.Replace(s, ) End Function regards r 2010/9/1 Aindril De aind...@gmail.com Hi Kalyan, You can use the following formula in the cell B2 and then copy it down =LEFT(A2,LEN(A2)-(LEN(A2)-FIND(^^,SUBSTITUTE(A2, ,^^,LEN(A2) -LEN(SUBSTITUTE(A2, ,)) For more info on the above please check: http://www.eggheadcafe.com/software/aspnet/28074391/need-to-find-first---from-right.aspx Cheers Andy On Wed, Sep 1, 2010 at 3:41 PM, Kal xcel kalx...@gmail.com wrote: Dear Experts, I need a help. I have a list of alpha-numeric string, (Like : Skin Fruits Instant Fairness Fruit Bleach 14gm), all strings are not equal. I want to extract only product name (like: Skin Fruits Instant Fairness Fruit Bleach) from that string. File is attached. 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 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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$$ PASSWORD REMOVER
https://sites.google.com/site/e90e50/vba/rimuoverepasswordexcel 2010/8/18 viju mobile vijumob...@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 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$$ Re: Exrtact only numbers.
sample attached along... Regards r 2010/6/30 lucky singh lucky60...@gmail.com 4 variours formulas reuired... sample attached along... Regards, Jay S On Wed, Jun 30, 2010 at 3:21 AM, r robb@gmail.com wrote: On 29 Giu, 12:05, lucky singh lucky60...@gmail.com wrote: Hi All, 1. I want to extract all the numbers 2. I want to trim only the middle name Regards, Jay S Nadine.xls 18KVisualizzaScarica can you give an example of the result you want Regards r -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.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 ALL_NUMBERS_ONLY.xls Description: MS-Excel spreadsheet