Work Note 4

After processing data of a table in Excel, output the data to another Excel location. The temporary code is as follows:

Sub button upload click () dim dict, temp, name, K, V, N, KS, vs set dict = Createobject ("scripting. dictionary ") dim R as range dim R2 as range dim R3 as range dim row as listrow dim rows as listrows set rows = sheet3.listobjects (" Table 6 "). listrows dim I as integer for I = 1 to rows. count set ROW = rows. item (I) set R = row. range. cells (1, 3) set r2 = row. range. cells (1, 4) set R3 = row. range. cells (1, 2) If R. text <> "" Then temp = Replace (R. text, "-", "+") name = Replace (r2.text, "-", "+") k = Split (temp, "+") V = Split (name, "+") for n = lbound (k) To ubound (k) if not dict. exists (mid (TRIM (K (n), 2, Len (TRIM (K (N)-2) Then dict. add mid (TRIM (K (n), 2, Len (TRIM (K (N)-2), trim (V (N) + "|" + trim (r3.text) else dict. item (mid (TRIM (K (n), 2, Len (TRIM (K (N)-2) = trim (V (N) + "|" + trim (r3.text) end if next I 'msgbox dict. count sheet3.range ("h1", "J100 "). clear Ks = dict. keys Vs = dict. items for I = 0 to dict. count-1 Key = KS (I) value = vs (I) sheet3.range ("H" & (I + 16 )). select activecell. formular1c1 = Key sheet3.range ("I" & (I + 16 )). select activecell. formular1c1 = Split (value, "|") (1) sheet3.range ("J" & (I + 16 )). select activecell. formular1c1 = Split (value, "|") (0) 'msgbox key & Value next set R = nothing set r2 = nothing set ROW = nothing set rows = nothing set dict = nothing end sub

Summary of usage of dictionary objects in VBA

Dim dict

'Create a dictionary
Set dict = Createobject ("scripting. Dictionary ")

'Add a project
Dict. Add "A", 300
Dict. Add "B", 400
Dict. Add "C", 500

'Count the number of projects
N = dict. Count

'Delete a project
Dict. Remove ("")

'Determine whether the dictionary contains keywords
Dict. exists ("B ")

'Get the value corresponding to the keyword. Make sure that the key exists before use. otherwise, an additional record will appear in dict.
Value = dict. Item ("B ")

'Modify the value corresponding to the keyword. If it does not exist, create a new project.
Dict. Item ("B") = 1000.
Dict. Item ("D") = 800.

'Looping the dictionary
K = dict. Keys
V = dict. Items
For I = 0 to dict. Count-1
Key = K (I)
Value = V (I)
Msgbox key & Value
Next

'Delete all projects

Dict. Removeall

Instance:

Sub macro 1 ()

Set dic = CreateObject ("Scripting. Dictionary") 'Dictionary
For 1 To 10000
If Not I Like "* 4 *" Then
Dic. Add I, "" if "1" is not included"
End If
Next
Range ("a2"). Resize (dic. Count, 1) =
Application. WorksheetFunction. Transpose (dic. keys)
'Place down from Unit A2
End Sub

 

 

 

========================================================== ======================================

Example of Tranpose worksheet function usage

 

'Convert a two-dimensional array with multiple columns in one row into a one-dimensional array.

Sub test ()
Dim arr, arrt
Arr = Range ("a1: j1 ")
Arrt =
WorksheetFunction. Transpose (WorksheetFunction. Transpose (arr ))
Stop
End sub

First, let's take a look at the basic usage of the transpose function. Official help shows that the transpose function can return to the transpose cell area, that is, the row cell area is transposed into the Column cell area, and vice versa.

The transpose function syntax is: transpose (array)

The array parameter is the array to be transposed or the cell area on the worksheet. The so-called array transpose is to take the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.