Posts by mikeopolo

    If anyone needs a formula solution, try this:


    With your data in Sheet1, column A, then on Sheet 2 create these formulae:
    A1: =INDEX(Sheet1!A:A,ROW()*2-1,1)
    B1: =INDEX(Sheet1!A:A,ROW()*2-,1)


    Copy these down as far as needed, until 0's appear.

    Re: VBA for PIVOT filter to remove blanks and zero values from Filter


    In similar work I have found it necessary to change all values first, then set the exceptions, for example:



    Maybe you can adapt this to your requirements.

    Re: Display Summary Of Table Data


    This task requires a macro solution.


    The following macro will re-arrange all your data on to a new worksheet titled "Result".
    It relies on your live file being exactly the same layout.


    It will stop if any of the role values are incorrect (your data contained "BU POC" for example :smash: )


    To install this macro to your worksheet:
    Copy this code text
    Open your workbook
    Press Alt+F11 to open the code editor
    In the Project window on the left, locate "VBA Project - workbook name"
    where workbook name is the name of your workbook
    Expand the entry by clicking on the + symbol, and double click "This Workbook"
    Paste the below code in to the big window on the right.
    Close this window
    To run the Macro, select Tools, Macro, ChangePlan, and click Run.
    A new worksheet called Result should be created.
    You can re-run it as much as you want, it will delete the output and re-create it.


    All the Steward values appear at the end of the range so you may need to re-sequence the columns to meet your requirements.



    Hope this works for you. Post back if you have any questions.
    Regards
    Mike

    Re: Updating Access


    BOF = beginning of file
    EOF = end of file
    together meaning the table is empty.


    I don't know enough yet to comment on the error message.
    Regards
    Mike

    Re: VB Sql query using cell value as parameter


    Instead of:


    "HAVING (vogctotalhoursreported.ReportMonth=' " & RepMonth & " ')"


    Try:


    "HAVING (vogctotalhoursreported.ReportMonth='" & RepMonth & "')"


    ie delete the two spaces which will end up around the variable value


    But I'm not sure that will cure the error you are getting.


    Regards
    Mike

    Re: Greater than, less than


    Not sure about your requirements, but your logic seems to say "if the date falls in the current month, respond x, if not, respond y".


    In which case:
    With the test date in A1
    B1 formula: =if(month(now())=month(a1),"mesage1","message2")
    or, for no message when outside current month:
    B1 formula: =if(month(now())=month(a1),"message","")



    Regards
    Mike

    Re: SQL Syntax - Query Access from Excel


    Have you resolved this issue?


    Definitely stick to single quotes for string variable values.


    I've had problems with anything except inner joins, and made some progress by using MS Query to generate the same query, and viewing the SQL statement that underlies it. This may help you.


    Regards
    Mike