更改运行宏的范围
问题描述:
我想知道是否有任何方法可以更改运行宏的范围?例如,如果我有:更改运行宏的范围
Public rng1 As Range
在宏1
和 Set rng1 = Range("K10").Offset(i, 0)
,我想改变位置,以1元到正确的 - 用macro2 - 某事像这样:
Set rng1 = rng1.Offset(0, 1)
我的意思是,那甚至有可能?或者有另一种方法来做到这一点?
整个代码:
Public rng1 As Range
Sub tert()
Dim rng As Range
Dim rnga As Range
Dim i As Integer
i = 0
Do
DoEvents
Set rng1 = Range("K10").Offset(i, 0)
Set rng = Range("J10").Offset(i, 0)
Set rnga = Union(rng, rng1)
rng.Interior.ColorIndex = 3
rng1.Interior.ColorIndex = 3
rng.Offset(-4, 0).clear
rng1.Offset(-1, 0).clear
i = i + 1
Application.Wait (Now + TimeValue("00:00:01"))
If Not Intersect(Range("A30:Z30"), rnga) Is Nothing Then Exit Sub
If rng.Offset(1, 0).Interior.ColorIndex = 3 Then Exit Sub
Loop
End Sub
Sub rightx()
Set rng1 = rng1.Offset(0, 1)
End Sub
答
只是为了demonstarte它是如何工作的:
Option Explicit
Sub DynamicRange()
Dim rng1 As Range
Dim i As Long
' let's take 1 as the offset value
i = 1
Set rng1 = Range("K10").Offset(i, 0)
Debug.Print rng1.Address ' << you get $K$11
Set rng1 = rng1.Offset(0, i)
Debug.Print rng1.Address ' << you get $L$11
End Sub
编辑1:将代码放在2个独立的Sub
S,移动子外rng1
声明,宣布它为Public
Option Explicit
' moved the rng1 declaration outside the Sub and declared it as Publi
Public rng1 As Range
Sub Call2Macros()
Call DynamicRange
Call DynamicRange2
End Sub
Sub DynamicRange()
Dim i As Long
i = 1
Set rng1 = Range("K10").Offset(i, 0)
Debug.Print rng1.Address ' << you get $K$11
End Sub
Sub DynamicRange2()
Dim i As Long
i = 2
Set rng1 = rng1.Offset(0, i)
Debug.Print rng1.Address ' << you get $M$11
End Sub
答
这是一个基本的例子。通过声明你的程序以外的范围内,可以使用任意的子您的模块
Dim rng As Range
Sub test1()
Dim i As Integer
For i = 1 To 2
Set rng = Range("K10").Offset(i, 0)
Debug.Print rng.Address
OffsetRange
Debug.Print rng.Address
Next i
End Sub
Sub OffsetRange()
Set rng = rng.Offset(0, 1)
End Sub
在这不是代码的特别有用的片 - 它是所有偏移和调试该位置。但是,它应该说明一种方法可以解决您的问题
您试过吗?当你尝试你提出的建议时发生了什么?这就是其中一种情况,就是你要问的时间,你已经有了答案。现在,如果你尝试了它,但它不工作,那是一个单独的问题...... – sous2817
让我告诉你一个秘密,是的,它的工作原理:)你可以使用'Debug.Print rng1.Address'和在眼前的窗口看到它 –
y我试过了,会发生什么是...完全没有:) – Pawel