Better methodology to search two Microsft Excel worksheet.

[jimbo6464]

Inquiring; if there better methodology to search two worksheet. Background, I have written a macro to basically merge to worksheet. There is one to many relationships with the two worksheets; worksheet ‘A’ contains the summary record; worksheet ‘B’ has the detail records.

The macro reads a row in the worksheet ‘A’ creates a key from specific columns from worksheet ‘A’ then commencing with the first record in worksheet ‘B’ creates a key for each row in worksheet ‘B’ then compares the keys looking for match; reads the entire worksheet.

There are two do loops; the first is for worksheet ‘A’ and second is for worksheet ‘B’; I am sure thru logic; I could reduce the records that are read using the do loops.

The question I am asking; are their methodology in Microsoft Excel VBA that could be employ to improve the search the worksheet ‘B’; rather read the each record.

Example; build an index for both worksheets then used the index to locate the record.

Thanks in advance for response to the inquiry

Jim

 

[SkipVought]

Hi,

I need some clarification.

You stated, "I have written a macro to basically merge to worksheet."

You are merging two worksheets, into what? One worksheet? That's generally not a good idea as each worksheet can function as a separate table within the workbook.

Have you checked out Excel's lookup functions? I most often, use INDEX and MATCH together...

vOffset = application.match(lookupvalue, lookuprange, 0)
if not iserror(vOffset) then
  finalvalue = application.index(finalrange, voffset,1)
end if

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}