我有一个用户窗体,可以在工作表上移动对象(员工姓名(,以便经理可以在必要时将员工移动到不同的部门(工作表上的不同行分组(。 我遇到的问题是,当我定义属于员工姓名的初始行,然后通过 Selection.Cut 和 Selection.Insert Shift:=xlDown 移动该行时,我无法再调用员工行的新位置,因为这已经更改,并且后面的任何代码行仍然可以识别在用户表单代码中标识的初始员工行。
Dim UserRow As Long
Dim UserMatrix As String
Dim CCIRow As Long
Dim CCIRange As String
Dim CEMRow As Long
Dim CEMRange As String
Dim MANRow As Long
Dim MANRange As String
UserRow = Application.WorksheetFunction.Match(UserName, Range("A1:A300"), 0)
UserMatrix = "B" & UserRow
CCIRow = Application.WorksheetFunction.Match("CCI / MDI", Range("A1:A300"), 0) + 1
Let CCIRange = "A" & CCIRow
CEMRow = Application.WorksheetFunction.Match("CE / Marketing", Range("A1:A300"), 0) + 1
Let CEMRange = "A" & CEMRow
MANRow = Application.WorksheetFunction.Match("Management", Range("A1:A300"), 0) + 1
Let MANRange = "A" & MANRow
OTHRow = Application.WorksheetFunction.Match("Other", Range("A1:A300"), 0) + 1
Sheet3.Rows(UserRow).Select
Selection.Cut
If UserDept = "CCI / MDI" Then
Sheet3.Rows(CCIRow).Select
Selection.Insert Shift:=xlDown
Sheet3.Range(UserMatrix).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Sheet3.Range("MATRIX_ROW").Select
Selection.Copy
Sheet3.Range(UserMatrix).Select
Selection.PasteSpecial PASTE:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Range(CCIRange).Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Sort _
key1:=Range(CCIRange), order1:=xlAscending
End If
If UserDept = "CE / Marketing" Then
Sheet3.Rows(CEMRow).Select
Selection.Insert Shift:=xlDown
Sheet3.Range(UserMatrix).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Sheet3.Range("MATRIX_ROW").Select
Selection.Copy
Sheet3.Range(UserMatrix).Select
Selection.PasteSpecial PASTE:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Range(CEMRange).Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Sort _
key1:=Range(CEMRange), order1:=xlAscending
End If
If UserDept = "Management" Then
Sheet3.Rows(MANRow).Select
Selection.Insert Shift:=xlDown
Sheet3.Range(UserMatrix).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Sheet3.Range("MATRIX_ROW").Select
Selection.Copy
Sheet3.Range(UserMatrix).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial PASTE:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Range(MANRange).Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Sort _
key1:=Range(MANRange), order1:=xlAscending
End If
我的问题:有什么方法可以在子例程中定义维度变量(即 Dim(的值发生变化时更新它? 感谢您的任何帮助!
如果你问你能改变UserRow
的值,那么答案是"是"。您所要做的就是为它分配您粘贴它的任何位置的行号值。
我很难理解这么多Select
、ActiveCell
和Selection
位的代码,但它可能是像UserRow = CCIRow
(或者,您可以在移动后再次使用 UserRow = Application.WorksheetFunction.Match(UserName, Range("A1:A300"), 0)
语句重置它。