我有簡單的代碼，可以在“薪資國家/地區”單元格更改時清除“狀態”單元格。例如，如果用戶在 A6 中選擇“USA”，然后在 X6 中選擇“Arizona”，那么以后可能由于某種原因他們改變主意并希望為國家選擇“CAN”，狀態單元格將清除。
但是如果以后有人決定在X列之前插入一列，那顯然會把我的State列移過去。有沒有辦法讓 VBA 更智能（或讓我更智能），以便函式將系結到“狀態”列而不是特定的“X”列？
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub 'CountLarge handles larger ranges...
'check Target column and row...
If Target.Column = 1 And Target.Row >= 6 Then
With Target.EntireRow
'State column
.Columns("X").Value = ""
End With
End If
End Sub
uj5u.com熱心網友回復：
您可以使用命名范圍，也可以使用它.Find來確定 State 列當前的位置。這是一個使用示例.Find
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub 'CountLarge handles larger ranges...
'check Target column and row...
If Target.Column = 1 And Target.Row >= 6 Then
Dim StateCol As Long
StateCol = Me.Range("1:5").Find("State", LookIn:=xlValues, LookAt:=xlPart).Column
With Target.EntireRow
'State column
.Columns(StateCol).Value = ""
End With
End If
End Sub
如果您要使用命名范圍，則可以定義StateColusing StateCol = Me.Range("NamedRange").Column，它會快一點，因為它不需要在用戶每次更改值時搜索行。
邊注： 的搜索范圍.Find是第 1 行到第 5 行，但您可能希望根據資料移動的預期限制或擴展該范圍。
uj5u.com熱心網友回復：
我（總是）為列定義一個列舉，像這樣
Public enum col_TableXXX 'adjust to your needs
col_ID = 1
col_PayrollCountry
col_State
end enum
列舉是自動編號的 - 所以 col_PayrollCountry 等于 2，col_State 等于 3 等等。
如果有新列或順序更改，您只需移動列舉或添加新列舉。
（您可以通過將列標題轉置粘貼到 Excel 作業表上，然后通過公式創建代碼來避免代碼輸入）
然后，您可以像這樣使用列舉：
If target.column = col_PayrollCountry then
target.entireRow.columns(col_State) = vbnullstring
End If
這也比 columns("X")
此解決方案的罪魁禍首：您必須知道列已更改。它不是基于列名稱的自動化。
uj5u.com熱心網友回復：
您的問題還有另一種解決方案（我受到了與 ACCtionMan 關于 enum-stuff 的簡短討論的影響）：
如果您可以插入表格（插入 > 表格），那么您可以使用listobject. 在許多其他優點中，您可以通過名稱參考該列。
我假設該表名為“tblData”
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lo As ListObject
Set lo = Me.ListObjects("tblData")
If Not Intersect(Target, lo.ListColumns("Payroll Country").DataBodyRange) Is Nothing Then
'changed cell is in Payroll country column then
'take the intersection of the targets row and the State column to change the value
Intersect(Target.EntireRow, lo.ListColumns("State").DataBodyRange) = vbNullString
End If
End Sub
但我更喜歡以下解決方案 - 因為我喜歡在事件處理程式中包含業務邏輯。
如果你（甚至你在6個月）的外表上collegue到更改事件代碼，他/她會立即明白什么是發生在這里-不讀怎么它完成。
Option Explicit
Private m_loData As ListObject
Private Sub Worksheet_Change(ByVal target As Range)
'if target cells is not within loData we don't need to check the entry
If Intersect(target, loData.DataBodyRange) Is Nothing Then Exit Sub
If ColumnHeaderOfRange(target) = "Payroll Country" Then
resetStateColumnToEmptyValue target
End If
End Sub
Private Sub resetStateColumnToEmptyValue(c As Range)
Intersect(c.EntireRow, loData.ListColumns("State").DataBodyRange) = vbNullString
End Sub
'this could go in a general module - then add listobject as parameter
Private Function ColumnHeaderOfRange(c As Range) As String
On Error Resume Next ' in case c is outside of listobject
ColumnHeaderOfRange = Intersect(c.Cells(1, 1).EntireColumn, loData.HeaderRowRange)
On Error GoTo 0
End Function
'this could be public then you can access the table from outside the worksheet module
Private Function loData() As ListObject
If m_loData Is Nothing Then
Set m_loData = Me.ListObjects("tblData")
End If
Set loData = m_loData
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/319078.html