Posts by Jong

    Re: Deleting a set number of characters in a cell

    Just do something like this:

    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))

    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.

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

    (Assuming PO-Box is entered in column A)

    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
    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:

    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:

    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.

    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:

    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.

    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.

    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

    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.

    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.

    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 :)