Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel VBA search for and read URL | Excel Programming | |||
I have a read only xl file, I need it to be read and write | Excel Discussion (Misc queries) | |||
Search and read registry for particular external application | Excel Programming | |||
How can a file be converted from Read-Only to Read/Write | Excel Discussion (Misc queries) |