Syntax for looping through rows and columns in Excel

[Curious]

I have an excel file containing a section from column B to column G,
and from row 14 to row 45. How can I loop through each row then each
column to get the cell value?

Thanks!

 

[Guest]

One way...

Sub test()
Dim rngToSearch As Range
Dim rng As Range

Set rngToSearch = Range("B14:G14")

Do While rngToSearch.Row <= 45
For Each rng In rngToSearch
MsgBox rng.Value
Next rng
Set rngToSearch = rngToSearch.Offset(1, 0)
Loop
End Sub

 

[Curious]

Thanks Jim!

 

[Dave Peterson]

You could just loop through each cell in that range. It'll go through each row.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = ActiveSheet.Range("B14:G45")

For Each myCell In myRng.Cells
MsgBox myCell.Address
Next myCell

End Sub


If you were afraid that excel's behavior could change, you could loop through
the rows yourself:

Sub testme2()
Dim myRng As Range
Dim myRow As Range
Dim myCell As Range

Set myRng = ActiveSheet.Range("B14:G45")

For Each myRow In myRng.Rows
For Each myCell In myRow.Cells
MsgBox myCell.Address
Next myCell
Next myRow

End Sub

 

[Curious]

Dave,

I want to output the cell values to a txt file by doing something like
below:

For Each myRow In myRng.Rows
For Each myCell In myRow.Cells
If myCell is not in column G Then ' What's the
correct syntax for detecting if the cell is in Column G??
Print #1, myCell.Value + ", "
Else
Print #1, myCell.Value + chr(10) ' add new
line character
End If
Next myCell
Next myRow

 

[Curious]

I now have the following:

--------------------------------------------------------------------------------------------------------------------------------------------------------------
Open "C:\Temp\Output.txt" For Output As #2

Dim rngToSearch As Range
Dim rng As Range


Dim myRng As Range
Dim myRow As Range
Dim myCell As Range


Set myRng = Worksheets("Options").Range("B14:G45")


For Each myRow In myRng.Rows
Print #2, "Exec SPcorTestTOC "
For Each myCell In myRow.Cells
If myCell.Column < G Then '
This doesn't work. I want to detect if the column the current cell is
in is before column G

Print #2, myCell.Value + ","
Else
Print #2, myCell.Value + Chr(10)
End If

Next myCell
Next myRow

 

[Dave Peterson]

You can use mycell.column to return the number of the column that you're looping
through, but that may not be the best way.

But maybe you could do it this way instead:

(I used crlf's at the end of each row in this version)

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myRow As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Worksheets("Options").Range("B14:G45")

Close #2 'just in case it's open
Open "C:\Temp\Output.txt" For Output As #2

For Each myRow In myRng.Rows
myStr = "Exec SPcorTestTOC "
For Each myCell In myRow.Cells
myStr = myStr & myCell.Value & ","
Next myCell
myStr = Left(myStr, Len(myStr) - 1)
Print #2, myStr
Next myRow

Close #2
End Sub

If you really wanted vblf's (Chr(10)'s), then change that print #2 statement to:

Print #2, myStr & vbLf;