Posts by joePAIN

Thanks for the reply - that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.

Cheers

Joe

Thanks for the reply, is there a VBA solution or am I barking up the wrong tree?

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?

Thanks

Joe

Hi,

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.

Sub Filter_Change()
Dim i As Long
Dim Str As String
Str = Me.TextBox1.Text
If Not Str = "" Then
    With Me.ListBox1
        For i = .ListCount - 1 To 0 Step -1
            If InStr(1, LCase(.List(i, 1)), LCase(Str)) = 0 Then
                .RemoveItem i
            End If
        Next i
    End With
 
End If
End Sub

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.

Cheers

JP

Re: VBA Autofilter using cell contents seperated by commas

OK - I've solved it, I can use MyArray in the filter criteria but I had to add in Operator:=xlFilterValues to make it work.

Thanks anyhow

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.

Cheers

Hi,

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.

Cheers