What do you want to do with it if you find it in other columns? If you just
want to know it it is there then you could use the CountIf method to just
see if it is there. If you want to do something with any of the other cells
where it is found then you could use a For...Each statement.

Assume the data is located tin A through n number of colums and 2 through n
number of rwos which vary by column.

Dim lr As long, lc As long 'delare variables for last row and column
Dim sh As Worksheet, cRng As Range
'Initialize the variables
Set sh = ActiveSheet
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
lc = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
Set cRng = sh.Range("A2:A" & lr)
'assign the value to find to a variable
fVar = sh.Range("BB1").Value 'Arbitrary value, replace with actual
'See if the value exists in the control range
Set fItem = sh.cRng.Find("fVar", After:=Range("A" & lr), LookIn:=xlValues)
If Not fItem Is Nothing Then
myNum = CountIf( sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)),
fItem.Value)
MsgBox "There are " & myNum & " other occurrences of " & fVar
End If
End Sub

Using the For ... Each you would basically set upt the range the same but
Once the item is found in the control range you would:

For Each fRng in sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr))
If fRng.Value = fVar Then
'Do something
End If
Next

This code has not been tested and is provided to illustrate methods for
searching items in a data base.



"mattg" wrote in message
...
I an trying to write a macro to search for a cell value in one column and
see
if it occurs in other columns

I'm having 2 problems:

1. How can I look from the last used row up to row 4 in "myrange+5" as
set
below?

2. How can I look in all other columns beside the "myrange+5" column
named
"Route Number(s)"?

Am I completely off track??

With lastperiod

'Find the last used column
myrange = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells(1, myrange + 2).Select

'find the last row
therow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious,
SearchOrder:=xlRows).Row

'paste the info
ActiveSheet.Paste


'start checking for duplicate route numbers
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet


Set rngToSearch = .Range('NEED HELP HERE--myrow+5')
Set rngToFind = .Range('NEED HELP HERE--all other columns with "Route
Number(s)" in Row 2')


For Each rng In rngToSearch
Set rngFound = rngToFind.Find(What:=rng.Value, MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time"
Else rng.Offset(0, 10).Value = "Repeat"

Next rng

End With