如何遍历两列范围并在每一步中连续选择两列？

I am trying to automate the task of defining names. Specifically, I have two columns A and B, with B being dependent:

我试图自动化定义名称的任务。具体来说,我有两列A和B,B依赖:

A          B
Label1     Data1
Label2     Data2
Label3     Data3

I want to select both columns in a defined range, and then iterate through it, defining column B with the name in Column A. I figured a FOR EACH construct would work, but I can't find anything about how Excel exactly reads each item in the FOR EACH. Does it see both columns in the current row as one unit (this would probably be ideal for my purpose)? Does it iterate down one column then the other? Does it iterate each column in the row, then move down?

我想在定义的范围内选择两个列,然后遍历它,在列A中定义名为B的列。我认为FOR EACH构造可以工作,但我找不到任何关于Excel如何准确读取每个项目的信息在FOR FOR EACH中。它是否将当前行中的两列都视为一个单元(这可能是我的理想选择)?它是否向下迭代一列而另一列?它是否迭代行中的每一列,然后向下移动?

Ultimately, I will be doing this for 3 or 4 pairs of columns. They will be used in the end to populate labels or selection boxes in a userform. For example, if you select from a ComboCox 'Bacon', the label dependent on it will populate as 'Eggs'. IF you select 'Oatmeal', it populates 'Raisins'.

最终,我将为3或4对列进行此操作。它们最终将用于填充用户表单中的标签或选择框。例如,如果您从ComboCox'Bacon'中选择,依赖于它的标签将填充为'Eggs'。如果你选择'燕麦片',它会填充'葡萄干'。

I have large columns I want to link up, and figured a quick macro would make it faster.

我想要链接大型列,并想出一个快速宏将使它更快。

Thanks

CODE SO FAR:

代码如此:

Sub FeatureNumberAssignMk2()
'
' FeatureNumberAssignMk2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Dim myCells As Range

Set myCells = Selection
For Each Row In myCells
    ' THIS IS WHERE I AM NOT SURE WHAT IS BEING SELECTED
    Row.CreateNames Top:=False, Left:=True, Bottom:=False, Right:= _
    False
Next
End Sub

2 个解决方案

#1

There is no need to loop. If you select a range comprising two columns, then that command will create a name for each row, using the text in the first column.

没有必要循环。如果选择包含两列的范围,则该命令将使用第一列中的文本为每行创建名称。

myCells.Row.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False

#2

...but I can't find anything about how Excel exactly reads each item in the FOR EACH...

...但我找不到任何关于Excel如何准确读取FOR FOR EACH中的每个项目...

For your reference, this is an easy way to test how the For Each will loop:

供您参考,这是一种简单的方法来测试For Each将如何循环:

Dim Cell As Range
Dim Rng As Range

Set Rng = Range("A1:B5")

For Each Cell In Rng
    Debug.Print Cell.Address
Next Cell

The output to the immediate window is as follows:

立即窗口的输出如下:

$A$1
$B$1
$A$2
$B$2
$A$3
$B$3
$A$4
$B$4
$A$5
$B$5

#1