在VBA中设置动态范围 [英] Setting Dynamic Ranges in VBA

问题描述

我在下面有以下代码.而不是使用停滞范围(即Range("AF9:AF50")&Range(AK9:BI50))，我希望实现一种动态范围，该范围可运行代码，该代码从第9行开始到最后一行数据这些专栏.我一直在阅读如何设置动态范围，但我无法使其正常工作.任何建议/帮助都将不胜感激.

I have the following code below. Instead of using stagnant ranges (i.e., Range("AF9:AF50") & Range(AK9:BI50")) I'm looking to implement a dynamic range that runs the code starting at row 9 through the last row of data for those columns. I've been reading on how to set dynamic ranges but I can't get it to work. Any advice/assistance is greatly appreciated.

Private Sub Worksheet_Change(ByVal target As Range)

Dim cell As Range
Dim controlRng As Range, nRng As Range

Set cell = Range("AK9:BI50")
Set controlRng = Range("AF9:AF50")
Set nRng = Intersect(controlRng, target)

Application.EnableEvents = False

If Not nRng Is Nothing Then
    Select Case target.Value
        Case "No Promotion"
            target.Offset(, 1).Value = Range("M" & target.Row).Value
            target.Offset(, 4).Value = Range("P" & target.Row).Value
            target.Offset(, 9).Value = ""
        Case "Promotion"
            target.Offset(, 1).Value = ""
            target.Offset(, 4).Value = ""
            target.Offset(, 9).Value = 0.07
        Case "Demotion", "Partner", ""
            target.Offset(, 1).Value = ""
            target.Offset(, 4).Value = ""
            target.Offset(, 9).Value = ""
    End Select
End If

If Not Application.Intersect(cell, target) Is Nothing Then
    Select Case target.Column
        Case 37, 39, 43
            target.Offset(, 1).Value = target.Value / Range("V" & target.Row).Value
        Case 38, 40, 44
            target.Offset(, -1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" & target.Row).Value), -2)
        Case 41, 60
            target.Offset(, 1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" & target.Row).Value), -2)
        Case 42, 61
            target.Offset(, -1).Value = target.Value / Range("V" & target.Row).Value
    End Select
End If

Application.EnableEvents = True

End Sub

推荐答案

如果我正确理解了该问题，则列和起始行是静态的，只需要动态的最后一行引用即可.

If I understand the question correctly, the columns and starting rows are static and only a dynamic last row reference is needed.

在 Worksheet_Change 事件中:

Set cell = Range("AK9:" & LastRow("BI"))
Set controlRng = Range("AF9:" & LastRow("AF"))

选择一个行号，该行号应大于您的数据可能具有的任何行数(例如1000).然后，在同一模块中:

Choose a row number larger than any number of rows your data will likely have (e.g.1000). Then, in the same module:

function LastRow(strColumn as string) as long
  LastRow=range(strColumn & 1000).end(xlup).row
end function

每次引发 Worksheet_Change 事件时，

VBA都会重新计算 LastRow ，从而使您的范围充满活力.

VBA will re-calculate the LastRow every time the Worksheet_Change event is raised, thus making your ranges dynamic.

这篇关于在VBA中设置动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！