+ Reply to Thread
Results 1 to 4 of 4

Loop with activecell.offset

  1. #1
    Registered User
    Join Date
    07-27-2005
    Posts
    24

    Loop with activecell.offset

    Need to accomplish the following:

    1. Loop through column B until isEmpty.
    2. Locate cell = "Date" in column B,
    3. If = Date Then offset(0, "A") (i.e. cell in column A, same row) =
    4. Offset(-6, 2) (i.e. value in the cell 6 rows up in column C)
    5. Locate the next cell = "Date" and repeat.

    Example:

    Do Until Column B isEmpty
    'Cell B8 is the first cell found in the loop with the word Date.
    If Cell(B8) = "Date" Then
    Cell(A8) = Cell(C2).Value
    End If
    Loop

    Thanks everyone for your expertise....

  2. #2
    Bernie Deitrick
    Guest

    Re: Loop with activecell.offset

    mthomas,

    Using activecell.offset to loop is not as efficient as using the built-in find method. See the code
    below.

    HTH,
    Bernie
    MS Excel MVP

    Sub FindDates()
    Dim c As Range
    Dim myFindString As String
    Dim firstAddress As String

    myFindString = "Date"
    With Range("B:B")

    Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

    If Not c Is Nothing Then
    firstAddress = c.Address
    c(1, 0).Value = c(-5, 2).Value
    Else:
    MsgBox "Not Found"
    End
    End If

    Set c = .FindNext(c)
    If Not c Is Nothing And c.Address <> firstAddress Then
    Do
    c(1, 0).Value = c(-5, 2).Value
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With

    End Sub


    "mthomas" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Need to accomplish the following:
    >
    > 1. Loop through column B until isEmpty.
    > 2. Locate cell = "Date" in column B,
    > 3. If = Date Then offset(0, "A") (i.e. cell in column A, same row) =
    > 4. Offset(-6, 2) (i.e. value in the cell 6 rows up in column C)
    > 5. Locate the next cell = "Date" and repeat.
    >
    > Example:
    >
    > Do Until Column B isEmpty
    > 'Cell B8 is the first cell found in the loop with the word Date.
    > If Cell(B8) = "Date" Then
    > Cell(A8) = Cell(C2).Value
    > End If
    > Loop
    >
    > Thanks everyone for your expertise....
    >
    >
    > --
    > mthomas
    > ------------------------------------------------------------------------
    > mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
    > View this thread: http://www.excelforum.com/showthread...hreadid=486370
    >




  3. #3
    Bruno Campanini
    Guest

    Re: Loop with activecell.offset

    "mthomas" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Need to accomplish the following:
    >
    > 1. Loop through column B until isEmpty.
    > 2. Locate cell = "Date" in column B,
    > 3. If = Date Then offset(0, "A") (i.e. cell in column A, same row) =
    > 4. Offset(-6, 2) (i.e. value in the cell 6 rows up in column C)
    > 5. Locate the next cell = "Date" and repeat.
    >
    > Example:
    >
    > Do Until Column B isEmpty
    > 'Cell B8 is the first cell found in the loop with the word Date.
    > If Cell(B8) = "Date" Then
    > Cell(A8) = Cell(C2).Value
    > End If
    > Loop
    >
    > Thanks everyone for your expertise....


    Replace
    If UCase(i) = UCase("Date") Then
    with
    If i = "Date" Then
    if you want the search to be case-sensitive.
    In any case Error will be generated if "Date" is found
    at less than B8.
    ==========================
    Sub Button359_Click()
    Dim i
    For Each i In Range([Sheet3!B1], [Sheet3!B1].End(xlDown))
    If UCase(i) = UCase("Date") Then
    i.Offset(0, -1) = i.Offset(-6, 1)
    End If
    Next
    End Sub
    ===========================
    Ciao
    Bruno



  4. #4
    Registered User
    Join Date
    07-27-2005
    Posts
    24
    Just wanted to let you know that due to simplicity and I'm a little more familiar with For...Next loops, I chose to use it AND IT WORKS GREAT! No reflection on the "replace" method.

    Thanks again Bruno and Bernie for your help! You are appreciated!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1