Posts by BDGibson4

    All,


    I am stuck on what I thought would be the "easy" part of a project - got through all the VBA/SQL lookup part.


    What I need to do if have a countif function setup that looks for a specific text string ("Yes") in one column and only counts based on that - Done

    It also needs to reset to an output of "1" for any variable if the value of column A (date in this case) changes. - Hopelessly Stuck


    Here is what I have, and is working perfectly except for resetting on a new day:


    Code
    1. =IF($K2="Yes",COUNTIF($M$2:$M2,$M2),"Same Run")

    Attached is a sample of the data I am looking at. The lines in yellow need column O to reset to 1 since the date in column A has changed.

    I am stuck trying to automate a code for work. Due to the sensitivity of the sql connection, I cant post a bunch of the code, and some of it will be xxxxx'd out for security reasons. That being said I have some code written by someone who actually knows what they are doing, and I am just tweaking it and adding rudimentary actions like clearing/copying cells before and after the meat of the program is ran. One section is a WHERE statement of an external connection pulling from a corporate database. One of the AND statements in it targets date data. I was able to figure out how to make that an AND/OR statement so I could make it target three date ranges. Previously (and me currently) the way the query was made was to manually edit the macro for the date, then run it. I want to automate that portion.


    So where I am stuck is assigning a Dim (I was thinking something simple like date1, date2, ... ) from another worksheet. In this case the first one would be Dates!B4 . In that page I put in a date for the Saturday of a week I want to project data for, and six other dates are calculated which encompass three ranges. One from earlier this year, and two from last year. I do not know the proper syntax that would be compatible with the SQL portion - it apparently wants the date in a dd-mmm-yyyy format, which is what my output cells in the worksheet spit out.


    Now for the sql part. Here is a small portion of the lookup code that I will need to figure out how to reference the date Dim's. The dates in there are arbitrary for now.


    Code
    1. 'This is the end of a WHERE statement
    2. "AND (APPLICATION_DATA.xxxx_DATE BETWEEN '2-jan-2020' AND '2-jan-2020' OR APPLICATION_DATA.xxxx_DATE BETWEEN '13-jan-2020' AND '13-jan-2020' OR APPLICATION_DATA.xxxx_DATE BETWEEN '4-jan-2020' AND '4-jan-2020')", conn


    Any help is appreciated, I am very green with VBA and have never touched anything pulling external data before.


    Thank you in advance

    I hate to ask someone to DO something for me, but I need this one done ASAP and its fairly complicated. Any and all help is appreciated. Ok so here is where I am at:


    I have a workbook with 4 sheets. Columns A - E occupied, Row 1 Is a header, 2-50 are viable data.


    What I need is a macro that will look up all four sheets. For any row that column E has a value >0, pull that corresponding row (only columns A-E) to a single summary page... alphabetically, and automatically as data in the four worksheets changes.


    All it is, is an ordering workbook. I have a Janitorial tab, a forms tab, a printer toner tab, ect. Once each remote office marks in column E the quantity they are requesting, I want a simple summary page for the purchaser that populates only the items that have been requested.


    Again, any help is appreciated. I am trying to learn, but I need this one done asap and it is a bit beyond the beginners guide :smile: