Find row with the same data like in another row

[Bartosz]

Hello All,

I try to learn VBA myself. But it stoped me:
Workbook A contain lot of rows. Workbook B contain one row which I need to
find in Worbook A. I try to find row in Workbook A which contain the same
data (First 20 columns contain data) like row 1 in Workbook B.

Thanks

 

[Mike H.]

Here is one way:

Sub FindRowInOther()
Dim Y as double
Dim Y as double
Dim Found as integer
dim Dataarray(20,1) as variant
sheets("Lookfordata").select
for X=1 to 20
dataarray(x,1)=cells(activecell.row,x).value
next

sheets("lookinData").select
x=1
do while true
if cells(x,1).value=empty then exit do 'assumes nothing in column A
ends search
found=0
for Y=1 to 20
if cells(x,Y).value=dataarray(x,1) then
found=Found+1
end if
end for
if found=20 then
msgbox("Found it! on row " & x)
cells(x,1).select
Ans=1
exit do
end if
x=x+1
Loop

if Ans=0 then
msgbox("Could not find your data!")
end if

End sub

 

[Bartosz]

I made one change:
....was:

for Y=1 to 20
if cells(x,Y).value=dataarray(x,1) then

Should be: if cells(x,Y).value=dataarray(Y,1) then

Thanks anyway!!!

 

[Mike H.]

I think I had it right the first time I typed my initial reply. But
sometimes when i click "Post", I get a login-in screen which when I finally
remove it it says I am not authorized to upload a reply. I lose the text I
just typed and have to retype it if I forget to save the test outside of the
Message Box where I am typing this. But anyway, glad you can use it.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Sub test12()
Dim rng1 As Range, rng2 As Range, iRow As Range
Set rng1 = Sheets("A").Range("A120").Rows
Set rng2 = Sheets("B").Range("A11")
For Each iRow In rng1
If RowsEqual(iRow, rng2) Then
MsgBox iRow.Row
Exit For
End If
Next
End Sub

Alan Beban