Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

2011-04-25 Thread roberto mensa
hi karan unfortunately with very small numbers is small rounding errors. I attach a sample file regards r 2011/4/25 roberto mensa > rng =OFFSET(Foglio1!$A$1,,,50,50) > > regards > r > > > > 2011/4/24 karan > >> Hi >> >> Thanks for the formul

Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

2011-04-25 Thread roberto mensa
rng =OFFSET(Foglio1!$A$1,,,50,50) regards r 2011/4/24 karan > 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 wrote: > > see if it helps > > > > > > > > > > > > On Tue, Apr 19, 2011

Re: Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

2011-04-24 Thread roberto mensa
with the arrayformula: text in A2 definited name rng =OFFSET(Foglio1!$A$1,,,50,50) in B2: =MOD(LARGE(ISERROR(--MID("a"&SUBSTITUTE($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(

Re: $$Excel-Macros$$ discussexcel : Formula : Extract email address from sentence in cell

2011-03-27 Thread roberto mensa
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(OFFSE

Re: $$Excel-Macros$$ macro for adding comments to cells

2011-01-26 Thread roberto mensa
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 C

Re: $$Excel-Macros$$ macro for adding comments to cells

2011-01-26 Thread roberto mensa
look here http://www.contextures.com/xlcomments03.html#Picture regards r 2011/1/26 Seba > 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 s

Re: $$Excel-Macros$$ Multiple Find and Replace in Textboxes - Issue with multilines

2010-12-16 Thread roberto mensa
I'm not sure of having understood, try: Sub MFR_Textbox() Dim Wks As Worksheet Dim rngSrch As Range Dim rngFind As Worksheet Dim rngFound As Range Dim oShp As Shape Dim oTotalRng As Variant Dim oTxtRng As Variant Dim oTxtRng1 As Variant Dim oTxtRng2 As Variant

$$Excel-Macros$$ Re: Outlook Folder Items Information

2010-12-15 Thread roberto mensa
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 > > Hi > > Can any one let me know how to get the information about outlook folder > items (Inbox) and get them in reco

Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right

2010-12-06 Thread roberto mensa
In B1 =INDIRECT("A"&COLUMN(A1)) regards r 2010/12/6 0 1 > 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

Re: $$Excel-Macros$$ Error in Code

2010-12-06 Thread roberto mensa
replace Range("A1:B334") with Selection regards r 2010/12/6 vebhav jain > 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 > > -- > > -

Re: $$Excel-Macros$$

2010-11-29 Thread roberto mensa
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 > =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 Ro

Re: $$Excel-Macros$$

2010-11-29 Thread roberto mensa
=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 > 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 t

Re: $$Excel-Macros$$ Guidance needed for intercompany reco...stmt..

2010-11-29 Thread roberto mensa
without a sample sheet with your data is not understood your request regards r 2010/11/29 Jagadeesh B S > 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 wrote: > >> Hello all >> >> I

Re: $$Excel-Macros$$ Bold a Text with Help Of Formula

2010-11-29 Thread roberto mensa
2010/11/29 Chandra Shekar > 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

Re: $$Excel-Macros$$ Bold a Text with Help Of Formula

2010-11-28 Thread roberto mensa
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 > Guys, Can anyone Help me to Bold the text with the help of Formula > > Eg. > > A1 Cell Contains "ABC" > > I Want to

Re: $$Excel-Macros$$ copy range to other sheet but split it to 3 column

2010-11-26 Thread roberto mensa
2010/11/26 عمر > 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",

Re: $$Excel-Macros$$ copy range to other sheet but split it to 3 column

2010-11-25 Thread roberto mensa
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

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
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 > I see you've changed the page I would have used even > s

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
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 > hi Ashi

Re: $$Excel-Macros$$ Gnatt Chart

2010-11-25 Thread roberto mensa
the best download excel gant that i know is here: http://www.riolab.org/index.php?option=com_content&view=article&id=76:diagramma-di-gantt&catid=47:excel&Itemid=68 for the page traslate in english: http://translate.google.it/translate?js=n&prev=_t&hl=it&ie=UTF-8&layout=2&eotf=1&sl=it&tl=en&u=http%

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
VBA code must run fine. > > > Thanks & Regards > Ashish Jain > (Microsoft Certified Application Specialist) > (Microsoft Certified Professional) > http://www.excelitems.com > http://www.openexcel.com > ___

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
problem? > > > > Thanks & Regards > Ashish Jain > (Microsoft Certified Application Specialist) > (Microsoft Certified Professional) > http://www.excelitems.com > http://www.openexcel.com > ________ > > On

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
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

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

2010-11-25 Thread roberto mensa
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 > > > > ___

Re: $$Excel-Macros$$ Re: Macro Commands to Create Pivot Table in Excel

2010-11-24 Thread roberto mensa
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 > HI Raj, > > You can use the below mention code: > Focus on arguments and understa

Re: $$Excel-Macros$$ Fwd: Need Change!

2010-11-13 Thread roberto mensa
try this function changed Function ProperCase_r( _ ByVal s As String) As String '__ '¯¯ 'https://sites.google.com/site/e90e50/vbscript/regexp '__ '¯¯¯

Re: $$Excel-Macros$$ Fwd: User form

2010-11-09 Thread roberto mensa
2010/11/9 neil johnson > > > > > > 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. >

Re: $$Excel-Macros$$ Fwd: Village

2010-11-06 Thread roberto mensa
> > 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://

Re: $$Excel-Macros$$ Re: Help Required!

2010-10-30 Thread roberto mensa
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

Re: $$Excel-Macros$$ Re: Help Required!

2010-10-28 Thread roberto mensa
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 > Hi, > > This works great. Thanks for your efforts and help. Though I have a small > need as well. > > The code gives the ou

Re: $$Excel-Macros$$ Fwd: Practice

2010-10-26 Thread roberto mensa
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

Re: $$Excel-Macros$$ Sales Territory's MAP

2010-10-25 Thread roberto mensa
I use the powerful Google Chart API, look this link: http://code.google.com/intl/it-IT/apis/chart/docs/gallery/new_map_charts.html the chart tool: http://imagecharteditor.appspot.com/ the Google Static Maps API: http://code.google.com/intl/it/apis/maps/documentation/staticmaps/ my web site with a

Re: $$Excel-Macros$$ Re: Quite a bit of help needed for inexperienced MS Excel 2003 user.

2010-10-22 Thread roberto mensa
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 -- ---

Re: $$Excel-Macros$$ csv macro to convert to .txt

2010-10-22 Thread roberto mensa
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 > 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. > > Propert

Re: $$Excel-Macros$$ Help with DoEvents to show progress form, please?

2010-10-21 Thread roberto mensa
For examples you can look here: http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/ http://spreadsheetpage.com/index.php/blog/progress_bars_the_movie/ regards r 2010/10/21 roberto mensa > the module of userform is a class module. > you have two form then you ha

Re: $$Excel-Macros$$ Help with DoEvents to show progress form, please?

2010-10-21 Thread roberto mensa
acros to > run at the same time: my main code in one form, and the looping code > in the second form. Am I trying to do the impossible? > > Ed > > On Oct 20, 4:13 am, roberto mensa wrote: > > try this soluction: > > > > 'in the class moduleuserform2 > &g

Re: $$Excel-Macros$$ Dynamically assign values to an array using ComboBoxes

2010-10-20 Thread roberto mensa
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/g

Re: $$Excel-Macros$$ Dynamically assign values to an array using ComboBoxes

2010-10-20 Thread roberto mensa
2010/10/20 RemyMaza > 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

Re: $$Excel-Macros$$ Help with DoEvents to show progress form, please?

2010-10-20 Thread roberto mensa
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_

Re: $$Excel-Macros$$ Dynamically assign values to an array using ComboBoxes

2010-10-20 Thread roberto mensa
try in a form add some combobox and a button and add this code: Option Explicit Private Sub CommandButton1_Click() Dim v v = not_null_control_list() MsgBox Join(v, ";") End Sub Private Sub UserForm_Initialize() Dim c As MSForms.Control Dim v 'for example add same value to all combobox v = Array(1

Re: $$Excel-Macros$$ vba excel understanding objects : worksheet

2010-09-23 Thread roberto mensa
> 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 gr

Re: $$Excel-Macros$$ vba excel understanding objects : worksheet

2010-09-23 Thread roberto mensa
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 = W

Re: $$Excel-Macros$$ Excel quiz

2010-09-22 Thread roberto mensa
2010/9/22 Ayush > > 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 ... > > Calcul

Re: $$Excel-Macros$$ PLEASE UNPROTECT THE SHEET OF ATTACHED FILE

2010-09-13 Thread roberto mensa
look this link: https://sites.google.com/site/e90e50/vba/rimuoverepasswordexcel regards r 2010/9/13 vikas gupta > Dear All > > I have a protected excel file can any one unprotect it > > regards > vikas > > -- > >

Re: $$Excel-Macros$$ extract only product name

2010-09-01 Thread roberto mensa
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]+|[

Re: $$Excel-Macros$$ PASSWORD REMOVER

2010-08-18 Thread roberto mensa
https://sites.google.com/site/e90e50/vba/rimuoverepasswordexcel 2010/8/18 viju mobile > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.c

Re: $$Excel-Macros$$ Re: Exrtact only numbers.

2010-06-30 Thread roberto mensa
sample attached along... Regards r 2010/6/30 lucky singh > > 4 variours formulas reuired... > > sample attached along... > > Regards, > Jay S > > > > On Wed, Jun 30, 2010 at 3:21 AM, r wrote: > >> >> >> On 29 Giu, 12:05, lucky singh wrote: >> > Hi All, >> > >> > 1. I want to extract all the nu