Get cell value

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hello

I've got a variable that is set to the cell address of a item of data that
I'm looking for.

Do
Set rngFind = .FindNext(rngFind)
If rngFind.Address <> firstAddress Then
addSelection = addSelection & rngFind.Address & ","
'Debug.Print rngFind.Offset(0, 0).Value & " " &
rngFind.Offset(0, 1).Value & " " & rngFind.Offset(0, 3).Value & " " &
rngFind.Offset(0, 7).Value
cnt = cnt + 1
End If
Loop While Not rngFind Is Nothing And rngFind.Address <>
firstAddress


So if my cnt is greater then 1 I've found duplicate entries. The variable
addSelection then has a value of $B$80,$B$81,
To determine which cell to use, I look at the date, compare them, and go
with the latest. The datesare three columns over, so I've

Part Number Date
$B$80 $E$80
$B$81 $E$81

What the best way to do this?
I can split addSelection by the coma's and cycle over it. Then how would
get the value of cell $E$80 to assign to a compare variable?
 
I wouldn't bother.

Instead, I'd just keep set a variable to the one to use. And do a comparison
between that cell's date and the next date I find. If it's newer, then change
the variable to point at this new cell.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim FoundCell As Range
Dim UseThisOne As Range
Dim myStr As String
Dim FirstAddress As String

Set wks = Worksheets("Sheet1")

myStr = "$b$5" 'just test data

Set UseThisOne = Nothing
With wks
With .Range("B:B")
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "Not found!"
Else
Set UseThisOne = FoundCell
FirstAddress = FoundCell.Address
Do
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
'starting from the top again
Exit Do
End If

If FoundCell.Offset(0, 3).Value _
UseThisOne.Offset(0, 3).Value Then
Click to expand...
Set UseThisOne = FoundCell
End If
Loop
End If
End With
End With

If UseThisOne Is Nothing Then
MsgBox "nothing found!"
Else
MsgBox UseThisOne.Address & vbLf & UseThisOne.Offset(0, 3).Value
End If

End Sub

This doesn't have any validity checks to make sure that there really is a date
in that cell in column E. I'd add that before I trusted the output.
 
Back
Top