Determine the Row in a Range
 

Public Sub DateCopy()
Dim rngCell As Worksheet
Dim FndRng As Range
Dim x As String

For i = 2 To 5547
Set rngCell = Workbooks("test.xls").Sheets("testsheet")
Set FndRng =
Workbooks("test2.xls").Sheets("testsheet2").Range( "A:A").Find(rngCell.Rows(i
).Columns("A").Value)

If Not FndRng Is Nothing Then
Set Sh = FndRng.Parent
rw = FndRng.Row
If sh.Cells(rw,3).Value = "Yes" Then
MsgBox "This Cell's Value has already been pulled once!! (Possible
Duplicate)", vbExclamation
' sh.Cells(rw,4).Value = rngCell.Rows(i).Columns("A").Value
rngCell.Rows(i).Columns("D").Value = "Duplicate?"
Else
sh.Cells(rw, 3).Value = "Yes"
rngCell.Rows(i).Columns("D").Value = sh.Cells(rw, 2).Value
End If
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Dan" wrote in message
...
I have the code below, which works great. But rather than setting the
values in the second sheet's columns using the .Offset method, I would
rather be able to determine the FndRng's current row and use that along with
the column I want data to be entered into to set a value. Instead of
relying upon the offset method (FndRng.Offset(0, 3).Value = "Yes").
I guess I just need to know how to set the row of a range to a variable,
or a column for that matter. Thanks a lot!

Public Sub DateCopy()
Dim rngCell As Worksheet
Dim FndRng As Range
Dim x As String

For i = 2 To 5547
Set rngCell = Workbooks("test.xls").Sheets("testsheet")
Set FndRng =
Workbooks("test2.xls").Sheets("testsheet2").Range( "A:A").Find(rngCell.Rows(i
).Columns("A").Value)

If Not FndRng Is Nothing Then
If FndRng.Offset(0, 3).Value = "Yes" Then
MsgBox "This Cell's Value has already been pulled once!! (Possible
Duplicate)", vbExclamation
' FndRng.Offset(0, 4).Value = rngCell.Rows(i).Columns("A").Value
rngCell.Rows(i).Columns("D").Value = "Duplicate?"
Else
FndRng.Offset(0, 3).Value = "Yes"
rngCell.Rows(i).Columns("D").Value = FndRng.Offset(0, 2).Value
End If
End If
Next

End Sub