Posts by Luke M

    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
    1. Sub FindData()
    2. Dim lastRow As Long
    3. lastRow = ActiveCell.EntireColumn.Find(what:="*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
    4. MsgBox "Last row with data: " & lastRow
    5. 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
    1. Sub RevealSecrets()
    2. Dim nm As Name
    3. For Each nm In ActiveWorkbook.Names
    4. nm.Visible = True
    5. Next nm
    6. 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.

    Yep, just need to know where you're going. Command line to take you anywhere is something like

    Code
    1. Application.Goto Workbooks("Some book.xlsx").Worksheets("Some sheet").range("B4")
    2. Call MyMacro