Find and loop help-multiple columns

[mattg]

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

 

[JLGWhiz]

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.

 

[joel]

I added a couple of For loops to your code to solve the problem. I di
not test the code but you should be able to figure it out yourself


With lastperiod

'Find the last used column
LastCol = .UsedRange.Columns.Count

'find the last row
LastRow = .Cells.Find(What:="*", _
lookin:=xlvalues, _
lookat:=xlpart, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row

'paste the info
.Paste

for ColCount = 1 to Lastcol
if .cells(2,Colcount) = "Route Number(s)" then
for Rowcount = Lastrow to 4 step -1
Set rngToFind
.range(.cells(4,ColCount),.cells(RowCount,colcount))
Route = .cells(RowCount,ColCount)
Set rngFound = rngToFind.Find(What:=Route
MatchCase:=False)
If rngFound Is Nothing Then
rng.Offset(0, 10).Value = "First Time"
Else
rng.Offset(0, 10).Value = "Repeat"
end if
next RowCount
end if
next Colcount
End With

 

[mattg]

Thanks for the help. What I am doing is pasting a running report. So if
value "1234" is in the newest data I want to see how many times it has
appeared on the report before. So ulimately, if "1234" appeared 2 other
times in past reports I would want a destination cell to say "Appears 3 times"

 

[JLGWhiz]

If you just want to count the occurrences then you could use the =COUNTIF()
function in Excel without bothering with VBA Check it out in the help file.
Just type in COUNTIF in the search box then click on it when the topic list
come up. It will explain how to use the function..