Has two forms, one for the "product information", "new information", in which two tables A column is A commodity code, C column is the goods store, if at the same time satisfy the two conditions are the same, is that this information is the same, now need the following macro calculation:
Button 1 (computing) : calculate the A2: this form D1000, scope and table "product information" in the "hs code" and "storing code" two conditions at the same time consistent in E: behind the E column input "clear" two characters, otherwise enter today's date, at the same time in the table "product information" if "hs code" and "storing code" and table "hs code" in the "new information" and "storing code" on the match at the same time, the input "reserved" two characters, otherwise enter "clear" completed for two tables to category column in ascending order, at the same time after the completion of the arrangement for "clear" line of your attributes columns to delete
Button 2 (clearance) - on the table "additional information" A2: E1000 are cleared, at the same time on the table in the "virtual" storing data "to date" column and "storing" column in ascending order

CodePudding user response:

Who can answer my questions

CodePudding user response:

. Completed in two tables to category column in ascending order, at the same time...
The said category refers to here, please?

CodePudding user response:

. On the table "additional information" A2: E1000 to clean...
Is that the entire emptied, or delete contains "remove" on the line?

CodePudding user response:

Write well, code is as follows:

 
Option Explicit 

Calculation (Sub) 
Dim dctProductInfos As Object 
The Set dctProductInfos=CreateObject (" scripting. The dictionary ") 

With Sheets (" product information ") 
Dim As Long I 
For I=2 To 1000 
If the Range (" A "& amp; I). The Value & lt;> "" Then 
Dim strKey As String 
StrKey=. Range (" A "& amp; I). The Value & amp; VbCrLf & amp; The Range (" C "& amp; I). The Value 

Dim objRange As Range 
The Set objRange=. Range (" A "& amp; I & amp; : "K" & amp; I) 

If dctProductInfos. The exists (strKey) Then 
DctProductInfos. Item (strKey). Add objRange 
The Else 
Dim colInfos As Collection 
The Set colInfos=New Collection 

ColInfos. Add objRange 
DctProductInfos. Add strKey colInfos 
End the If 
End the If 
Next 
End With 

Dim dctNewInfos As Object 
The Set dctNewInfos=CreateObject (" scripting. The dictionary ") 

With Sheets (the "additional information") 
For I=2 To 1000 
If the Range (" A "& amp; I). The Value & lt;> "" Then 
StrKey=. Range (" A "& amp; I). The Value & amp; VbCrLf & amp; The Range (" C "& amp; I). The Value 
The Set objRange=. Range (" A "& amp; I & amp; ":" E & amp; I) 

If dctNewInfos. The exists (strKey) Then 
DctNewInfos. Item (strKey). Add objRange 
The Else 
The Set colInfos=New Collection 
ColInfos. Add objRange 
DctNewInfos. Add strKey colInfos 
End the If 
End the If 
Next 
End With 

Dim varKey As the Variant 
For Each varKey dctNewInfos In 
The Set colInfos=dctNewInfos. Item (varKey) 
If dctProductInfos. The exists (varKey) Then 
For I=1 To colInfos. Count 
ColInfos. Item (I). The Item (5). The Value="remove https://bbs.csdn.net/topics/
"Next 
The Else 
For I=1 To colInfos. Count 
ColInfos. Item (I). The Item (5). The Value=https://bbs.csdn.net/topics/Format (Now, "yyyy/mm/dd") 
Next 
End the If 
Next 

For Each varKey dctProductInfos In 
The Set colInfos=dctProductInfos. Item (varKey) 
If dctNewInfos. The exists (varKey) Then 
For I=1 To colInfos. Count 
ColInfos. Item (I). The Item (11). The Value="https://bbs.csdn.net/topics/keep 
"Next 
The Else 
For I=1 To colInfos. Count 
ColInfos. Item (I). The Item (11). The Value="remove https://bbs.csdn.net/topics/
"Next 
End the If 
Next 

With Sheets (" product information ") 
For I=1000 To Step 2-1 
If the Range (" K "& amp; I). The Value="https://bbs.csdn.net/topics/to remove" Then 
The Range (I & amp; ":" & amp; I). The Delete 
End the If 
Next 
End With 
End Sub 

Sub remove () 
With Sheets (the "additional information") 
'delete line contains "clear" : 
Dim As Long I 
For I=1000 To Step 2-1 
If the Range (" E "& amp; I). The Value="https://bbs.csdn.net/topics/to remove" Then 
The Range (I & amp; ":" & amp; I). The Delete 
End the If 
Next 
'delete all data: 
'Range (" A2: E1000 "). The Clear 
'need to "virtual store a data table: 
'Range (" A1: E1000.) Sort Key1:=Range (" A2 "), Key2:=Range (" C2 "), the Header:=xlYes 
End With 

End Sub 

Run the example:


Download address:
Link: https://pan.baidu.com/s/1c6NSAGkQlRr-5j0ws6gkNg
The extracted code: 5 JMH

CodePudding user response:

Consider two tables to create a new field connect AC column, and then by judging whether the new field is equal to do the corresponding processing,