excelforeachpi-dbvba

VBA Excel For Each Loop

The nested loops should take a point and find its description and write it into another column. The problem I have is only the value from B20 is written into C10:C20. If I change the fourth line to b.value, it works correctly for the output but in the wrong column so I believe it's a looping issue but I don't see the solution.

For Each b In Worksheets("Device").Range("B10:B20").Cells
    For Each c In Worksheets("Device").Range("C10:C20").Cells
        Set pt = srv.PIPoints(b.Value)
        c.Value = pt.PointAttributes.Item("descriptor")
    Next
Next
Solution

  • Try to use this one instead:

    For Each b in WorkSheets("Device").Range("B10:B20").Cells   
    Set pt = srv.PIPoints(b.Value)
    b.Offset(,1).Value = pt.PointAttributes.Item("descriptor")
Next

    where b.Offset(,1) gives you cell one column to the right from b, i.e. if b points to B11 then b.Offset(,1) points to C11