Posts by Gizzmo

    I am trying to automate the 'Search' box on the following website "". I have tried to modify original source code from "xl-searchbot.xlsm" from", but having no luck in actually getting the 'Search' box to be populated. My understanding of the html and DOM structure is limited and I have being trying for the last 2 days with no luck.

    This is the HTML Extract for what I believe is the particular area of the DOM that I need to interact with.

    I would aApprecaite any help or advise.

    Hi JimmyB,

    I'm guess that it has something to do with the "Dim" statements and the associated "Set" statements.

    Try amending

    Dim RngA as Double
    Dim FirstCell as Range 'or as Double
    Dim LastCell as Range 'or as Double

    Note the above Dim as statements will depend on what type of object you are trying to dimension (i.e. define) and subsequently set in the Set Statements

    Set FirstCell = RngA.Offset(-600, -3) what is that you want here (i.e. Cell Address, Row Number etc.).

    I am not a VB expert so hopefully this makes sense and helps you.

    If you attach a copy of the workbook, it would also help.

    As an alternative or look at the attached file which I based on code from [URL="http://Range(FirstCell,%20LastCell).Delete%20Shift:=xlUp"]stackoverflow [/URL]and my interpretation of the comments in your code.



    Hi Trebor76,


    What was wrong with my solution here?

    If you must cross-post please provide a link to each other site as I have done with the above link.

    My apologies, I was not aware the OP was a cross-post and that you had already provided a solution.



    Hi crysp711,

    Try this code, modify as required the SourceFolderName ?????? and Range (" ") = Array portion.

    Note credit for the majority of this code comes from Santosh I have just modified where required to meet modified today and file is pdf.


    Ok if that's the case, and this is messy, only because I am no expert, but try this code instead, hopefully it will cater for row and column sizes being different between workbooks


    Hi Claire,

    Try this


    Hi Peter,

    Yes helper column/cell is needed with the formula provided by NBVC

    As an Example in cell C2 just put "=YEAR(DATE("20"&LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2)))"

    Change the format to General for cell C2. Result should be "2019". Your original post referring to "=year (B2)" has been interpreted by me as return the year only which is 2019, in a separate cell.

    From your response to NBVC, it appears you are wanting to come up with custom format on cell B2 in the first instance to display "2019-06-05"

    If this is the case then go to Format Cells, Custom and enter 20##-##-##. But please realise that excel will not recognise this as a date!

    Hope this helps.



    Hi Peter,

    Which part of your value in B2 represents Year, Month and Day?

    Is the year 2019, the month 06 and the day 05; or
    is the year 2005, the month 06 and the day 19



    Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc

    Amended file with 2 possible solutions (thanks to pivot as per royUK, amended formula as per MrRedli) and combination of dynamic names, helper column for year and data validation linked to unique list based on pivots.

    Hopefully Lamzie finds replies helpful.

    Re: New to Macros, Need help

    Hi royUK,

    Agreed, but from the orginal post


    .... It is difficult for me to send the document to people and explain to them how to filter out only their platoon, or filter just vehicles.... I want to make a button at the top of the document, that when clicked, will show specific information from the spreadsheet. The same way as if I filtered out specific text under columbs.

    I get the impression that Max wants something other than this (i.e. Not have to explain to them how to use AutoFilter).


    Re: New to Macros, Need help

    Hi Max,

    And welcome to Ozgrid.

    Your tittle "New to Macros, Need Help" whilst I appreciate your intention its not the best place to put this comment, only because the tittle is used by Forum users to search on key words that they can then either help and or use. Perhaps a better tittle would be "Button to filter data".

    If you can attach a sample of your data (if its not sensitive, or even a dummy sample) I may be able to help or provide suggestions.



    Re: Project Plan with Gantt Chart and Resources Management

    Quote from Gorgey;727224

    Is there any other trick that could be consider to speed up the update?

    This is probably not the best solution but try amending Worksheet_Change to...

    This will only change/update the row in which the 'Responsible' resource has been changed.

    Howver if the Current Date is changed it will still Call the UpDateGantt macro so that the Current Date is highlighted correctly

    There is probably a more refined way to do this, perhaps try asking the rest of the Forum to either refine this code or come up with a better way. I have noticed that "jindon" has provided some better more refined solutions to some of my past posts perhaps see if he/she is willing to help.