Posts by joePAIN

    Afternoon all,

    I have a query around SUMIFS and whether or not I can use it with a criteria that's delimitated with a ";".

    I have a monthly hourly forecast by project, and I'd like to allocate one or multiple resource names to each Project.

    I think MS Project does this with ease, but I have a large amount projects with other data and creating two, three or more lines per resource might be too onerous.

    Is there a way to sum the allocation of hours by resource by dividing the hours by the amount of names in the resource column?

    So where there are 80 hours in P3 for Project 1, Bob and Harry has a sum of 40 hours?




    I've used this code snippet to basically filter a listbox that I've populated from a table. When you start typing in a word it will work through the listbox checking in the second column for a match and removing each entry where it doesn't match.

    This continues as you carry on typing the word in, leaving you with a narrowed down listbox to select from.

    What I'm trying to figure out is how to reverse the process if the user deletes a letter that they've entered, giving a realtime update in the listbox.

    At the moment it doesn't do anything, I've assigned a KeyUp procedure which captures vbkeyback but I'm struggling to reinstate the removed entries.

    Any help would be appreciated.



    Re: VBA Autofilter using cell contents seperated by commas

    Thanks for the quick reply.

    My simple code is:

    MyFilter = ActiveSheet.Cells(ThisRow, colCode).Value
    Sheets("Filter Data").Select
    ActiveSheet.AutoFilterMode = False
    Range(Cells(1, 1), Cells(Cells(1, 1).End(xlDown).Row, Cells(1, 1).End(xlToRight).Column)).AutoFilter Field:=11, Criteria1:=MyFilter

    I've made an array when a comma is present in the cell:

    Dim MyArray As Variant
    MyArray = Split(MyFilter, ",")

    I can see it successfully creates MyArray(0), MyArray(1), MyArray(2) etc. but I'm not sure how to pass these into the filter as the Criteria.



    I have a number cells in column "A" that containt various 6 digit numbers seperated by commas. Sometimes there will be two e.g. 100123, 100124 sometimes six etc.

    I can auto filter another range of data based on a cells contents using AutoFilter Field=3, Criteria1:=A1 for example, but this obviously only works if there is one single 6 digit number in the relevant cell.

    Can I create an array from the comma seperated list in a cell, and then use this list of numbers to autofilter a range?

    Any help would be appreciated.