Posts by Shawn Michaels

Hi Roy,

It says Wrong number of arguments or invalid property assignment.

Its has be without tables, that's why not using those.

Thanks

Hi,

I intend to use advanced filter for more than 20 times across the whole project, so I am trying to create a reusable advanced filter sub procedure as given below but getting errors.

Sub RunAdvFilter()
Call AdvFilter(Sheet1, Sheet1.Range("A1"), Sheet1, Range("H1"), Sheet2.Range("A1"))
End Sub

Sub AdvFilter(DataSheet As Worksheet, StartCell As Range, CriteriaRange As Range, DestinationRange As Range)

DataSheet.StartCell.CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=CriteriaSheet.CriteriaRange.CurrentRegion, _
CopyToRange:=DestinationRange _
, Unique:=False

End Sub

I would highly appreciate any help.

Regards

Shawn

Dear Bosco, Roy,

It works like a charm

I was struggling with this,

Many many thanks

Thanks for your reply Alan.

I am looking for a formula.

Many thanks

Hello,

My data is setup in this way:

The Cell F2 has Salesman name and in G2, I need a formula, which will count the salesman visits in areas on all dates.

So in this case, the formula should return 4 (on 1st July, he visited 2 areas, on 2nd July, he visited 2 areas).

The pivot tables are counting salesman's name and giving me count of 16, which is not the right answer.

Highly appreciate your help.

Many thanks

Dear Bosco,

Thank you very much for your reply.

It shows Match and No Match but for some strings, it still shows Match when there is none. For example,

A2: Feelings: A Baby and Blue Book-------------------------------------------------------B2: Payback (Fearless, No. 6)

A3: Goldberg, Lee: The Death Merchant (Diagnosis Murder # 2)---------------B3: Sun Hawk

A4: Treasure Island and Other Cartoon--------------------------------------------------B4: I Who Have Never Known Men

A5: Harry Potter and the Sorcerers Stone-----------------------------------------------B5: The Changing of the Guard (Star Wars: Jedi Quest)

Thank you very much for your help.

Regards

Hello ,

I am trying to do partial match based on words in both columns.

I have two data in Col A and Col B and the formula goes in Cell C2 and down till last row.

So for example, I have these in A2 and B2.

A2: The White Lion King ---------B2: The Lion King -----------------------------C2: Formula should return Match because 3 words from B2 match A2

A3: Brown Horse -------------------B3: Lazy Fox ------------------------------------C3: Formula should return No Match because words don't match

A4: The Great Scientist -----------B4: The Great Newton Scientist---------C4: Formula should return Match because 3 words match

I am trying to do it with Search formula. Tried find as well but to no avail. What formula can we use here for partial match.

Many thanks

Aah, its working absolutely perfect Thank you for your efforts and time Roy.

Many many thanks

Yes, the combobox seems more efficient.

Thanks

Hello,

Thank you for your reply. I want to use MultiSelect.

Office, I am using Excel 2010.

Many Thanks

Hello,

Not all staff, but more than 5 members of staff, they have the same hours. So the idea is to select, lets say, 5 staff, enter hours in textbox and click the button to enter the hours in the relevant column.

Hi Roy!

Thank you for the code. It works but this is for SingleSelect ListBox. How can we change it to work for multiselect listbox?

Thank you

Hi Roy

I have attached the example workbook

Thanks

Hello,

I have a userform with following controls:

TextBox1 for Date (which is to be found using find)

TextBox2 for Text to be entered in worksheet

Listbox2 which contains the items (which are to be found using find)

So when I click the commandbutton, the code should look the items in the listbox2 and the relevant date column and enter the textbox2 value in relevant cell.

I am using the following code, which is not working:

Private Sub CommandButton1_Click()


Dim strfind As String
Dim x As Variant
Dim RowNum As Long
Dim ColNum As Long
Dim strDate As Date


If IsDate(Me.TextBox1.Text) Then
strDate = Me.TextBox1.Text


'loop through all items in ListBox2
For x = 0 To Me.ListBox2.ListCount - 1
current string to search for
strfind = Me.ListBox2.List(x)




With ws
.Cells.Find(strfind, After:=.Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
On Error GoTo 0


ColNum = ActiveCell.Column
RowNum = Application.WorksheetFunction.Match(strfind, ws.Range("B:B"), 0)


.Cells(RowNum, ColNum).Value = Format(Me.TextBox2, "#0.00")
End With
End
Next x
End If


End Sub

I would highly appreciate help.

Many thanks

Hello,

I am trying to create a find and findnext with do until loop but getting error.

My 2 Criteria are in Sheet 1("CreateList") : ProductNum in cell B4 and Product Name in cell B5.

On Sheet 2 ("List"), I have IDs and multiple product names (in a table) associated with that ID. So When I find a particular ID, I check the name in next column matches Criteria in Sheet 1. If it doesn't, the loop should continue until Find matches both criteria from Sheet 1 but I am not able to code that.

Here is my code:

Sub ProductIDsandNames()

    Dim wsCreateList As Worksheet, wsList As Worksheet
    Dim LastRow As Long, rg as Range
    Dim ProductNum As Long, ProductName As String
    Dim Found As Range
       
    Set wsCreateList = ThisWorkbook.Worksheets("CreateList")
    Set wsList = ThisWorkbook.Worksheets("List")
    LastRow = wsList.Range("B" & rows.Count).End(xlUp).Row
       
    ProductNum = wsCreateList.Range("B4").Value
    ProductName = wsCreateList.Range("B5").Value

    With wsList
        
        Set Found = .Cells.Find(what:=ProductNum, After:=.Range("B23"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        
        Do Until Found = .Find(ProductNum) And Found.Offset(0, 1).Value = ProductName
                    If Found.Offset(0, 1).Value = ProductName Then
            
                   Set rg = Found.Offset(0, 7).Range("A1:R1")
                    Exit Do
                    End If
        Loop
    
    End With
    
    With wsCreateList
        .Range("A9").CurrentRegion.Offset(1, 0).Clear
        .Range("A500:A517").Value = WorksheetFunction.Transpose(rg)
    End With

Thank you for your help.

Regards

Hi Dangelor,

Works like a charm...:) Thank you so very much, its accurate now

Here is the Data Table, and the current region can take care of its growth.

Here is the final table:

There are 2 more Sunday (04/10/2020) rows and 1 Friday (09/10/2020) row, which the final table doesn't show.

Thanks for the code Dangelor and your efforts. Your are truly a gem of a professional coder.

I have 2 relevant questions.

1. If my data table (A8:C11) grows, then I only have to change C11 to C12 or C13 because I think the lines like "The For i = 4 To d2 - d1 + 4", the number 4 means the data table's 4 rows ? Is this possible in the code?

2. In your recent code, some last dates are not showing, for example, the result table stops on 04/10/2020 Sunday if I choose 2 Sat, 1 Sun, 1 Fri. The next Fri which is on 9th Oct should also show in the final table.

Thank you for your cooperation and support.

Lol, nice one, thanks but now the product sets and products are getting wrong.

Hello

I have tried that too but still the problem arises when same Day has to sets. It is only giving row for 1 set not for the Set 2 on the same day.

I guess, I will have to change my data table structure to find solution. If you think there is a better way to set the data table (A8:C11), I will adopt that .

I am also thinking to make one like this:

Thanks