CODEWITHSUNDEEP
excelfor-looprangevba
JSM
JSM

Reputation: 225

How do I iterate through a range of two columns and select both columns in a row at each step?

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

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?

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'.

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

Upvotes: 2

Views: 2425

Answers (2)

ARich
ARich

Reputation: 3279

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

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

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

Upvotes: 3

teylyn
teylyn

Reputation: 35915

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

Upvotes: 3

Related Questions