Re: $$Excel-Macros$$ Toggle a Cell value
Hi Don Sam Don. appreciate your comment. I had not made it clear what I was looking for. Apologies. Sam, yes it is working great with dbl click or right click. It does not work with a single click, which is what I prefer. In use the cell would be selected and then may also need to click once again to change the cell to requirement. Your comments would be appreciated. Thanks Charlie Harris On Sun, Oct 16, 2011 at 12:13 AM, dguillett1 dguille...@gmail.com wrote: Your request was “when I SELECT the cell”. So, as Sam says, use another event such as doubleclick. Don Guillett SalesAid Software dguille...@gmail.com *From:* Cab Boose swch...@gmail.com *Sent:* Friday, October 14, 2011 8:32 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Toggle a Cell value Hi Don and Sam Thanks for your input. I use Win7 Excel and on a laptop. Don, your code work ok but small hiccup. The code/color changes ok when I select a different cell and then come back to actual cell again. Color and number change as required. However I would like to be able to leave the cursor in the active cell, somethimes, and each time I tap the cell on the laptop pad, it should change. That is without having to go to another cell first. In other words how do you get Excel to know when you select cell and then select again without moving. Maybe if we can after the first selection and change of color/code Excel places the active cell into a default cell nearby, and if the direction needs to be changed again I need to select that cell again. Or is there a way of refreshing the g15 cell without moving. Sam, your code brings up a run time error '13' type mismatch. The color is ok by format, but does the code allow for changing the value in the cell back and forth from 1 to 0 and 0 to 1 . Thankyou all Charlie Harris On Sat, Oct 15, 2011 at 6:36 AM, Sam Mathai Chacko samde...@gmail.comwrote: This should do it Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address(0, 0) = G15 Then Target.Value = Abs(CLng(Not -Target.Value)) End If End Sub Use a conditional format for coloring the cell. If you do not want to add a conditional format (which is faster than VBA by the way), and do the coloring also through VBA, then just add the following line within the If End IF statement in the VBA above. Target.Interior.ColorIndex = 4 - Target.Value Regards, Sam Mathai Chacko On Fri, Oct 14, 2011 at 6:09 PM, dguillett1 dguille...@gmail.com wrote: Right click sheet tabview codecopy/paste this to make the changes in col G Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column 7 Or Target.Count 1 Then Exit Sub If Target = 1 Then Target = 0 Target.Interior.ColorIndex = 4 Else Target = 1 Target.Interior.ColorIndex = 3 End If End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Cab Boose swch...@gmail.com *Sent:* Friday, October 14, 2011 2:33 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Toggle a Cell value Hi eveyrone See attached sheet. For a 12 volt motor speed direction, Forward or Reverse I want to have a value of a cell to toggle back and forth between 0 and 1 and cell color also to change each time between green and red. Prefer to use a cell than use a toggle button, unless a toggle button can change colors etc and the value of 0 or 1 from the toggle button can be used to export etc Your comments would be appreciated. Charlie Harris -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 -- Sam Mathai Chacko -- -- Some important
Re: $$Excel-Macros$$ Toggle a Cell value
I don't necessarily support this alternative, and I don't think it is popular among the old schools either, but it is effective nonetheless. There is a certain Hyperlink event which could be modified to bamboozle Excel and make it work to your advantage. The trick is to create a hyperlink to the same cell from within itself, and then use the event macro. I have attached a modified version of the same here. The green line is just added as an embellishment. You can ignore that if not needed. *Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) With Target.Parent If .Address(0, 0) = G15 Then Target.ScreenTip = Click to reverse direction of the motor .Value = Abs(CLng(Not -.Value)) End If End With End Sub Regards, Sam Mathai Chacko (GL) * On Sun, Oct 16, 2011 at 12:06 PM, Cab Boose swch...@gmail.com wrote: Hi Don Sam Don. appreciate your comment. I had not made it clear what I was looking for. Apologies. Sam, yes it is working great with dbl click or right click. It does not work with a single click, which is what I prefer. In use the cell would be selected and then may also need to click once again to change the cell to requirement. Your comments would be appreciated. Thanks Charlie Harris On Sun, Oct 16, 2011 at 12:13 AM, dguillett1 dguille...@gmail.com wrote: Your request was “when I SELECT the cell”. So, as Sam says, use another event such as doubleclick. Don Guillett SalesAid Software dguille...@gmail.com *From:* Cab Boose swch...@gmail.com *Sent:* Friday, October 14, 2011 8:32 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Toggle a Cell value Hi Don and Sam Thanks for your input. I use Win7 Excel and on a laptop. Don, your code work ok but small hiccup. The code/color changes ok when I select a different cell and then come back to actual cell again. Color and number change as required. However I would like to be able to leave the cursor in the active cell, somethimes, and each time I tap the cell on the laptop pad, it should change. That is without having to go to another cell first. In other words how do you get Excel to know when you select cell and then select again without moving. Maybe if we can after the first selection and change of color/code Excel places the active cell into a default cell nearby, and if the direction needs to be changed again I need to select that cell again. Or is there a way of refreshing the g15 cell without moving. Sam, your code brings up a run time error '13' type mismatch. The color is ok by format, but does the code allow for changing the value in the cell back and forth from 1 to 0 and 0 to 1 . Thankyou all Charlie Harris On Sat, Oct 15, 2011 at 6:36 AM, Sam Mathai Chacko samde...@gmail.comwrote: This should do it Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address(0, 0) = G15 Then Target.Value = Abs(CLng(Not -Target.Value)) End If End Sub Use a conditional format for coloring the cell. If you do not want to add a conditional format (which is faster than VBA by the way), and do the coloring also through VBA, then just add the following line within the If End IF statement in the VBA above. Target.Interior.ColorIndex = 4 - Target.Value Regards, Sam Mathai Chacko On Fri, Oct 14, 2011 at 6:09 PM, dguillett1 dguille...@gmail.comwrote: Right click sheet tabview codecopy/paste this to make the changes in col G Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column 7 Or Target.Count 1 Then Exit Sub If Target = 1 Then Target = 0 Target.Interior.ColorIndex = 4 Else Target = 1 Target.Interior.ColorIndex = 3 End If End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Cab Boose swch...@gmail.com *Sent:* Friday, October 14, 2011 2:33 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Toggle a Cell value Hi eveyrone See attached sheet. For a 12 volt motor speed direction, Forward or Reverse I want to have a value of a cell to toggle back and forth between 0 and 1 and cell color also to change each time between green and red. Prefer to use a cell than use a toggle button, unless a toggle button can change colors etc and the value of 0 or 1 from the toggle button can be used to export etc Your comments would be appreciated. Charlie Harris -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email
Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!
hi can u show me how to import the info to the different cells in each field? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ How to extract number in cell
Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Formula needed to extract the text from string
You are welcome. Regards, DILIPandey On 16 Oct 2011 14:16, Anil Bhange anil.bha...@tatacommunications.com wrote: Amazing... I know this group can help me... it save my lot of time... Thanks Dilip Ashish Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 -Original Message- From: Dilip Pandey [mailto:dilipan...@gmail.com] Sent: Saturday, October 15, 2011 09:59 PM To: excel-macros@googlegroups.com Cc: Noor Ali Sayyed; Anil Bhange Subject: Re: $$Excel-Macros$$ Formula needed to extract the text from string Hi Anil, You can use following two formula for your two columns respectively:- =MID(B7,SEARCH(ORIG:,B7)+5,(SEARCH(ID:,B7)-1-SEARCH(ORIG:,B7)-5)) =MID(E7,SEARCH(BNF:,E7)+4,(SEARCH(ID:,E7)-1-SEARCH(BNF:,E7)-4)) Sample sheet is also attached for your better understanding. Regards, DILIPandey On 10/15/11, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Ms-EXl-learner and Noorain, Could you help me with another formula for attached mail. Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Anil Bhange Sent: Wednesday, October 12, 2011 04:14 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Formula needed to extract the text from string Amazing... Ms-Exl-learner... This is exactly what I wanted... I was pretty sure this forum only can help me with solution... Thanks once again.. this saves my lot of time... Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Ms-Exl-Learner . Sent: Wednesday, October 12, 2011 03:34 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Formula needed to extract the text from string Hi Anil, I Assume that your data is in Column A and your first row is having the column header. So your data will start from 2nd row of Column A (i.e. from A2 cell) A1 Data A2 /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO 019481 A3 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011 A4 /ENTRY-10 OCTTRF/REF 6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110295 TRANSACTIEDATUM 10-10-2011 Copy and paste the below formula in any of the 2nd row cell (i.e. B2 cell) =TRIM(MID(SUBSTITUTE(/A2REPT(/,6),/,REPT(CHAR(32),255)),5*255, 255)) Drag the B2 cell formula below for the remaining cells of B column. Hope that helps! --- Ms.Exl.Learner -- On Wed, Oct 12, 2011 at 1:20 PM, Anil Bhange anil.bha...@tatacommunications.commailto:anil.bhange@tatacommunicati ons.com wrote: Hi Expert, I needed the formula which can extract the specific content from a Text, below is some sample excel cells To simplify this there is / sign after each content, and I need exact data which start after 4th / and till 5th / (which is mention below in Red). Can anybody tell me the formula. /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM DE MEERN /BNF/ INVOICENO 201100247 ACCOU NTNO019481 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/ 20110279 TRANSACTIEDATUM 10-10-2011 /ENTRY-10 OCT TRF/REF 6004ABS68300025023 /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/ 20110295 TRANSACTIEDATUM 10-10-2011 Anil Bhange Assistant Manager Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai - 400 001, India ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.commailto:anil.bhange@tatacommunicatio ns.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.commailto: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 :
Re: $$Excel-Macros$$ How to extract number in cell
Array formula solution =1*MID(J3,MATCH(TRUE,ISNUMBER(1*MID(J3,ROW($1:$9),1)),0),COUNT(1*MID(J3,ROW($1:$9),1))) Macro Sub ExtractNumbers() Dim r As Range Dim s As String Dim v As Variant Set r = Range(j3, Range(j3).End(xlDown)) s = Join(Application.Transpose(r)) With CreateObject(VBScript.RegExp) .Pattern = \D+ .Global = True s = Trim(.Replace(s, )) End With v = Split(s) With Range(l3).Resize(UBound(v) + 1) .Value = Application.Transpose(v) .Sort key1:=.Item(1), Order1:=xlAscending, Header:=xlNo End With End Sub Don Guillett SalesAid Software dguille...@gmail.com From: neil johnson Sent: Sunday, October 16, 2011 5:54 AM To: excel-macros Subject: $$Excel-Macros$$ How to extract number in cell Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to extract number in cell
Nice work Don..!! Regards, DILIPandey On 10/16/11, dguillett1 dguille...@gmail.com wrote: Array formula solution =1*MID(J3,MATCH(TRUE,ISNUMBER(1*MID(J3,ROW($1:$9),1)),0),COUNT(1*MID(J3,ROW($1:$9),1))) Macro Sub ExtractNumbers() Dim r As Range Dim s As String Dim v As Variant Set r = Range(j3, Range(j3).End(xlDown)) s = Join(Application.Transpose(r)) With CreateObject(VBScript.RegExp) .Pattern = \D+ .Global = True s = Trim(.Replace(s, )) End With v = Split(s) With Range(l3).Resize(UBound(v) + 1) .Value = Application.Transpose(v) .Sort key1:=.Item(1), Order1:=xlAscending, Header:=xlNo End With End Sub Don Guillett SalesAid Software dguille...@gmail.com From: neil johnson Sent: Sunday, October 16, 2011 5:54 AM To: excel-macros Subject: $$Excel-Macros$$ How to extract number in cell Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to extract number in cell
Dear Neil, Please try it to find integer.. Function only_integer(rng As Range) Dim i As Integer For i = 1 To Len(rng) If VBA.IsNumeric(Mid(rng, i, 1)) = True Then only_integer = only_integer Val(Mid(rng, i, 1)) End If Next End Function -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Sun, Oct 16, 2011 at 4:24 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to extract number in cell
In keeping with Noorain's VBA function, use this formula as an array. This extracts all numbers irrespective of whether the numbers are together, or scattered across the text like ABC123DEF, or A1B2CD3EF =SUM(IFERROR(MID(A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT(1:LEN(A1))),1)^0)*ROW(INDIRECT(1:LEN(A1))),ROW(INDIRECT(1:LEN(A1,1),0)*(1REPT(0,(ROW(INDIRECT(1:LEN(A1)))-1 Regards, Sam Mathai Chacko (GL) On Sun, Oct 16, 2011 at 10:44 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Neil, Please try it to find integer.. Function only_integer(rng As Range) Dim i As Integer For i = 1 To Len(rng) If VBA.IsNumeric(Mid(rng, i, 1)) = True Then only_integer = only_integer Val(Mid(rng, i, 1)) End If Next End Function -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sun, Oct 16, 2011 at 4:24 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Sam Mathai Chacko -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 extract number in cell
Excellent formula Sam... Awesome Regards, DILIPandey On 10/16/11, Sam Mathai Chacko samde...@gmail.com wrote: In keeping with Noorain's VBA function, use this formula as an array. This extracts all numbers irrespective of whether the numbers are together, or scattered across the text like ABC123DEF, or A1B2CD3EF =SUM(IFERROR(MID(A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT(1:LEN(A1))),1)^0)*ROW(INDIRECT(1:LEN(A1))),ROW(INDIRECT(1:LEN(A1,1),0)*(1REPT(0,(ROW(INDIRECT(1:LEN(A1)))-1 Regards, Sam Mathai Chacko (GL) On Sun, Oct 16, 2011 at 10:44 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Neil, Please try it to find integer.. Function only_integer(rng As Range) Dim i As Integer For i = 1 To Len(rng) If VBA.IsNumeric(Mid(rng, i, 1)) = True Then only_integer = only_integer Val(Mid(rng, i, 1)) End If Next End Function -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sun, Oct 16, 2011 at 4:24 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, How to extract number form the cell . For example abc123abc wc34agh 783abcd Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Sam Mathai Chacko -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Plotting without zero's at end of plot
I have a sheet where I am listing the data (numbers) in columns. I add data at the bottom of the columns as I get it. I plot a single column of data using the offset function within a Named range to set the data to plot against the number of data points input; plus a few blank rows (e.g. 30) below the data to make the chart look good. Named range CashFlow =OFFSET(Poker!$G$3,0,0,COUNTA(Poker!$G$3:$G $1004)+30,1) Source for chart: Series Values =Poker.xls!CashFlow I auto scale based on the offset function; testing for the data in the column. E.G. COUNTA. No issues there. I am using another column to filter the data in the first column to show a subset of the data in the first column. I use formulas in the second column to filter the data. The formulas are filled in for a 1000+ rows. When I try the same technique to plot the second column I get the data plus a few rows below the data with zeros in the plot. e.g. The plot goes to zero at the end for the rows below the data with formulas in the cells. I have cut off the extra rows; it works; but does not look the same as the first chart. Specifically: Plot goes all the way to the right side of the chart. Ideas on making the second plot with a few extra rows that do not plot as zeros? Where to add the rows? In the named range or in the source for the plot? And how? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Plotting without zero's at end of plot
Send your file with a complete explanation and before/after examples to dguillett1@gmail.com Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Ken Sent: Sunday, October 16, 2011 2:29 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Plotting without zero's at end of plot I have a sheet where I am listing the data (numbers) in columns. I add data at the bottom of the columns as I get it. I plot a single column of data using the offset function within a Named range to set the data to plot against the number of data points input; plus a few blank rows (e.g. 30) below the data to make the chart look good. Named range CashFlow =OFFSET(Poker!$G$3,0,0,COUNTA(Poker!$G$3:$G $1004)+30,1) Source for chart: Series Values =Poker.xls!CashFlow I auto scale based on the offset function; testing for the data in the column. E.G. COUNTA. No issues there. I am using another column to filter the data in the first column to show a subset of the data in the first column. I use formulas in the second column to filter the data. The formulas are filled in for a 1000+ rows. When I try the same technique to plot the second column I get the data plus a few rows below the data with zeros in the plot. e.g. The plot goes to zero at the end for the rows below the data with formulas in the cells. I have cut off the extra rows; it works; but does not look the same as the first chart. Specifically: Plot goes all the way to the right side of the chart. Ideas on making the second plot with a few extra rows that do not plot as zeros? Where to add the rows? In the named range or in the source for the plot? And how? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ auto complete feature in drop down list or combo box
can anybody help me with auto complete feature incorporation in drop down list or in a combo box.thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ JOB
*Experience:* 3 - 6 Years *Location:* Noida *Education:* UG - Any Graduate - Any Specialization,Graduation Not Required PG - Any PG Course - Any Specialization,Post Graduation Not Required *Industry Type:* IT-Software/ Software Services *Role:* System Admin *Functional Area:* IT-Other *Posted Date:* 11 Oct Job Description *JOB DUTIES – INCLUDES PRIMARY RESPONSIBILITIES ONLY* • Intermediate to advanced experience with SQL (Oracle DBMS experience preferred) • Intermediate to advanced experience with Microsoft Visual Basic for Applications (VBA) • Development of reports and analysis based on extraction of data from a DBMS and leveraging MS Excel including VBA • Run routine data audits for project and procedure compliance • Provide on-boarding and ongoing support or troubleshooting of standard and ad hoc reports • Identify root cause, investigate issues, and provide recommendations, workarounds, resolutions and benefits by minimizing risk and cost. *JOB REQUIREMENTS* *ESSENTIONAL POSITION FUNCTIONS *• High organizational skills • Ability to handle multi-tasks and meet deadlines • Ability to work efficiently and productively • Capable of exercising discretion and independent judgment • Ability to handle confidential information in a professional manner • Ability to communicate, both verbally and written, with a high level of confidence *Keywords:* Data reporting, data analysis, data extraction, SQL, VBA Desired Candidate Profile We are looking for Data Analysts responsible for development of reports and analysis, extraction of data. Must have strong expertise in *SQL and MS Excel*. Fair knowledge on *VBA* is also must. Company Profile Fiserv Inc Fiserv, Inc. (NASDAQ: FISV), a Fortune 500 company, provides information technology systems and services to the financial and insurance industries. Leading services include transaction processing, outsourcing, business process outsourcing (BPO), software and systems solutions. The company serves more than 18,000 clients worldwide—in the U.S. and Canada, Europe, Latin America, the Caribbean, and Asia-Pacific. Headquartered in Brookfield, Wis., the company is the leading provider of core processing solutions for U.S. banks, credit unions and thrifts. Fiserv was ranked the largest provider of information technology services to the financial services industry worldwide in the 2004, 2005 and 2006 FinTech 100 surveys. In 2007, the company completed the acquisition of CheckFree, a leading provider of electronic commerce services. Fiserv reported $4 billion in total revenue from continuing operations for 2007. Fiserv India Pvt Ltd. Fiserv India Pvt Ltd is a Fiserv affiliate that provides global delivery capabilities to boost Fiserv operations worldwide. It commenced its operations on 4th October 2005 at Noida, India. Its mission includes partnering with Fiserv, Inc. to give its worldwide clients a range of services focusing on IT, QA, BPO and Infrastructure in the BFSI domain across technology platforms. FGS operates from multiple centers in India out of Noida and Pune. It has also established its footprint globally offering near shore service offerings to its customers from Costa Rica, South America. Contact Details *Company Name:* Fiserv India Pvt Ltd *Website:* https://www.fiserv.com / www.resultscorp.com *Executive Name:* Mansha Garella *Address:* Fiserv India Pvt Ltd 5th Floor,Tower- Tech Boulevard, Plot no-6, Sector-127 NOIDA,Uttar Pradesh,India 201301 *Telephone:* 91-120-4095000 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
