none
Offset to next cell with contents within 20 columns RRS feed

  • Question

  • Hi All.........

    I would like to select a cell, and then be able to Offset to the right to the next cell that contains a value, within the next 20 columns.....and if none do, then just continue on with macro.

    ie: select cell F114, and offset to the first cell with contents in the range G114:Z114.

    Any help would be much appreciated.

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Sunday, October 14, 2012 4:45 PM

Answers

  • Try this then:

        Dim c As Long
        For c = 1 To 20
            If ActiveCell.Offset(0, c) <> "" Then
                ' Found a non-blank cell, do something with it, e.g.
                ActiveCell.Offset(0, c).Select
                ' And exit the loop
                Exit For
            End If
        Next c


    Regards, Hans Vogelaar

    • Marked as answer by CABGx3 Sunday, October 14, 2012 9:48 PM
    Sunday, October 14, 2012 8:13 PM

All replies

  • Try this:

        Dim rng As Range
        Set rng = ActiveCell.End(xlToRight)
        If rng.Column <= ActiveCell.Column + 20 Then
            ' Cell is at most 20 columns to the right, so do something with it, e.g.
            rng.Select
        End If


    Regards, Hans Vogelaar


    Sunday, October 14, 2012 7:09 PM
  • Thanks Hans.........works cool but sometimes skips over value in column G if there is also a value in column H......

    Using XL2k here if that makes a difference.

    Chuck, CABgx3


    Chuck, CABGx3

    Sunday, October 14, 2012 7:26 PM
  • Try this then:

        Dim c As Long
        For c = 1 To 20
            If ActiveCell.Offset(0, c) <> "" Then
                ' Found a non-blank cell, do something with it, e.g.
                ActiveCell.Offset(0, c).Select
                ' And exit the loop
                Exit For
            End If
        Next c


    Regards, Hans Vogelaar

    • Marked as answer by CABGx3 Sunday, October 14, 2012 9:48 PM
    Sunday, October 14, 2012 8:13 PM
  • Finer than Frog Hair.............

    Many thanks Hans.

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Sunday, October 14, 2012 9:49 PM