Posts by Luke M

    Hi Luke,

    if my column which needs to be searched is not column A (it is Column AI) so what I have to amend in your code to get it work?


    Thanks so much for your reply!

    Don't need to amend the code, just change some things in the workbook. Unhide Sheet2, and change the value of cell A1 to be whatever header you want to do the filter on. The other part you may need to change is in the Formulas - Name Manager, make sure the definition of rngTable is the correct range of your table.

    You could do a COUNTIFS and see if there's at least one data point.


    Should be doable, but some more info would be helpful for building the exact code. Are you wanting to send a basic message to each user, an image from body of workbook, or an attachment? Is it just the two columns of data we need to work with?


    One way I'd do it is to copy the list of emails to a new blank column, Remove Duplicates. Then cycle through that unique list and use it to filter for emails back in your original data. Depending on what we do next, we either create an attachment to send, or copy and image.

    The labels in a PivotTable, including dates, are all displayed as Text. So, when trying to match up against the numerical date in A1, it's failing. You might have success by changing the "$A$1" argument to this
    TEXT($A$1, "dd/mm/yyyy h:mm")

    Here's an example to find last row with data in a column.

    Code
    Sub FindData()
        Dim lastRow As Long
        
        lastRow = ActiveCell.EntireColumn.Find(what:="*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
        MsgBox "Last row with data: " & lastRow
    End Sub

    Between dynamic and table, I'd say the table will generally always win (assuming you don't need XL2003 and older compliancy). Beyond that, your next consideration is whether the data should be in a database like Access vs. Excel. If you start handling over 100k of rows, XL will start to struggle to keep up.

    I'd suggest using the table. It's more "native" should will be faster than first having to do a formula calculation (even a simpel one) to define the table range. Also, using the strcutural references it'll be easier later to determine what all the formulas are doing (assuming good field names).

    Digger deeper, why do you want the cell address? Reason I ask, many times people do this and then feed the address into INDIRECT, when it's generally easier to get the data directly.


    But, assuming value only appears once
    =ADDRESS(SUMPRODUCT((TableRange="SearchValue")*ROW(TableRange)), SUMPRODUCT((TableRange="SearchValue")*COLUMN(TableRange)))

    Wow, that one was buried deep. Turns out the names were hidden from the file manager. Not sure how, but they're there. If you run this short script, you'll then be able to see the names and delete them.

    Code
    Sub RevealSecrets()
        Dim nm As Name
    
    
        For Each nm In ActiveWorkbook.Names
            nm.Visible = True
        Next nm
        
    End Sub

    You need a way to tell the VBA that you're not ending the string, so we use double quotation marks to indicate that "yes, I want a quotation mark here". I also went through and optimized things a little bit

    My guess is that you have a sheet name with a space somewhere in the name. In which case, you need to make sure you're including single quotation marks around the sheet name. Try something like this

    Try this. It'll automaitcally clear out old query before running, and it returns all matches and relvant info.


    You want to use the EntireRow to get the range selected, not the Row property.

    I'd suggest just putting a mark of some kind in a helper column (maybe an "X"?), and then use Data - Filter, and you can filter your sheet to only show those present, and print that out.