vba search

[Guest]

Hi,

I want to search a value in a column. But the value is in another sheet
(column I and each cell in that column).

Ex. I want to search all the values of sheetA.ColumnI in ColumnI of sheetB.
if found i give an unique number to that row (ColumnJ) in SheetA and the
same number to the matched row in sheetB (ColumnJ).

I am a beginner so first of all I tried to record a macro.
But it is to static :

Range("I1").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("I:I").Select
Selection.Find(What:="1808,22", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range("J1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Sheets("Sheet1").Select
Range("J1").Select
ActiveCell.FormulaR1C1 = "1"
Range("I2").Select
Selection.Copy



I have problems with the selection.Find :
What:=xxx has to be SheetA:I1

So the find must be in a while till end of the rows in the excel-sheet, also
the what must change for each row (I1,I2,I3,...).

How can I do this in a better way?

Thanks for help.
Nic

 

[Dave Peterson]

Maybe something like this will give you boost:

Option Explicit
Sub testme()
Dim rngA As Range
Dim rngB As Range
Dim myCell As Range
Dim res As Variant
Dim matchCtr As Long

With Worksheets("sheet1")
Set rngA = .Range("I1", .Cells(.Rows.Count, "I").End(xlUp))
End With

With Worksheets("sheet2")
Set rngB = .Range("I1", .Cells(.Rows.Count, "I").End(xlUp))
End With

matchCtr = 0
For Each myCell In rngA.Cells
res = Application.Match(myCell.Value, rngB, 0)
If IsError(res) Then
'no match, do nothing
Else
matchCtr = matchCtr + 1
myCell.Offset(0, 1).Value = matchCtr
rngB(res).Offset(0, 1).Value = matchCtr
End If
Next myCell

End Sub


It uses the worksheet function =match() to find if there's a match.