Help with the Find() VBA command and variable rows in results

  • Hi all,

    There is a dump of financial data each week which are Commitments (for expenditure) which can be up to 700 rows. These are goods which have been ordered, but not yet invoiced from the supplier.

    They are organised in rows where column A contains the expense number, which is always a five digit number.

    I have to input a formula into column G whenever a row in column A starts with a 5 digit number beginning with 15. So, all the numbers in column A are unique and will range from 10000 to 99999, and I'm only looking for 15000 to 15999.

    I have attached an example of the raw data (coloured light blue).

    Here is my code, and it's the Find() routine which is failing on finding the first 15XXX number:

    For i = 9 To LastRow
            'Find the first Ordered Work Commitment
            Selection.Find(What:="15???", After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
            Range("G" & i).Formula = "=(F" & i & "+1)"
        Next i

    Each Job Number can have multiple purchase orders, and I really need the last line to be the amount to report in Column G.

    While the code above generates a formula just to capture the first purchase order amount, my coding skiils are nowhere good enough to generate this properly. I was going to ask for help with variable row numbers next - so that's why the code is so skinny atm.

    The attached sheet includes what I want the result to look like (column G):

    I have other routines which pick up this figure, so it's just generating the correct forumla based on a variable amount of rows.

    Any help would be appreciated.

    Thank you,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!