Sep 24 2021 03:22 PM
Sep 24 2021 03:22 PM
I am so happy to join the microsoft community and now I am learning Excel VBA and confront below daily work problem, I would be highly appreciated if any friend can help me figure out a valid marco ?
my work need to deal with at least 20 above solid format invoces ,but i only need to extract out below green area data :
the final data i need to extract require as below :
1- alwayse start from solid 24th row till the row of last valid sku located （ here is 31th row, and total row often change）
2- alwayse start from solid column A till the column which "USD unit price" located ( here is column AC)
3- extract all targeted cells area between above 2 points ( here is A24:AC31) then paste the targeted cells area under the last valid sku's located row of above original invoice ( here is 33th row) .
4- judge each targeted row from first size( here is 30#) to last size( here is 48#), if SUM( H25:Z25) = range("AB25").value then go to check next row .
if SUM( H25:Z25) not equal to range("AB25").value then replace each cell value( of H25:Z25) with the calculate result of each origial cell value multiply range("AA25").value
above 2nd "if" explain : if SUM( H25:Z25) not equal to range("AB25").value, it means each cell value of this row is only represent the size assortment of this row rather than i wanted result ( = each cell value need show exact total qty of this row sku.( above 2nd picture can better explain)
Very looking forward you could help figure out valid marco for above question so that i can speedy deal with such invoices . and i think the most valid and fast learning way is try as much as we can to use this tool to solve daily work problem .
Oct 04 2021 11:48 PM - edited Oct 04 2021 11:48 PM
Please post a sample invoice workbook, and we will create a helper sheet template to do the work for you so all you have to do is to copy the invoice into the template and the helper sheet will do the checking so after you copy the invoice into the template all you have to do is go to the helper sheet and copy the results back into the original invoice as a value. then on to the next invoice. all with just formulas no vba