Do until Loop?

[Skyhawk32806]

So my 2nd question for today...

I have lots of data in sheet2. I need to find the right 'code' in column D with the correct date in column C. Once the row of information is found I need to stop the loop, as there will only be one instance in the file. But from what I'm reading a loop is a bad idea (take too long?, as I have to do this loop business about 40 with different codes and dates...)
For example:

  For Each c In Range("D:D")
                If c.Value = code And cDriverValue.Offset(0, -1) = WeekStartDate Then
                    c.Offset(0, 1).Value.Copy
                    Sheet2.Range("C1").PasteSpecial
                    Exit For
                End If
            Next c

Can it be accomplished using something like

Cells.Find(What:=Code, After:=[A1], LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, MatchCase:=False).Activate

but then also looking at the cell offset 0, -1? I'm just not sure what the best way to do something like this.

By the way I have just over 10,000 rows of data.

Thanks again, you guys are great.

 

[Jonmo1]

You would SET the result of the find to a range object variable.
Then you can use that to offset from...

Dim MyCell As Range
SET MyCell = Cells.Find(..blah..blah..blah) 'Notice no activate here
 
MyCell.Offset(0, 1).Copy

Hope that helps.

 

[Skyhawk32806]

You would SET the result of the find to a range object variable.
Then you can use that to offset from...

Dim MyCell As Range
SET MyCell = Cells.Find(..blah..blah..blah) 'Notice no activate here
 
MyCell.Offset(0, 1).Copy

Hope that helps.

Hmmm...

So I could something like:?

Dim MyCell As Range
SET MyCell = Cells.Find(CODE) 
 
MyCell.Offset(0, 1).Copy

But how do I only copy MyCell.Offset(0, 1) if MyCell(0, -1) = WeekStartDate? Is there a find next function or find until that could be used?

I guess I could combine the 2 columns together to make unique values, I'm just trying to figure out the best way to get this done.

Thanks!

 

[Jonmo1]

Here's a sample
Searches column A for "Jon"
If Offset(0,1) = "x" then gives a message box

Hope it helps.

Sub test()
Set MyCell = Range("A:A").Find("Jon", Range("A1"))
 
If Not MyCell Is Nothing Then
    If MyCell.Offset(0, 1) = "x" Then
        MsgBox "found in " And MyCell.Address
    Else
        Do Until MyCell.Offset(0, 1).Value = "x"
            Set MyCell = Range("A:A").Find("Jon", MyCell)
        Loop
        MsgBox "found in " And MyCell.Address
    End If
Else
    MsgBox "Not Found"
End If
End Sub