用双“For”循环标记区域 [英] Marking an area with a double 'For' loop
问题描述
向右,两下。右边和下边的区域数量可能会有所不同,所以我想要编码,我只需要指定向右和向下的区域。请注意,第一个区域始终是F7:G8,所以我可以使用它作为参考。现在,在下一个区域之前跳过的列数可能会有所不同,并且在下一个区域之前跳过的行数可能会有所不同。所以这个我需要考虑。
我有以下VBA代码:
< code
$ Dim b As Integer,j As整数
k = 2'区域向下'
l = 3'区域向右'
暗淡区域作为字符串
让area =F7:G8'first area,always同样的'
Dim Upper_letter As String
让Upper_letter =F
Dim Upper_nr As String
让Upper_nr =7
Dim Lower_letter As String
让Lower_letter =G
Dim Lower_nr As String
让Lower_nr =8
i = 1到k
对于j = 1到l
area = area& ,& Upper_letter& Upper_nr:& Lower_letter& Lower_number
'每次迭代j后,如何在Upper_letter和Lower_letter上添加3个字母?'
下一个j
upper_nr = upper_nr + 3'迭代i,在每次迭代后添加3'
lower_nr = lower_nr + 3',添加3'
下一个我
结束Sub
所以我没有看到如何在最里面的循环中添加字母。
请试试这个:
Sub ErosRam()
Dim i& j& area $,k As范围,r As范围
Const COL_PERIOD = 3
Const ROW_PERIOD = 3
Const REPS_HORIZONTAL = 3
Const REPS_VERTICAL = 2
Set r = [f7:g8]
Set k = r
For i = 0 REPS_VERTICAL - 1
对于j = 0到REPS_HORIZONTAL - 1
设置k = Union(k,r.Offset(i * ROW_PERIOD,j * COL_PERIOD))
下一个
下一个
area = k.Address(0,0)
MsgBox area
End Sub
您可以编辑顶部的 Const
行来更改周期和重复次数。
I am trying to do the following: I want to store different areas (marked in yellow) in a string. The first yellow area is F7:G8, second is I7:J8 and so on, such that the string becomes: "F7:G8,I7:J8,L7:M8,F10:G11,I10:J11, L10:M11".
So in this example I have three areas to the right, and two down. The number of areas to the right and downwards may vary, therefore I want to make a code where I only have to specify how many areas to the right and downwards. Note that the first area always is F7:G8, so this I can use as a reference. Now, how many columns one skips before next area might vary, and also how many rows one skips before next area might vary. So this I need to take into account.
I have the following VBA code:
Sub test()
'
' test Makro
'
'
Dim i As Integer, j As Integer
k = 2 'areas downwards'
l = 3 'areas rightwards'
Dim area As String
Let area = "F7:G8" 'first area, always the same'
Dim Upper_letter As String
Let Upper_letter = "F"
Dim Upper_nr As String
Let Upper_nr = "7"
Dim Lower_letter As String
Let Lower_letter = "G"
Dim Lower_nr As String
Let Lower_nr = "8"
For i = 1 To k
For j = 1 To l
area = area & "," & Upper_letter & Upper_nr ":" & Lower_letter & Lower_number
'How do I add 3 letters to both Upper_letter and Lower_letter after each iteration of j?'
Next j
upper_nr = upper_nr + 3 'after each iteration of i, add 3'
lower_nr = lower_nr + 3 'after each iteration of i, add 3'
Next i
End Sub
So I fail to see how I can add letters, in the innermost loop.
Please try this:
Sub ErosRam()
Dim i&, j&, area$, k As Range, r As Range
Const COL_PERIOD = 3
Const ROW_PERIOD = 3
Const REPS_HORIZONTAL = 3
Const REPS_VERTICAL = 2
Set r = [f7:g8]
Set k = r
For i = 0 To REPS_VERTICAL - 1
For j = 0 To REPS_HORIZONTAL - 1
Set k = Union(k, r.Offset(i * ROW_PERIOD, j * COL_PERIOD))
Next
Next
area = k.Address(0, 0)
MsgBox area
End Sub
You can edit the Const
lines at the top to change the period and the number of repetitions.
这篇关于用双“For”循环标记区域的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!