looping through a range of 2 columns

Question

My question has been answered over and over but I don't understand the solutions and therefore can't customize them to fit my needs.

Here's a link to a solution on StackOverflow Loop through each row of a range in Excel.

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A2:b22")

For Each row In rng.Rows
  For Each cell in row.Cells
    'Do Something
MsgBox cell
  Next cell
Next row

Here's my code that should go inside the loop. It should take the value of the first column, copy the data, then take the valule of the 2nd column and paste the data.

What it's doing is using the same value from the 1st column. So at what point does the value of CELL change to B2 from A2?

Windows("UnitedOrig.xlsx").Activate
Sheets(CurYearTxtPRAC).Select
Range("A4:U4").Select

ColumnFROM = MyColumnLetter(Cells.Find(What:=cell, After:=ActiveCell,  
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,  
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column)

Range(ColumnFROM & "5:" & ColumnFROM & LastRowPRAC).Select
Selection.Copy

Windows("United.xlsx").Activate
Sheets("PRACS").Select
Range("A1:U1").Select

ColumnTO = MyColumnLetter(Cells.Find(What:=cell, After:=ActiveCell, 
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, 
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column)

i apologize for the formatting, i tried editing it 4 times and i can't get it to work. — lalachka, May 08 '13 at 19:58
Not a problem. I touched up the formatting. Could you give us a before&after example? i.e. Give us 2 rows of a sample table, then show us what your code should do to it. — PowerUser, May 08 '13 at 21:02
sure)))) OriginalName MyName Sent Sent PROV-ID ProvID LST-NM LN FST-NM FN MDL-NM MN DEGREE Degree PROV-SSN SSN TAXID TIN ADR-LN-I-TXT Addr ADR-CITY City ADR-ST State ADR-ZIP ZIP ADR-ZIP-4 ZIP4 ADR-COUNTY County SPCL-DESC Specialty AREA-CD(1) AreaCode TEL-NBR(1) Telephone MARKET(1) Market EFF-DT(1) EffectiveDate i just want to be able to read the data in the columns — lalachka, May 08 '13 at 22:12
sorry, i don't know how to format here. this looks really bad. — lalachka, May 08 '13 at 22:12
Comments don't have much formatting. You can always edit your post and put your additional notes in there. — PowerUser, May 09 '13 at 16:02

Jon Crowell · Accepted Answer · 2013-05-10T04:47:01.407

In order to understand what the loop is doing, you need to monitor what is being processed. You can easily do that by changing the color of the cell or adding a border to the row.

Paste both of these subs into a module:

Sub WhereInDoubleLoop()
    Dim rng As Range
    Dim row As Range
    Dim cell As Range

    Set rng = Range("A1:D5")

    For Each row In rng.Rows
    ' Do something to the row
    row.BorderAround xlContinuous, xlThin, vbBlack
      For Each cell In row.Cells
        ' Do something to the cell
        cell.Interior.Color = vbYellow
      Next cell
    Next row
End Sub

Sub WhereInSingleLoop()
    Dim rng As Range
    Dim cell As Range

    Set rng = Range("A1:D5")

    For Each cell In rng
        ' Do something to the cell
        cell.Interior.Color = vbGreen
    Next cell
End Sub

Step through each of the loops, (F8 in the code editor), and you should gain a better understanding of exactly what is happening. Once you understand them, you can modify them for your own loop.

Kurt Wagner · Answer 2 · 2013-05-08T22:05:53.063

1

I recently did something similar (I actually did comparison of multiple cells over two different sheets) and am new to VBA, but instead of ranges I based my loops off of specific row/column numbers and I found out you don't need .Activate for reading or writing or changing cells (the below method I used instead of .Select/.Activate made runtime roughly 300-400% faster). Here's some very quick psuedo-esque code that hopefully should make sense and should be easily modifiable to what you need.

Dim oSheet As Excel.Worksheet, vSheet As Excel.Worksheet
Dim oRowCount As Integer, vRowCount As Integer
Dim oRow, vRow
Dim someDataToCopy As String

'Gets last row # for oSheet & vSheet    
oRowCount = oSheet.Cells.SpecialCells(xlLastCell).row
vRowCount = vSheet.Cells.SpecialCells(xlLastCell).row

For oRow = 2 to oRowCount
    someDataToCopy = oSheet.Cells(oRow, 4).Value 'Where 4 is an arbitrary column
    For vRow = 2 to vRowCount
        vSheet.Cells(vRow, 8).Value = someDataToCopy 'Where 8 is an arbitrary column
    Next vRow
Next oRow

You can add more 'temp' data objects (eg someDataToCopy2) to correspond if you're copying not just one cell per row, but multiple cells per row as well as adding more "= oSheet.Cells(oRow, x).Value" as needed. Let me know if you have any questions about what the code is doing!

edited May 08 '13 at 22:05

answered May 08 '13 at 21:54

Kurt Wagner

3,295
13
44
71

i do)))) it's giving me an error "object variable or With block vatriable not set" at line 6. – lalachka May 08 '13 at 22:09
Line 6 of my provided code is a comment so I'm guessing you have something different. What's happening most likely is you're not Dim-ing a variable you're using in 'line 6' or you have a missing loop closing statement (ie End If or Wend) – Kurt Wagner May 08 '13 at 22:19
Ah yes, I forgot to mention `Set oSheet/vSheet = [...]` In your case @lalachka you should be able to use something like `Sheets("PRACS").Cells(vRow, 8).Value` since it's already set. – Kurt Wagner May 08 '13 at 23:47
thank you, both of your repllies are solutions so i upvoted yours and marked his as an answer. i hope you're OK with this. sorry, i never know what to do in these cases – lalachka May 10 '13 at 00:01
No worries, I take no offense. Glad you have a working solution, regardless of the method! – Kurt Wagner May 10 '13 at 20:21

looping through a range of 2 columns

2 Answers2

Linked