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.

  • VBA Non-Loop Find Cell from Current Cell based on Value

    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
    Loop
    rowTill = x

    End Function


    [/VBA]

  • VBA function to get MAX/MIN of a range offset

    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?


    Code
    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!

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

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


    Batman,


    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.



    Baraakhalil,


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


    Best,
    Jerry

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

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


    Quote from BaraaKhalil;754427

    Try this


    Hi,
    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!!

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

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



    Hi,
    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?

  • Application.OnTime to repeat at fixed 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



    Hi,


    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

  • Application.OnTime to repeat at fixed time

    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


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


    Thanks!