Posts by Jong

  • Delete Characters

    Re: Deleting a set number of characters in a cell


    Just do something like this:


  • Reading individual characters

    Re: Reading individual characters


    If you are doing this in VBA, you can use the Left() and Right() functions
    For example:


    Left(MyString, 1) 'Will return the first char
    Right(Left(MyString, 2), 1) 'Will return the second char
    Right(Left(MyString, 3), 2) 'Will return the third
    Right(Left(MyString, 4), 3) 'Will return the fourth
    ...
    ...


    And so on..



    So, if you want to loop this:


    Dim I as Integer
    For I = 0 to MyString.Len
    MsgBox(Right(Left(MyString, I + 1), I))
    Next

  • Switch off the Enable/Disable window?

    This feature, (as far as i know), is to prevent malicious macros to run on your PC and cannot be removed programatically.


    Although, it is possible to remove the prompt on a computer manually by doing the following:


    Tools > Macros > Security


    then, select LOW security


    This will remove the prompt.

  • Macro, delete some rows

    Best thing to do in this situation is somethign like this:


    (Assuming PO-Box is entered in column A)



    Code
    For each cell in worksheets("A:A")
     if instr(UCase(cell.value), "PO BOX") <> 0 or instr(UCase(cell.value), "P.O. BOX") <> 0 or _
instr(UCase(cell.value), "P O BOX") <> 0 or instr(UCase(cell.value), "POBOX") <> 0 then


cell.entirerow.delete


next cell




    You can also use regular expressions for this, although since there are so many ways to write POBOX, your expression could get quite complicated.


    If your interested in doing it with regular expressions, here is a good read:
    http://www.aivosto.com/vbtips/regex.html

  • On-the-fly Comment Highlighting

    When a user enters a comment in a cell, and you would like to flag that cell in a different color to make it stand out as a commented cell, here's how.


    I initially wrote this because users were having trouble distinguishing which cells had comments. When a user enters a comment (RightClick > Insert Comment) in a cell, it will highlight the cell in a light shade of green to make it stand out.


    The only thing I didn't include is a way to remove the highlight if a comment is deleted. Although, that could be easily implemented.


    This code should be pasted in the "ThisWorkbook" object in your VBA editor:


  • Save all your Charts in GIF format

    This code snippet will allow you to save all your existing charts into a GIF format. It will save them in a folder called /Charts located in the ThisWorkbook.Path directory.


    This can be useful if you need to code an Index for your charts, or you want to output a Chart Report sheet in HTML or whatever. I found it extremely useful after I had finished implementing it, and I hope others will to.


    In order for the following code to work, you will need to add a reference to "Microsoft Scripting Runtime" in your Tools > References (VBA editor). So without further delay, here is the code:







    If anyone is interested, I can also post my code that displays all these GIF charts into a web browser. Enjoy.

  • saving in sequential numbering i.e 1,2 3 etc or by date/time

    Okay, first, in order to generate an incremented number, you need to find out what number you are currently using. To do this, I suggest using the "ThisWorkbook" object. In the "ThisWorkbook" object, paste the following code (or something like this):






    Okay, that takes care of the number. Now to save your new format you can paste this in any module:


  • Check if cell contains DATE

    Ok, here is my problem.



    When a cell is labelled as 24_MAY_04 or something similar, excel does not reckognize it as a DATE FORMAT. even if the Cell format is set to a DATE, it is still not a DATE FORMAT.


    If by chance a user enters this type of format in a cell, my macros will crash since the dates are incorrect. It crashes because I use the Data in a chart, and when it hits these values, Excel does not reckognize it as a date format.

  • Check if cell contains DATE

    I'm wondering how would you check to see if a cell contains a date format?


    is there a function similar to IsNumeric for dates?



    I figure, if it is a date, it would be automatically formatted as a DATE FORMAT.


    My main concern is, I need to check if a cell is reckognized as a DATE in excel.

  • VB Code for copying

    Code
    For I=1 to 10


Worksheets("My sheet").Range("B" & I & ":D" & I).Copy
Worksheets("My Sheet").Range("F" & I & ":H" & I).PasteSpecial Paste:=xlPasteAll


Next I

  • Swapping cell values

    my initial question was quite vague, sorry to have wasted your time.


    Also, thank you for answering my question :)


    There is no swap function "built-in" excel.

  • GMT Clock in Excel

    What you could do is get the user to specify what time zone he is in, and then adjust the rest of the value accordingly.


    The time zone the user will specify will be your comparison points.



    This is just a suggestion.

  • Swapping cell values

    What I meant to ask was, is there a SWAP function?


    lets say we have data in A and B
    to swap data, i need to:
    - Copy A to a temp
    - Copy B to A
    - Copy Temp to B
    - Destroy Temp


    I was just wondering if there was a pre-made function available. Although while waiting for my answer, I actually wrote my own, so its all good :)