VBA/MACRO for Auto detecting rows with items passing certain criterias

[acerlaptop]

Hi guys,

I have a workbook here that has a array formula on "Shipped" worksheet on Column A. Basically, this array formula will detect row numbers of items from different worksheet/workbook which passes multiple criterias.

What I want is to make it a VBA if its possible. I don't know anything about VBA or Macros but all I want is to make my workbook more faster. The array formula seems make my workbook unresponsive. That's why i'm gonna try VBAs and hope it will solve my problem.

Thanks in advance.

[watersev]

hi acerlaptop, option, please check attachment

[acerlaptop]

hi acerlaptop, option, please check attachment

ok you got me amazed there.! ^^

how do I edit your code to my preferences???

[acerlaptop]

ok you got me amazed there.! ^^

how do I edit your code to my preferences???

there are differences between that sample and my actual data. the column reference doens't actually match, the column header doesn't match, my data entry on my actual summary starts on row four not row 2 as compared to the data reference which starts at row 2. and so on ^^

[watersev]

can you tell me the reason of making sample file data layout different from the original data layout?

[acerlaptop]

can you tell me the reason of making sample file data layout different from the original data layout?

well i can't really remake the exact data because it's too bulky, but if you want I can attach my original data..

[acerlaptop]

can you tell me the reason of making sample file data layout different from the original data layout?

or you can just help me edit your code to my preference.. ^^ I also want to learn VBAs ^^

[acerlaptop]

or you can just help me edit your code to my preference.. ^^ I also want to learn VBAs ^^

and one more thing, I will also be using this code on my other reports, which basically doesn't really match with my current data. So please just let me know how to edit your code.

Would really appreciate if you could do that for me.

Thanks..

[watersev]

please provide sample file with the same data layout as in you original data file. The code needs input parameters: kind, date from, date to, source data table fields and result table fields. All of those as per layout of the real data file.

[acerlaptop]

please provide sample file with the same data layout as in you original data file. The code needs input parameters: kind, date from, date to, source data table fields and result table fields. All of those as per layout of the real data file.

ok.. i'll attache the data.. but i will also be using your code on my other reports. thats why i want to know how to edit your code to adapt it to my other reports which basically requires same procedures and end result but different structure..

anyway here's the data.

The delivered worksheet is my summary, reference is in the summary work sheet (merchant name, date from, and date to)
oms data is the data source.
created date from (oms data) column F
date delivered from (oms data) column J
SKU from (oms data) column C
Details from (oms data) column D
Unit price from (oms data) column E
Sales order item from (oms data) column B
Seller SKU from (oms data) column AB
Order no. from (oms data) column A
Status from (oms data) column M
Destinataion from (oms data) column L (please replace all "~" with space " ")

[watersev]

as far as I understand the data from OMS file should be filtered for Seller name and Reconciliation Period and status Delivered from SC FEB file sheet Summary. The result should a sheet with the 10 fields specified above. Which date should fall into the reconciliation period created or delivered or both? Is it correct?

[acerlaptop]

as far as I understand the data from OMS file should be filtered for Seller name and Reconciliation Period and status Delivered from SC FEB file sheet Summary. The result should a sheet with the 10 fields specified above. Which date should fall into the reconciliation period created or delivered or both? Is it correct?

yes that's correct..
the recon period should be the date delivered..
and you can remove the row column on delivered worksheet..

[acerlaptop]

as far as I understand the data from OMS file should be filtered for Seller name and Reconciliation Period and status Delivered from SC FEB file sheet Summary. The result should a sheet with the 10 fields specified above. Which date should fall into the reconciliation period created or delivered or both? Is it correct?

additional:

Region from (Location Mapping worksheet) column B based on region
Shipment Provider (OMS Data) column Y
Delivery Rate from (Delivery Rate Card) table based on "region" and "shipment provider"

[watersev]

please check attachment, press Run button on Summarry sheet, in Open file window select OMS file, result goes to Delivered sheet

[acerlaptop]

please check attachment, press Run button on Summarry sheet, in Open file window select OMS file, result goes to Delivered sheet

Run-time error '7':

Out of memory

help.. thanks..

[watersev]

I do not get such error on a sample file provided. Try this option then

[acerlaptop]

hi acerlaptop, option, please check attachment

Hi, I'm downloading the file you attached but for some reason, the downloaded file don't have macro in it.

Hope you can help thanks