search and offset?

[Guest]

I am trying to search a column in my worksheet, and if that column has a
value that is not 1, then compare it to the cell value in the same row in
column N. If the two cell values are different then put the current
selection in column R. Does that make sense? Here is my code. any help is
appreciated!

Dim wks As Worksheet
Dim rngFound As Range
Set wks = Sheets("exam_am_trades")
Set rngToSearch = Range("p41700")
saddr = ActiveCell.Address
Set rngFound = rngToSearch.Find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound = 1 Then
Set rngFound = rngToSearch.FindNext
Else
If rngFound <> saddr.Offset(0, -2) Then
saddr.Offset(0, 2) = saddr.Value
End If
End If
End Sub

 

[Jim Cone]

John,

The Find method might be even faster, but this works...
Jim Cone
San Francisco, USA

'-----------------------------
Sub FindAndEnterValues()
Dim rngCell As Range
Dim rngToSearch As Excel.Range
Dim varValue As Variant

Sheets("exam_am_trades").Activate
Set rngToSearch = Range("p41700").Cells

'note: rngCell(1, 1) is the cell in column P

For Each rngCell In rngToSearch
varValue = rngCell.Value
If varValue <> 1 Then
'Col n
If rngCell(1, -1).Value <> varValue Then
'Col r
rngCell(1, 3).Value = varValue
End If
End If
Next 'rngCell

Set rngCell = Nothing
Set rngToSearch = Nothing
End Sub
'----------------------------------


"John"
<[email protected]>
wrote in message
I am trying to search a column in my worksheet, and if that column has a
value that is not 1, then compare it to the cell value in the same row in
column N. If the two cell values are different then put the current
selection in column R. Does that make sense?
Here is my code. any help is appreciated!

Dim wks As Worksheet
Dim rngFound As Range
Set wks = Sheets("exam_am_trades")
Set rngToSearch = Range("p41700")
saddr = ActiveCell.Address
Set rngFound = rngToSearch.Find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound = 1 Then
Set rngFound = rngToSearch.FindNext
Else
If rngFound <> saddr.Offset(0, -2) Then
saddr.Offset(0, 2) = saddr.Value
End If
End If
End Sub

 

[Guest]

Thanks Jim, I will have to compare yours to mine and see why the difference