Hai Macros,
I want to make the condittional formatting for A Coloumn in an
Excel sheet, Condition is " It should only allow Alphabets [a-zA-z]
and should not allow any special characters and numbers.Could you please
help me out in this regard,
Thanks
Sai
On 17 October 2011 05:05, <excel-macros@googlegroups.com> wrote:
> Today's Topic Summary
>
> Group: http://groups.google.com/group/excel-macros/topics
>
> - Plotting without zero's at end of
> plot<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_0>[2
> Updates]
> - How to extract number in
> cell<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_1>[6
> Updates]
> - Formula needed to extract the text from
> string<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_2>[1
> Update]
> - vlookup return with pcture
> assinged<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_3>[2
> Updates]
> - Working out of Statistics on Exam Results URGENT solution
> needed!<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_4>[1
> Update]
> - Toggle a Cell
> value<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_5>[2
> Updates]
> - How to learn
> vba<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_group_thread_6>[4
> Updates]
>
> Topic: Plotting without zero's at end of
> plot<http://groups.google.com/group/excel-macros/t/7df552f0fd74cb48>
>
> Ken <ksgood...@gmail.com> Oct 16 12:29PM -0700
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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?
>
>
>
>
> "dguillett1" <dguille...@gmail.com> Oct 16 03:15PM -0500
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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
>
>
>
> Topic: How to extract number in
> cell<http://groups.google.com/group/excel-macros/t/5618aa528e0b1187>
>
> neil johnson <neil.jh...@googlemail.com> Oct 16 04:24PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Hi All,
>
> How to extract number form the cell .
>
> For example
>
> abc123abc
> wc34agh
> 783abcd
>
> Thanks
>
>
>
>
> "dguillett1" <dguille...@gmail.com> Oct 16 08:08AM -0500
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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
>
>
>
>
> Dilip Pandey <dilipan...@gmail.com> Oct 16 07:02PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Nice work Don..!!
>
> Regards,
> DILIPandey
>
>
> --
> Thanks & Regards,
>
> DILIP KUMAR PANDEY, mvp
> MBA,B.Com(Hons),BCA
> Mobile: +91 9810929744
> dilipan...@gmail.com
> dilipan...@yahoo.com
> New Delhi - 62, India
>
>
>
>
> NOORAIN ANSARI <noorain.ans...@gmail.com> Oct 16 10:44PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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/>
>
>
>
>
> Sam Mathai Chacko <samde...@gmail.com> Oct 16 10:55PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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)*(1&REPT("0",(ROW(INDIRECT("1:"&LEN(A1)))-1))))
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
>
> On Sun, Oct 16, 2011 at 10:44 PM, NOORAIN ANSARI
>
> --
> Sam Mathai Chacko
>
>
>
>
> Dilip Pandey <dilipan...@gmail.com> Oct 16 11:32PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Excellent formula Sam... Awesome....
>
> Regards,
> DILIPandey
>
>
> --
> Thanks & Regards,
>
> DILIP KUMAR PANDEY, mvp
> MBA,B.Com(Hons),BCA
> Mobile: +91 9810929744
> dilipan...@gmail.com
> dilipan...@yahoo.com
> New Delhi - 62, India
>
>
>
> Topic: Formula needed to extract the text from
> string<http://groups.google.com/group/excel-macros/t/d2a0719d9f7e402>
>
> Dilip Pandey <dilipan...@gmail.com> Oct 16 04:47PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> You are welcome.
>
> Regards,
> DILIPandey
> On 16 Oct 2011 14:16, "Anil Bhange" <anil.bha...@tatacommunications.com
> >
> wrote:
>
>
>
>
> Topic: vlookup return with pcture
> assinged<http://groups.google.com/group/excel-macros/t/8dd7d0523ed461a9>
>
> "prabhat.shrivasta...@gmail.com" <prabhat.shrivasta...@gmail.com> Oct
> 15 10:03PM -0700
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Hi, can u pls explain the formula being used in this sheet.
>
>
>
>
>
> Sam Mathai Chacko <samde...@gmail.com> Oct 16 04:40PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Since Noorain and Ashish aren't around, I'll step in. The main function
> used
> is the INDIRECT function, along with a combo Match function to return
> the
> position of the value being searched for.
>
> So for example, =INDIRECT("SourcePic!A"&MATCH($A$4,SourcePic!A:A,0))
> will
> become =INDIRECT("SourcePic!A2") or =INDIRECT("SourcePic!A3") or
> =INDIRECT("SourcePic!A4") or any relevant number that corresponds to
> the
> value selected in range A4 of the active sheet. Remember, SourcePic is
> a
> sheet that contains the pictures.
>
> This INDIRECT equation is given a range name, let us say Photo.
>
> We then insert a picture as a link. How do you do this? You copy a
> range,
> and paste special as picture link. U can use the camera tool also to do
> this.
>
> Once this is done, the inserted picture is selected, and you can now
> assign
> a formula to the picture in the formula bar. So you give your range
> name
> that was defined earlier, =Photo.
>
> As soon as you do this, your equation equates the cell to the relevant
> picture based on the value you have selected in A4 of the active sheet.
>
> The indirect function can be a very versatile one, when used wisely.
> The
> drawback of the INDIRECT function (which is also is advantage) is that
> the
> static value that is passed doesn't get updated if the reference gets
> changed. For example, the name of the sheet doesn't get updated when a
> user
> changes the sheet name. This can be a bit of a problem at times.
>
> In your case specifically, you can get rid of this nuisance by using
> the
> INDEX function.
>
> So you can use =INDEX(Player,MATCH($A$4,Player,0)) in place of the
> INDIRECT
> formula above. Player is a named range housing the relevant text
> entries, as
> well as the pictures thereof.
>
> Check the attachment for more clarity. The function that does all the
> work
> now is the MATCH function. Hope I have explained it enough. I have also
> put
> in a work around for the drawback of INDIRECT. HTH.
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
>
>
>
> On Sun, Oct 16, 2011 at 10:33 AM, prabhat.shrivasta...@gmail.com <
>
> --
> Sam Mathai Chacko
>
>
>
> Topic: Working out of Statistics on Exam Results URGENT solution
> needed!<http://groups.google.com/group/excel-macros/t/d29320974c199f8a>
>
> shaneallen <shanealle...@gmail.com> Oct 16 02:56AM -0700
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> hi can u show me how to import the info to the different cells in each
> field?
>
>
>
> Topic: Toggle a Cell
> value<http://groups.google.com/group/excel-macros/t/480cb96b64ce4e84>
>
> Cab Boose <swch...@gmail.com> Oct 16 06:36PM +1200
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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
>
>
>
>
>
> Sam Mathai Chacko <samde...@gmail.com> Oct 16 02:25PM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> 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)
>
> *
>
> --
> Sam Mathai Chacko
>
>
>
> Topic: How to learn
> vba<http://groups.google.com/group/excel-macros/t/f7bb5108b7d79608>
>
> Mr excel <excelkeec...@gmail.com> Oct 16 05:53AM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Hi group,
>
> i would like to know a specific method for learning Excel vba.I mean i
> m
> just getting perplexed & worried about how to learn excel vba on seeing
> this
> so many examples in this forum.I could not get a right point where to
> start
> in vba.
>
> Kindly suggest some tips or methods so that i can follow in learning
> excel
> vba.
>
> Thanks in advance.
>
>
>
>
> Venkat CV <venkat1....@gmail.com> Oct 16 07:28AM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Hi,
>
> Refer Below Links..
>
> http://www.cpearson.com/Excel/MainPage.aspx
> http://www.teachexcel.com/
> http://www.ozgrid.com/VBA/
>
>
> http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html
>
> *Best Regards,*
> *Venkat *
> *Chennai*
> *My Linked in profile <
> http://in.linkedin.com/pub/venkatesan-c/21/492/a71>*
>
>
>
>
> --
> *
> *
> *
> *
> *
> *
>
>
>
>
> XLS S <xlst...@gmail.com> Oct 16 09:57AM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> firstly learn recording then play with recorded code
>
> http://www.youtube.com/watch?v=Ycab4OiPug0
> http://www.internet4classrooms.com/excel_record_macro.htm
>
>
> http://office.microsoft.com/en-us/excel-help/record-and-use-excel-macros-HA001054837.aspx
> http://msdn.microsoft.com/en-us/library/7kyhdt1z%28v=vs.80%29.aspx
>
>
> --
> .........................
>
>
>
>
> Dilip Pandey <dilipan...@gmail.com> Oct 16 10:05AM +0530
> ^<https://mail.google.com/mail/html/compose/static_files/blank_quirks.html#1330f16c9252c264_digest_top>
>
> Hi Mr. Excel,
>
> I would suggest you to enable recording and do whatever you want to do
> in Excel window. Now go to code window (Alt + F11) and see how Excel
> transformed your actions into vba code and try to understand that.
> Change some references there and play that again to see your edited
> actions. Keep doing like this and you will learn automatically plus
> you can refer the links as well as mentioned by group members..
>
> Best of luck.
>
> Regards,
> DILIPandey
>
>
> --
> 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
>
--
ThanX,
Chudheer Kumar
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To 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
