Macro to extract items on seprate sheet pertaining to N/A

[Howardc1001]

I have account numbers in Col F on sheet


Where #N/A appears in Col F on "Import Data", I want all the data on sheet "GL Numbers" to be copied to sheet "extract items NA" based on the following criteria


Where reference numbers in Col A on sheet "GL Numbers" is the same as the ref numbers on sheet "Import Data" , and the sum of the values in Col G on Sheet "GL Numbers" agrees to the value in Col D , then extract the data on sheet "GL Numbers" to sheet "extract items NA"


I have attached sample data and manually extracted the data to sheet "extract items NA"


Your assistance regarding the above is most appreciated

[vba_php]

Howard,

I'm pretty sure you can do this through the interface. for the refs matching on the ''GL'' sheet to the ''IMmport Data'' sheet, I'm sure you can use VLOOKUP(). You might also be able to use what many pros do. The combination of INDEX() and MATCH(). You may have to write some code as well to get the last part of your request done. At least that's how I would do it. But I'm also pretty sure you can do everything you need with the built-in resources of Excel with no code.

[Howardc1001]

Hi Adam

Thanks for the reply. As sheet2 has the same reference appearing several times and the value makes up one reference on sheet1, I am bot sure that one can use either Vlookup and Index/Match to extract the info

[Sintek]

I assume only positive figures are important...

Please Login or Register  to view this content.

[vba_php]

looks like you got a solution Howard. please post back if it solved this for you. I think people here appreciate that.

[Howardc1001]

Hi Sintek

Only the positive figures are of importance

Thanks for the help, code works perfectly

[Sintek]

Glad I could assist...

[Howardc1001]

hi Sintek


I tried to apply you code to another workbook buy amending the code to suit the particular needs , and the layout of the heading and number of columns differs on sheet1 from the example you kindly helped me with but the criteria is exactly the same

when running the code, I get type mismatch and the ode below is highlighted


I have attached the sample data as well as what the extracted data should look like but doing this manually

Kindly amend the code

Please Login or Register  to view this content.

[Sintek]

File is corrupt...

[Howardc1001]

Thanks for letting me know. I went through your original code and amended it so that it conformed to the changes. It now makes sense why it never worked


Will set up a new workbook

[jolivanes]

Not relevant.