Can a VBA function in excel be assigned to always be tied to a Range?-CodePudding

I have simple code that clears out the "State" cell whenever the "Payroll Country" cell changes. For example if the user selects "USA" in A6 and then selects "Arizona" in X6, then maybe later for some reason they change their mind and want to pick "CAN" for the country, the state cell will clear out.

But if someone in the future decides to insert a column before the X column, it will obviously move my State column over. Is there a way to make the VBA smarter (or make me smarter) so that the function will be tied to the "State" column rather than the specific "X" column?

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

CodePudding user response：

You can use a named range, or you can use .Find to determine where your State column currently is. Here is an example using .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

If you were to use a named range instead, you can define StateCol using StateCol = Me.Range("NamedRange").Column, it would be a little bit faster, since it doesn't need to search the row each time the user changes a value.

Side Note: The search range for .Find is Rows 1 to 5, but you may want to restrict or expand that range based on how you expect the data to move.

CodePudding user response：

I (always) define an enum for the columns, like this

Public enum col_TableXXX    'adjust to your needs
  col_ID = 1
  col_PayrollCountry
  col_State
end enum

enums are numbered automatically - so col_PayrollCountry equals to 2, col_State equals to 3 etc.

In case there are new columns or the order changes you only have to move the enums around or add a new enum.

(You can avoid code typing by transpose-pasting the column titels on an excel sheet and then create the code via formulas)

You can then use the enums like this:

If target.column = col_PayrollCountry then
   target.entireRow.columns(col_State) = vbnullstring
End If

This is also much more "readable" than columns("X")

Culprit of this solution: you have to know that the columns changed. It is not an automatism that is based on the columns name.