Read and search
 

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



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

Read and search
 

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 x200 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

Read and search
 

Thank you very much Mike.

Will get back to you if needed.

Regards
Carlos
"Mike H." wrote in message
...
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 x200 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