Sub?Vlookup_Click()' This is the process name. It can be customized. As long as you like it, you can do anything. Sub is a process symbol, a keyword. Vlookup means searching by column. Click is a mouse click.
Dim?a,?d?As?Object,?i&'Define variables, a and d are object types, i is an integer
With?Sheets("nbr")' Operate on the object named nbr
a?=?.Range(.[a2],?.[b65536].End(3))' Assign a to a cell object, and the range is from The last valid cell in columns A2 to B. .[b65536] is the limit cell of column B, that is, the last cell of column B. End(3) means that you select the last cell in column B and then press the ctrl+Home keys at the same time. The 3 in brackets means Up.
End?With
Set?d?=?CreateObject("Scripting.Dictionary")'Creates a key-value associated library object, which is a special array with the subscript a (i,?1)), its value can be a(i,?2)
For?i?=?1?To?UBound(a)' area a is also an array, Perform array-type operations on it
d(a(i,?1))?=?a(i,?2)' This kind of assignment is convenient for statistics. The array d? uses the value of column a as the subscript , taking the value of the corresponding row in column b as the value.
Next
With?Sheets("total")' operate the table named total for statistics
a?=?.Range(.[a2] ,?"b"?&?.[a65536].End(3).Row)'The value of a is the area A2 to Bn
For?i?=?1?To?UBound(a)
a(i,?2)?=?d(a(i,?1))' assigns a value to the second column in the area. The principle is that if the value of column a in the table total is the same as the value of the table If the value of column a in nbr is the same, then the value of d(a(i,?1), which is the value of column b in table nbr, is given to column b in table total. To put it bluntly, it is the first column query. Return to the second column value.
Next
.[a2].Resize(i?-?1,?2)?=?a' displays the value of a in the [a2] position. The moved cells are filled with the area a?=?.Range(.[a2],?"b"?&?.[a65536].End(3).Row)
End ?With
End?Sub?
This example only searches for references and can be used to count the number of projects.
Zhang Zhichen