Posts by jerryexcel2

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Here my actually code, with a loop.
    I just need the same thing without the loop since it really drags on speed.

    [VBA]Function rowTill(col As Integer, startBar As Long, target As Variant) As Integer
    x = 0
    j = startBar

    Do Until dataSheet.Cells(j - x, col).Value = target
    x = x + 1
    rowTill = x

    End Function


    I want to use a User-defined function to find the max or min of a range that is also dynamic depending on the input variable.

    Anyone know how this works?

    Function X(Rng As Range, Period As Integer)
    X = Application.Max("Rng.Offset(-period, 0):Rng.Offset(-1, 0)")
    End Function

    for instance,
    for function X(A10, 5), should return MAX value of range("A5:A9")

    Many thanks!

    Re: VBA function to find last non-zero value in a column


    The macro is periodically getting data and update them in the range that the formula uses.
    I tested both with and without Application.Volatile, and it always gets #VALUE error.
    I tested manually changing the values in the range and it didn't get the error.
    the function is to be used from within the sheet, the macro has nothing to do with it, it simply gets data and update the range.


    Yes, it works great now. I'll do some more test to see if there will be any problems.

    And thank you both! You are both being very helpful!!


    Re: VBA function to find last non-zero value in a column

    Quote from BaraaKhalil;754427

    Try this

    This works as well.
    However when I did some test, when I use a macro to get data from external sources, it returns the last non-zero value of the range of the ACTIVE sheet, which is not the sheet that has the data and the function.
    Could you change the code a bit so that it specify which sheet is used for calculation.
    Thanks a lot!!

    Re: VBA function to find last non-zero value in a column

    Thanks for the explanation.
    I did some test, it appears that whenever I use a macro to get data from external sources.
    The custom function will go to #VALUE error, while the regular LOOKUP is fine.
    To get out of that #VALUE error I have to change the value of any cell.

    Is there any way around this so I would not have to correct the error every time?

    Re: Application.OnTime to repeat at fixed time

    Quote from cytop;753565

    Unless I'm missing something, why not just add TIMEVALUE(“00:01:10") to Now()...?

    Also, struggling to understand how 1m10s added to
    9:12:30 is 9:13:10


    Sorry I didn't explain it clearly.

    I meant to say it should run at every minute + 10 more seconds,
    so if now is 9:12:30, it should run 9:13:10
    if now is 9:12:50, it should run 9:13:10
    if now is 9:13:15, it should run 9:14:10

    I would like to run a macro at every minute + 10 seconds

    So if NOW is 9:12:30, it should run at 9:13:10

    I'm trying to add a minute to NOW and reset the seconds to 10

    Application.OnTime Now + TimeValue("00:01:00") - Application.Second(Now) + TimeValue("00:00:10") , macro