Previously, I added a button to update the dynamic table to trigger the update event, which is concise but not elegant. The boss told me to solve the problem. Thanks to Brother Cui for telling the active event. Later, I found a change event! This seems to be more useful. Cui elder brother discussed how to use the three variables of the company code as the condition for updating the query during the accounting year.
I found two more problems in writing code. The first problem is that there are seven queries in the query. How do you know the last update of the query. This problem is still well solved. I wrote a piece of code to test it, as shown below:
If target. Address = "$ CG $1" or target. Address = "$ Ch $9" then
Msgbox "query 7"
Elseif target. Address = "$ A $1" or target. Address = "$ B $9" then
Msgbox "query 1"
Elseif target. Address = "$ q $1" or target. Address = "$ r$ 9" then
Msgbox "query 2"
Elseif target. Address = "$ AK $1" or target. Address = "$ AK $9" then
Msgbox "query 3"
Elseif target. Address = "$ ax $1" or target. Address = "$ ay $9" then
Msgbox "query 4"
Elseif target. Address = "$ BJ $1" or target. Address = "$ BK $9" then
Msgbox "query 5"
Elseif target. Address = "$ BW $1" or target. Address = "$ BX $9" then
Msgbox "query 6"
End if
Previously, I thought that the last updated query would be the last inserted query, But I found through the code that it was the last update of the first inserted query! The test results are the same each time. Therefore, the cell of the first query is used as the cell to check for updates.
The second problem is not so obvious. The Code will report an error here every time:
'Select the range c_delteplace to be deleted and use the serial number to determine
While worksheets (p_contentsheet). cells (p_conrows, 2). Value "" 'determines whether to delete a blank row.
'Selected and deleted
Worksheets (p_contentsheet). cells (p_conrows, 2). Select
Selection. entirerow. Delete
Wend
I thought I could not encapsulate the code in sub, But I copied the code to the sub of change. After that, I removed selcet and executed it perfectly!
While worksheets (p_contentsheet). cells (p_conrows, 2). Value "" 'determines whether to delete a blank row.
'Selected and deleted
Worksheets (p_contentsheet). cells (p_conrows, 2). entirerow. Delete
Wend
No selcet is added. No wonder the select method of range is invalid. This is the case for an unfamiliar language. If such an error occurs, you must carefully repeat the prompt to solve the problem.
PS: VBA is so powerful that you must read the Excel 2007 programmer reference.