Hi Roy,
It says Wrong number of arguments or invalid property assignment.
Its has be without tables, that's why not using those.
Thanks
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
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
Display More
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:
Date | Day | Area | Product | SalesmanName |
01/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
01/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A1 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
02/07/2021 | Thu | A2 | Prod-1XN | Salesman1 |
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
Display More
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
Display More
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.
Products | Day | Product Set |
Product 1 | Sat | Set 1 |
Product 2 | Sat | Set 2 |
Product 3 | Sun | Set 1 |
Product 4 | Sun | Set 2 |
Product 5 | Fri | Set 1 |
Here is the final table:
Date | Day | Product Set | Product |
11/09/2020 | Fri | Set 1 | Product 5 |
12/09/2020 | Sat | Set 1 | Product 1 |
12/09/2020 | Sat | Set 2 | Product 2 |
13/09/2020 | Sun | Set 1 | Product 3 |
13/09/2020 | Sun | Set 2 | Product 4 |
18/09/2020 | Fri | Set 1 | Product 5 |
19/09/2020 | Sat | Set 1 | Product 1 |
19/09/2020 | Sat | Set 2 | Product 2 |
20/09/2020 | Sun | Set 1 | Product 3 |
20/09/2020 | Sun | Set 2 | Product 4 |
25/09/2020 | Fri | Set 1 | Product 5 |
26/09/2020 | Sat | Set 1 | Product 1 |
26/09/2020 | Sat | Set 2 | Product 2 |
27/09/2020 | Sun | Set 1 | Product 3 |
27/09/2020 | Sun | Set 2 | Product 4 |
02/10/2020 | Fri | Set 1 | Product 5 |
03/10/2020 | Sat | Set 1 | Product 1 |
03/10/2020 | Sat | Set 2 | Product 2 |
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:
Product 1 | Product 2 | Product 3 | Product 4 | |
Mon | ||||
Tue | ||||
Wed | ||||
Thu | ||||
Fri | Set 1 | |||
Sat | Set 1 | Set 2 | ||
Sun | Set 1 | Set2 |
Thanks