Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

Continued from last post ( https://excelfox.com/forum/showthrea...ll=1#post19863 )

Explanation of the two Match bits

Rem 1
Code:
Rem 1 try to match the name & Activity & Sub-activity
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
Dim strSrch As String ': Let strSrch = Range("E2").Value & Range("F2").Value & Range("G2").Value ' This gives for example, "JohnA.1A.1.1"
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
Let strSrch = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim MtchRw As Long
Let MtchRw = Application.Match(strSrch, arrD1(), 0) ' this tries to match the correct row in Database1
This is fairly straight forward.
This is the array of name & Activity & Sub-activity

arrD1() =
NameActivitySub-activity
JohnA.1A.1.1
JohnA.1A.1.2
JohnA.2A.2.1
JohnA.2A.2.2
JohnA.3A.3.1
JohnA.3A.3.2
JohnA.4A.4.1
JohnA.4A.4.2
MarkA.1A.1.1
MarkA.1A.1.2
MarkA.2A.2.1
MarkA.2A.2.2
MarkA.3A.3.1
MarkA.3A.3.2
MarkA.4A.4.1
MarkA.4A.4.2
AnneA.1A.1.1
AnneA.1A.1.2
AnneA.2A.2.1
AnneA.2A.2.2
AnneA.3A.3.1
AnneA.3A.3.2
AnneA.4A.4.1
AnneA.4A.4.2


Now take for example the case of row 2 from worksheet database. I am looking to match this
JohnA.1A.1.1

So that is obviously 2 – ( Match tells me the position along in the array that it finds what it is looking for, the second position in this case is where it finds JohnA.1A.1.1 )



Rem 2 This is more tricky, and may not always work with different data. This initial solution may need to be re thought

This is the array of dates
arrDts() =
Name Activity Sub-activity 44866 44896 44927 44958 44986 45017 45047 45078

Now take for example from row 2, the date 30 November, 2022. Excel holds dates internally as numbers, and for the date 30 November, 2022 it has 44895.
I don’t have the number 44895 in my array. So this, Application.Match(DteV2, arrDts(), 0) , would give an error. It errors because it cannot find an exact match.

However we can use a 1 or -1 instead of the 0 in that final third argument in the Match - The syntax supports 3 different options, -1 0 1

If we choose 1 or -1 , then it will not error if it finds something close to what its looking for. The exact rules governing how that works are not so clear. I have a feeling Microsoft have some inconstant documentation in this.
For the case of using a 1 , we must have the numbers in the array in ascending order, which we have in the test data. I think then it tries to find the nearest smallest number.

Simple example to clarify:
In other words, if as a simple example, we had a 4 and a 9, in an array like this
{ 2 3 4 9 }
but we were lookig for a 8, then it would think that the matched value was 4, if we used a third argument of 1 in Match . So it would return us a 3 in that case as 4 is in position 3 along in the array.
In other words the 4 is the third number and its the nearest number to 8 that is smaller than 8. The number 9 is nearer, but that is a bigger number. (If we wanted that number 9 to get matched to 8, then I think the array must be in decending order, and we must use a -1 as the third argument in the Match


So...
Lets look again in detail at the test data and add some color highlighting to demo the results i get, which luckily are correct with the test data

The array: ( from Database1)
1
2
3
4
5
6
7
8
9
10
11
Name Activity Sub-activity
44866
44896
44927
44958
44986
45017
45047
45078


The 4 numbers I use from column D (from Database ) to get the results I want
44895
44895
44926
44985



If you follow the logic you can see that we will get the correct column numbers we want with the test data: We will get
4 4 5 7

But there are at least 3 issues to consider:

_1) This is relying on the date in column D in database being typically bigger than the corresponding date to be matched in Database1

_2) I am not sure how the Match always reacts to having a mixture of numbers and text in the array. It does not seem to cause a problem with the test data.

_3) The dates in Database1 must be typically in acending order. (I expect they probably willl be?)











( I added some extra stuff in the Database worksheet on the uploaded file to help show things a bit better )