Posts by Denis

    Chris,


    Nice solution. I was trying to craft a solution using "AutoFilter", since I was experimenting with that for another post. I'd never used a Filter in code before.


    :thumbup: @ CD :coolrsvd:

    Quote

    Originally posted by bruno


    can this be done, please help:D if it can please explain it in simple terms:eureka:


    Just kidding Bruno.


    Highlight all of your cells with the data. I'm assuming that they are all in the same column with no blank spaces. The easiest way to accomplish this is to:


    1. Select the topmost cell in the column, e.g. A1
    2. Hold CTRL+SHIFT and then press the down arrow.


    OK, once we've done that, go to " Data" menu and select "Text To Columns".


    On the Text To Columns window, select "Delimited" and then hit "Next".


    In this window, choose "other" for type of delimter and use the minus/hyphen sign -


    Hit Finish.


    Now you will have two columns, from your example, the first column will contain data like "Animals" and the other column will contain the data " House of The Rising Sun". (note the SPACE in front of "House")


    To get rid of that SPACE we're going to use the TRIM function.


    In the topmost cell of next column over from the song titles type in this formula.


    =TRIM(B1)


    Then double-click on that little black box on the excel cursor.


    I'm assuming that your data is originally in Column A and begins in cell A1. You can change that TRIM formula to whatever you need.


    Any problems, just repost.

    Here's the code to make it run only on sheets names "Sheet2" and "Sheet3":



    We regards to the digital signature issue. I'm assuming that your security is set to "high" which automatically disables workbooks with macros from anyone but a Trusted Source.

    Oh you mean Digital Signatures and all that malarky? I've never really used them I'm afraid.


    Actually, the code I gave you, it's probably best if you remove the two statements that say


    Application.EnableEvents = False (and the second one = True


    They're just more hassle than they're worth.

    What range do you want to limit this function to? It will be easier for me to write this explicitly rather than give you a generic example.

    In cell B1 type in the formula "=UPPER(A1)". Exit the cell.


    Highlight cell B1 with the excel cursor (the black border thingy)..


    There is a little square on the excel cursor on the bottom right hand corner. Double click on this and your formula will be copied to B7989 .

    It doesn't do the same thing. That's what I thought. It does what the OP is trying to correct.


    There is more than one agent whereas in your spreadsheet "blaa" is the only agent. In the example I've attached, I've added the agent "Denis" with a value of 200.


    What the OP wants to highlight the max for "blaa" and the max for "Denis". Not just the max in the column.

    The example is to show you how to use the UPPER and LOWER functions. Not to solve your problem. There wasn't enough information to solve your specifc problem.


    To use the UPPER function, you simply type "=UPPER(CellReference)" into a specific cell, where "CellReference" is the cell that has the text you want to convert to UPPERCASE e.g.


    <CENTER&gt;<TABLE borderColor=#c0c0c0 cellSpacing=0 width="60%" align=center border=1&gt;<TBODY&gt;<TR&gt;<TD bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD align=right&gt;<FONT color=white size=2&gt;___Running: xl2000 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=#d4d0c8 colSpan=5&gt;<TABLE height=10 width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb7552&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb3707.sltNb8739.value);' type=button value="Copy Formula" name=btCb8704&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb3707&gt;<TD align=middle width="5%" bgColor=white&gt;<SELECT onchange="document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value" name=sltNb8739&gt;<OPTION value==UPPER(A1) selected&gt;B1<OPTION value==UPPER(A2)&gt;B2<OPTION value==UPPER(A3)&gt;B3<OPTION value==UPPER(A4)&gt;B4<OPTION value==UPPER(A5)&gt;B5<OPTION value==UPPER(A6)&gt;B6<OPTION value==UPPER(A7)&gt;B7</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=120 value==UPPER(A1) name=txbFb8093&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<BR&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Denis</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[0].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;DENIS</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;John</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[1].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;JOHN</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Paul</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[2].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;PAUL</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;George</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[3].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;GEORGE</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Pete</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[4].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;PETE</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;TheDeadGuyBefore Beatles were famous</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[5].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;THEDEADGUYBEFORE BEATLES WERE FAMOUS</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Ringo</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb3707.sltNb8739.options[6].selected=true; document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;RINGO</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD colSpan=5&gt;<U&gt;Sheet2</U&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;<BR&gt;<BR&gt;<FONT color=#339966 size=1&gt;The above image was automatically generated by [HtmlMaker 2.12] </FONT&gt;<FONT color=#339966 size=1&gt;If you want this FREE SOFT, <A href="http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm"&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT color=#339966 size=1&gt;This code was graciously allowed to be modified: by <A href="mailto:[email protected]"&gt;Ivan F Moala</A&gt; All credit to <A href="mailto:[email protected]"&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;




    If you are wanting to convert a whole lot of values, then Dreamboat has given you a good procedure to follow.

    OK, this is going to require VBA or you can mess about with your AutoCorrect, which I don't advise.


    The VBA method:



    To use this:


    1. open your workbook
    2. Hit ALT+F11
    3. Double click on "ThisWorkbook" in the Project Explorer
    4. Paste the code into the Window that appears.


    You can start typing "A", "AA", "SCL" all over your workbook and they will be replaced by the appropriate numeric value.


    The second question I was asking you was, do you want to limit what cells you can type A, B, C, D, E, AA and SCL into and have them converted? i.e. only change values on Sheet1 in Column A?


    The way it is set up just now is that if "A" is typed into any cell in the workbook, it will be converted to "2".