Posts by naira

    Re: Macro that runs when System Clock changes minute?

    Quote from royUK;683636

    Attach the workbook or post the code that you are using

    Public Sub NextTime()
        Application.OnTime Now + TimeValue("00:01:00"), "NextTime"
        Sheets("SAVED DATA").Rows("4:4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("SAVED DATA").Cells(4, 1) = Sheets("LIVE DATA").Cells(3, 1)
        Sheets("SAVED DATA").Cells(4, 2) = Sheets("LIVE DATA").Cells(3, 2)
        Sheets("SAVED DATA").Cells(4, 3) = Sheets("LIVE DATA").Cells(3, 3)
        Sheets("SAVED DATA").Cells(4, 4) = Sheets("LIVE DATA").Cells(3, 4)
        Sheets("SAVED DATA").Cells(4, 5) = Sheets("LIVE DATA").Cells(3, 5)
    End Sub

    Sample file attached.

    This is just a sample file to show that the code is run as soon I give the execute command and then every one minute from the time of first execution of the code.

    What I want is that even if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), its first run should be only at 09:15:00, then next run at 09:16:00 so on... every time the system clock's minute changes. I can make the code run/ start at 09:15:00, but as said in the initial post, it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds. Therefore, the next run is not after 60 seconds but after 60+20=80 seconds.


    I am looking for help to create a Macro that runs when system clock changes minute and then at every change of minute of system clock.

    For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...

    I already have a Macro that runs every minute from initial run time, but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.



    Re: Macro for Message Box auto-disappear after fixed time

    Hi jindon,

    Sorry the message box is not self-destructing/ does not self-disappear if not acknowledged. Also, above solution displays some extra message that is not required.

    Basically the Macro-2 message box is working properly and self disappearing if not acknowledged, but I need the message of Macro-1 displayed in message box of Macro-2

    Also, would prefer a message box that is completely clean (without exclamation marks etc. as are the code I have mentioned, since I will be using it for a lot of information). Your solution was somehow including a big exclamation mark in the message box.

    If anybody can give a better solution that does not use Macro-2 above, same will definitely be preferred.


    I have 2 Macros that pop up on Worksheet_Calculate event.

    Macro 1: This Macro pops Message from Rows of data: (Corresponds to Button 1 of attached sheet):

    and Macro 2: This Macro is for poping a Message Box that self destructs after 2 seconds if not acknowledged by User (Corresponds to Button 2 of attached sheet):

    Sub Test()
        Dim AckTime As Integer, InfoBox As Object
        Set InfoBox = CreateObject("WScript.Shell")
        AckTime = 2
        Select Case InfoBox.Popup("Click OK or do nothing within 2 seconds.", _
        AckTime, "This is your Message Box", 0)
        Case 1, -1
        Exit Sub
        End Select
     End Sub

    I need to have the message box created through Macro-1 NoData disaapear after 2 seconds if not acknowledged by User.
    Macro-2 is to help get started with some ideas in case a better and more elegant solution is not available.

    Sample file attached with both Macros

    I have a formula to get ratio of 2 nos. with 3 decimal places:


    For example the ratio of 1,234 : 4,321 is 1 : 3.502
    However, if I want the ratio of 2:3, the result is 1 : 1.5, whereas I want the result as 1 : 1.500
    Any ideas how to get this?

    Re: Formula to find largest value among nth-10 rows?

    Quote from danerida;681490

    Put this formula in C34

    Above formula works if the cells/ rows are actually blank.
    My cells contain formula the result of which is a blank cell, in which case the above formula fails.

    Used Countif instead of CountA and that works.



    I have a excel sheet where data is continuously being added to the top of a column, after shifting the remaining rows/ cells down.
    I want to find the largest value among the nth - 10 rows and result at the bottom of that column.

    For example:
    At 12:13 pm, the Range A would contain data in A2 to A31, but I want to find the maximum value among rows A2:A20 ignoring A21 to A31;
    At 12:14 pm, the Range A would contain data in A2 to A32, but I want to find the maximum value among rows A2:A21 ignoring A22 to A32; and so on....

    Please help with a formula that takes care of this dynamically expanding range.

    1. Would appreciate a non-macro soluiton since they are volatile and remove the undo option;
    2. Would appriciate a non-array formula since they can take a long time to process.

    Would be OK, if I have to use multiple calculations in multiple cells to arrive at the result.

    Sample file attached


    Re: Optimising a Column Insert VBA Code: Convert code from .Cells to .Range?

    Nopes, your code was only copying formula along with cell formatting etc. My original code is just copying values.
    Following Code helped me though:

    Sub CopyMyData()
        Worksheets("DATA").Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Worksheets("DATA").Range("C4:C7").Value = Worksheets("LIVE").Range("D4:D7").Value
    End Sub

    Thanks again for your prompt replies though.


    I need help in optimizing below Code that first inserts a column in sheet "Data" before/at Column D, then copies values from Rows 4 to 7 of Column C of Sheet "LIVE" and pastes the same on Sheet "DATA" at position Col. D.

    The problem is that the below code inserts one cell at a time, while I would like to convert it to a .Range code for faster run time.
    Requested since the no. of data to be inserted is about 2000, which leads to memory issues for running cell-wise inserts.

    Sub CopyMyData()
    Sheets("DATA").Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("DATA").Cells(4, 4) = Sheets("LIVE").Cells(4, 3)
    Sheets("DATA").Cells(5, 4) = Sheets("LIVE").Cells(5, 3)
    Sheets("DATA").Cells(6, 4) = Sheets("LIVE").Cells(6, 3)
    Sheets("DATA").Cells(7, 4) = Sheets("LIVE").Cells(7, 3)
    End Sub


    Re: Macro to Copy Column data and insert on another sheet

    No Issues about privacy.
    Am attaching a sample file about which you have been so helpful.
    You can start the Macro by pressing the 'Start' Button on the Live Sheet and Stop it with the Stop Button.
    When you open the sheet, please notice the cells highlighted in yellow colour are selected (on sheet 'Data') when the macro runs. What I was requesting is that it should not be selected by a general outline.
    Also, you would be happy to know that your original formula suddenly became immensely helpful in what I was trying to achieve. A big thank you for that.

    Re: Macro to Copy Column data and insert on another sheet

    Quote from Mumps;679866

    You could add this line at the end of the code.


    Nopes. That doesn't work. Gives some Microsoft for Visual Basic Application 400 error and Run-time error '1400': Select method of Range Class failed error and highlights that code line on debugging, incase the current Active sheet is different from Sheet1. Seems you are trying to set the Active cell as A1 in the background, but works only if the current active sheet is Sheet1. Any other method of setting the active cell as A1 for Sheet1 in the background?

    Re: Macro to Copy Column data and insert on another sheet

    Almost Perfect, Just Almost...
    Added your suggestion and also removed screen flashing with

    Application.ScreenUpdating = False 
    Application.ScreenUpdating = True

    However, the Cells pasted still remain selected when I go to Sheet1 (though not in the memory anymore). Any Idea to remove the selection. Requesting since this was not the case with your original code.
    Will save me an extra click or an extra Esc key press.
    And Oh! Thanks again for your prompt replies.

    Re: Macro to Copy Column data and insert on another sheet

    Quote from Mumps;679840


    Yup, That works!!
    But brings a new problem, viz. the cells being copied and pasted remain in memory selection (with the dashed line appearing around the cells being copied, as is usual in Excel when we copy-paste something, and the pasted cells too remain selected). The cells being copied therefore continue to remain in memory.
    If I press Ctrl+V i.e. paste, the cells copied are pasted as formulas in another place. This was not the case with your earlier code.
    Also, there is a violent screen flash when the code is started and executed. The code

    Application.ScreenUpdating = False
    Application.ScreenUpdating = True

    fails to stop the violent screen flashing.
    I guess the code


    used in place of

    .PasteSpecial xlPasteValues

    while selecting the range should help, but am not sure how to use .Value with this code.
    Can you advise a way to clear the cells being copied from memory/ selection.

    Re: Macro to Copy Column data and insert on another sheet

    Hi Mumps,
    You code works as requested.
    However, I forgot to mention that the data in Sheet2 is actually the result of a formula, so the code is currently copying the formula to Sheet1 not the actual values.
    Can you please modify it to paste absolute values from cells in Sheet2 to Sheet1 (values can be numbers in upto 3 decimals or even text). Also, the values can be blank or the value in the cell can be a calculation error too (#N/A), but the code should paste whatever is available in the cells at the time

    Patel: Your code works, but it uses the Activate command, therefore making my screen jump to Sheet1 every time it is executed, whereas my request was for background execution.
    Thanks for your support guys.

    I have a macro that copies data from Column D of Sheet2 and pastes values in Column B of Sheet1.

    Sub CommandButton1_Click()
        Sheets("Sheet2").Columns("D").Copy Sheets("Sheet1").Range("B1")
    End Sub

    However, I want that before pasting the values, the macro should insert a new column so that existing values in Column B of Sheet1 are shifted to the right and values are pasted in the empty Col. B of Sheet1
    Alternately, if someone could suggest a different solution to copy values from Cells D1 to D10 of Sheet2 and insert these as a new column B in Sheet1 (after shifting existing columns to the right).
    Please note that all the above will happen in background automatically and the Macro will be run from some other sheet so the Active Sheet need not be Sheet1 or Sheet2

    Re: Specifying VBA Calculation for a particular Worksheet different from Active Sheet

    Hi GC, Code works great and as per requirement, but if any of the cells in the range A1:B10 is blank during the macro run, it gives an error "Run Time Error '6': Overflow and highlights the code

    c.Value = c.Offset(0, -2).Value / c.Offset(0, -1).Value

    Can you please look into it again? Sample file with your code attached to check

    I have a simple VBA where values in Rows 1:10 of Col. A are divided by Values in Rows 1:10 of Col. B and result is given in Rows 1 to 10 of Col. C.
    This is code is run every 10 seconds:

    Sub Divide()
             Range("C1").Value = Range("A1").Value / Range("B1").Value
    Application.OnTime Now + TimeValue("00:00:10"), "Divide"
    End Sub

    The problem is:
    1. That the macro will run only on whatever is the current active sheet every 10 seconds, whereas I want it to run only on Sheet1.
    2. Only value in cell A1 is being divided with value in cell B1 and result appearing in cell C1, whereas I want calculations for Rows 1 to 10 of Col A and Col. B and respective result in Rows 1 to 10 of Col. C
    Any Ideas?
    PS: The values in col. A and B are being populated automatically from some other process and not entered manually, and result of Col. C is being used on another worksheet hence this requirement.