StealthRT StealthRT - 3 months ago 7
Vb.net Question

Vb.net Excel move to next row

Hey all I am looking to see how to go about moving 1 row over where I am currently in my spreadsheet.

I have tried:

Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsCell As Excel.Range

xlsCell = xlsSheet.Range("A1", "A100")

For Each oRowRange As Excel.Range In xlsCell.Rows
For Each oCellRange As Excel.Range In oRowRange.Columns

If colorRGB = "252, 213, 180" Then
Debug.WriteLine(oCellRange.Text)
oCellRange.Offset(0, 1).Select()
Debug.WriteLine(oCellRange.Text)
etc...
End If
Next
Next


In the example above, it keeps putting the same text:


BOB

BOB


When it really should be:


BOB

JON


So any help would be great!

Answer

I think your issue is that you use the same line twice Debug.WriteLine(oCellRange.Text). After the first one, the oCellRange isn't changed. You're using .Select, kind of. You do .Select, but then never use Selection...this is a good thing. However, just tweak that second line and remove the .Select line:

     Debug.WriteLine(oCellRange.Text)
     Debug.WriteLine(oCellRange.Offset(0, 1).Text)

Edit: Alternatively, you could replace the .Select line with Set oCellRange = oCellRange.Offset(0,1) and leave that third line as is. However, I don't suggest this and would personally keep the .Offset() method.

Comments