Posts by Luke M

  • Autofilter range (table) field with multiple criteria 'contains'

    Quote from Conexus_One

    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.

  • VBA code to make a cell mandatory on save if previous cell says Fail

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


  • One mail per recipient

    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.

  • GetPivotData REF! Error when field exists.

    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")

  • Incorporate Application.InputBox

    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

  • Table or Dynamic Range?

    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.

  • Table or Dynamic Range?

    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).

  • Finding the exact cell address in a Table

    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)))

  • Help removing defined names

    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

  • Issue with adding formula to a excel macro

    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

  • VBA created hyperlink giving "Reference isn't valid."

    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

  • Search text in multiple sheets and display adjacent cell along with sheet info

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


  • Copy/Paste a row with a variable range

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

  • Attendance Sheet

    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.