Loop with activecell.offset

[mthomas]

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....

 

[Bernie Deitrick]

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

 

[Bruno Campanini]

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

 

[mthomas]

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!