Read and search

[carlos]

Greetings,

I've a workbook with sheet1 sheet2 and sheet3

Sheet1 has MailIds of all members ( column A1 to A200 )

Sheet 2 has member's name, address, phone and MailIDs etc., as shown below:


Name Add1 Phone SubscriptionID
mail

John xx 1212122
(e-mail address removed)


I want to read MailIDs from sheet1 and search sheet2, if a match is found
then copy entire row (line) to sheet3

Any help will be appreciated.

Regards
Carlos

 

[Mike H.]

Here is one approach that would work

sub copystuff()
Dim X as double
dim DataArray(199,5) as variant

Sheets("sheet1").select
cells(1,1).select
let x=1
do while true
if x>200 then
exit do
end if
dataarraY(x,1)=cells(x,1).value
loop

sheets("sheet2").select
cells(1,1).select
'I am assuming the id is in column 1 and that when col 1 is blank my
searching is done.
x=1
do while true
if cells(x,1).value=empty then
exit do
end if
for y=1 to 200
if dataarray(y,1)=cells(x,1).value
'then found a match
dataarray(y,2)=cells(x,2).value 'name
dataarray(Y,3)=cells(x,3).value 'address
....etc
exit for
end if
next
Loop

sheets("sheet3").select
let y=1
for x=1 to 200
if len(dataarray(x,2).value)>0 then 'we populated this id
y=y+1
cells(y,1).value=dataarraY(x,1)
cells(Y,2).value=dataarray(x,2)
....etc.....
end if
Next

 

[John]

Thank you very much Mike.

Will get back to you if needed.

Regards
Carlos