Posts by PTG258

    DisplayAlerts = False
    Selection.TextToColumns Destination:=Range("C6"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(11, 1)), TrailingMinusNumbers:=True

    I recorded the above macro to split some data, but when I run the code within a Loop, I get the Alert that asks:

    "Do you want to replace the contents of the Destination Cells?

    Well, of course I do, that is why I recorded the macro, to replace the contents of the destination cells!!

    I used the code:

    DisplayAlerts = False

    but this does not always seem to work, the pop-up will still display. ( on test, I have not used "= True" after the action, but it still pops up)

    Is "DisplayAlerts = False" the correct code to use on this particular function?


    Re: Formula to split DATE value to separate Cells

    The problem was the source of information in row 2.
    To fix it, I simply used the =NOW formula so it macro at the time of DATA_IN, then go from there, Copy and Paste Value, then go from there. It is enough for it to proceed


    Scenario 1:
    Event is due to start at: 12:00:00, January 27 2012 in a different time zone.
    Time zone of base computer is 11:30:00, January 27 2012
    Therefore a Variable is required to adjust Time Zone by 30 minutes +/-
    ( default setting in Sheet named "Settings" )
    By Default 3 settings to Activate Macro BEFORE, If Cell range = 1
    Variable Cell input (1) in Sheet "Settings" -60 seconds
    Variable Cell input (2) in Sheet "Settings" -30 seconds
    Variable Cell input (3) in Sheet "Settings" -15 seconds
    Add/Delete more if required Function.
    By Default 3 settings to Activate Macro AFTER, If Cell range = 1
    Variable Cell input (1) in Sheet "Settings" + 900 seconds (15 minutes)
    Variable Cell input (2) in Sheet "Settings" + 1800 seconds (30 minutes)
    Variable Cell input (3) in Sheet "Settings" + 3600 seconds (45 minutes)
    Add/Delete more if required Function.
    Scenario 2:
    If the 12:00:00 event was web queried at 11:50:00 and it suited criteria ( Because Cell range = 1 )
    Then it will Loop same web query till
    Variable Cell input (1) in Sheet "Settings" -60 seconds
    At this point Activate Macro A (Variable Cell input (1) ))
    If the 12:00:00 event was web queried at 11:50:00 and it did not suit criteria,Cell range will = 0 )
    and it will go to the next Event on a list of many Events in Time order.
    Scenario 3:
    If the 12:00:00 January 27 2012 in a different time zone was web queried
    on January 28 2012 for example, and if a Cell range was 2, then Activate Macro 5 (post data analysis required)
    if that same Cell range is 0, then Activate Macro 6 ( post data analysis NOT required)
    All real time time lists and web query are currently working, I made it all myself.
    But I am not able to do precision real-time based macro activators with Add and Subtract Time Formula
    or Functions correctly as Excel is design to do these types of methods correctly.


    I can supply the time sheets for your testings because the formats is specific to and may have to be changed
    to suit Time calculations.

    After testing on my system I am not sure if there is unforeseen scenarios or circumstances, so this may be a ongoing thing.

    I am not sure how to offer anyone a price on this type of work, but I'm willing to pay up to AU$75.00 ONO

    Re: Recording Macro Web Query and altering the web query Code


    I am not sure how I solved it, but works now.
    The problem was probably in the syntax.

    The solved working code looks like this:


    With ActiveSheet.QueryTables.Add(Connection:="URL;" & Range("A1"), Destination:=Range("$A$2")) & ".html"
    .Name = "7_1"
    .FieldNames = True
    'rest of macro

    Previous error non working code was this:

    With ActiveSheet.QueryTables.Add(Connection:="URL;" & Range("A1"), Destination:=Range("$A$2")) & ".html"
    .Name = "7_1"
    .FieldNames = True
    'rest of macro

    Maybe it was a fluke and did something esle, but it now works


    Sample of Recorded Web Query Macro and Alterations From Recording To Required Cell Reference method.

    I will try to explain,
    I needed to record a web query so I can select specific web tables and this work OK.


    .WebTables = "13,16,71,72,""pooldata"",75"

    BUT, when I make a minor tweak FROM:( recording).


    With ActiveSheet.QueryTables.Add(Connection:= _
    ' "URL;", Destination:=Range("$A$1"))[/B]

    TO ( preferred cell reference method)


    [COLOR="#0000CD"] With ActiveSheet.QueryTables.Add(Connection:="URL;" & Range("A1"), Destination:=Range("$A$2")) & ".html"

    WHERE RANGE "A1" is the same as /2012/2/7/ZZ/2 the Record Macro method,
    it does not work when I use a Cell Reference method.
    I get a error message.

    The Cell reference structre is exactly the same in both the recorded and the the altered method.

    It has worked before,( using cell referecnce) but this time is is not working

    What am I not doing correctly?


    Re: formula to separate numbers and text

    Thankyou, that worked.

    One more thing to fine tune.
    I see how it is structured,



    However the time's last 2 digits includes seconds.
    In the example it is 10:15:18

    I need the seconds to become 00 no matter what the time of the other digits are.

    So it shows, 10:15:00

    Otherwise it's perfect


    Note to consider, just in case.
    ( it is also important to consider keeping the 24 hour format, so it is 10:15:00AM
    If it was evening, it would be 22:15:00PM)

    I have uploaded a workbook to show where the macro finds a specific cell with a year-text and time in the one cell.
    This is the raw data/infomation from a web query.

    What would be the formula to separate the info in cell range E30 and place the time and date separately as shown?

    Or would I need to use VBA as it must be above as shown in spreadsheet.
    The "find" macro has to be used because the location may vary from time to time depending where it's placed in the raw.
    But it will always have the text "ReferenceA" 2 cells left.
    The letter "T" is not required.

    Thanks in advance

    Re: Number Rank code not working on 2007

    Apologies for delay in responding, connection and browser issues, ongoing.

    Thanks for the code, it worked perfectly and the extra Msg part would help.
    The only change I did to that was made it a timed message, (5 seconds), and the rest of the process can continue accordingly.

    Have not had time to look at why it worked in 2003 version and not 2007, but can see where it would not have worked in anycase.
    Will get back to it, for the record post the problem, when these connection issues are sorted.


    During some progressive migration of VBA projects from older 2003 versions of Excel and Office in general, the code below seems to work ok in VBA 2003,
    but does not work in Excel 2007
    The name of the worksheet was Sheet1 in 2003, but the only chnage I done was rename the new workbooks worksheet.
    It is simply supposed to add a number sequence in Column A if any of the cells have values in them in Column B
    The total rows are not always the same, sometimes there might be up 150 rows of data, other times as low as 7.
    So it would end up looking something like:

    Column A-----Column B
    RANK-------- TITLE
    or other times
    --infinately ( average upto 150, ~)

    'What am I missing here?
    Sub RANK_NO()
         For i = 1 To LastRow
            Worksheets("SCRAP").Cells(i, 1).Value = i - 1
             Worksheets("SCRAP").Range("A1").FormulaR1C1 = "RANK"
     Next i
    End Sub

    Re: Prompt For Save will not go away

    Try using the; Application.DisplayAlerts = False
    twice or 3 times sequentially.

    Application.DisplayAlerts = False
    Application.DisplayAlerts = False


    Application.DisplayAlerts = True

    Re: APPENDING text file logging

    Solved ( Partially )

    I would like to thank all members who helped with this.
    There are two final requirements, but not right away, and that is,

    To solve entirely;
    1: To have the option to print in real time if required.
    The requirement within the code has to be something like PRINT - True may mean On or False may mean Off.
    I'll try and figure it, but if I can't, hopefully someone can and post here on this thread, then that completes the entire question.
    Hence, Solved ( Partially ).
    There are sections of cell references which will require real-time data analysis or real time summary to be printed on a dot matrix,
    ( using a box of sheets continual feed reel type). The printer options may be set at generic/default.
    To do this, one can use the same code, simply rename. LogmyError1 to LogMyError2 and so on
    I no longer use LogMyError, there are no errors to log!, Point is, one can improvise to identify their own title, such as LogOfABC etc.
    Then when that is working, all is Solved and on with the next "bug"!
    2: When the text file file reaches a pre-defined file size it will save that file to the current date and start a new text file.
    So if it reaches 500Mb, then the save would be for example; 20111015-LogMyError or, LogMyError-20111015

    In summary #1
    There is still much amending to do now to clean up the workbook, delete a few sheets perhaps due to the old "logging" method.
    All this logging during the development of this project over the years can now finaly be in text files.
    May not be not a big deal for most, but for others or me it's another step forward of a progressive progressive phase,
    for now.

    Asking a question like this, then reading the Application's help files ( in VB Editor, 2007 ) starts to make sense. A bit like that old saying, "the penny drops".


    Re: APPENDING text file logging

    A Ha !
    It's delightful when one can answer their own questions

    Solution to cell reference logging


    LogMyError Workbooks("WORKBOOK1").Sheets("Sheet1").Range("A1")

    More to be revealed.

    Re: APPENDING text file logging

    Thank you trunten

    Below is the sample test log it did:


    The "testers" are


    Next part A & B of the question is, what syntax do I use to include to the logging, a cell reference on the same line?

    example A:
    LogMyError "UNABLE TO CLEAR2" cell reference Sheet 1 Range A1

    Part B is cell reference only:
    LogMyError cell reference Sheet 1 Range A1

    I assume it may be something like:
    LogMyError "UNABLE TO CLEAR2" & Sheets("Sheet 1").Range("A1).Value '?

    LogMyError "Sheets("Sheet 1").Range("A1).Value" '?

    It ought to look like:
    "20110919 03:12:05>UNABLE TO CLEAR1:>100"

    20110919 03:12:05>100"