Prezados,
Tenho duas tabelas/plan distintas: uma com o total de vendas de cada produto (que não indica qual loja vendeu) e outra com o total de vendas de cada loja (que não menciona quais produtos foram vendidos), conforme modelos a seguir:

Descricao Qtde %
Prod 1 6 0,03%
Prod 2 291 1,40%
Prod 3 44 0,21%
Prod 4 80 0,38%
Prod 5 335 1,61%
Prod 6 80 0,38%
Prod 7 3.000 14,42%
Prod 8 7 0,03%
Prod 9 615 2,96%
Prod 10 44 0,21%
Prod 11 5 0,02%
Prod 12 115 0,55%
Prod 13 122 0,59%
Prod 14 298 1,43%
Prod 15 10 0,05%
Prod 16 94 0,45%
Prod 17 26 0,12%
Prod 18 217 1,04%
Prod 19 153 0,74%
Prod 20 13.095 62,93%
Prod 21 665 3,20%
Prod 22 83 0,40%
Prod 23 79 0,38%
Prod 24 555 2,67%
Prod 25 110 0,53%
Prod 26 35 0,17%
Prod 27 643 3,09%
Prod 28 1 0,00%
Total 20.808 100,00%

Loja 1 1.600 7,7%
Loja 2 2.502 12,0%
Loja 3 688 3,3%
Loja 4 4.919 23,6%
Loja 5 2.212 10,6%
Loja 6 3.004 14,4%
Loja 7 2.302 11,1%
Loja 8 2.279 11,0%
Loja 9 1.302 6,3%
Total 20.808,00 100,0%

Preciso ratear, ou seja, distribuir os totais por produto para todas as lojas, pois não tenho, mas preciso, estimar a qtde de produtos vendidos por loja.

Minha ideia está sendo tirar o percentual da participação de venda cada loja sobre o total e o percentual de participação nas vendas de cada produto nas vendas totais e ratear para cada loja a quantidade de cada produto. É lógico que não refletirá a realidade mas como não tenho melhor informação, o melhor que pode ser feito é uma estimativa e já atende. Para tanto preciso criar um código VBA, pois é uma tarefa frequente.
Inicialmente fiz como teste numa planilha uma matriz com os dados que tenho das tabelas acima, aplicando fórmulas do Excel, como seria a lógica de rateio, . . .

Onde as colunas que calculam os %, os totais de linhas e de colunas e as que verificam se o total rateado está maior ou menor para checar os totais rateados com os totais originais
A partir da célula G5 ate a célula O32 coloquei a fórmula: =SE(OU(SOMA($F5:F5)>=$D5;SOMA(G$4:G4)>=G$2);0;SE(ARRED(G$2*$E5;0)<=0;1;SE(OU(SOMA($F5:F5)+ARRED(G$2*$E5;0)>$D5;SOMA(G$4:G4)+ARRED(G$2*$E5;0)>G$2);MÍNIMO(G$2-SOMA(G$4:G4);$D5-SOMA($F5:F5));ARRED(G$2*$E5;0))))
O total distribuído nunca pode ser diferente do total original de cada loja, tampouco diferente do total de produtos e usei a função ARRED(), para retornar inteiros
O problema é que como a quantidade de produtos rateados para cada loja tem que ser um nro inteiro, o produto do % de cada loja vezes o total de produtos tem que ser arredondado, com isto em alguns casos não estou conseguindo atingir no rateio o total exato de produtos para algumas lojas e produtos, conforme indicado nas células c/ o texto" menor". Além disso quando a qtde do produto a ratear ou o % de participação de uma loja sobre o total é muito pequeno corre-se o risco de se ter zero unidades rateadas.
Preciso encontrar e transformar a lógica da fórmula correta num código VBA, pois o produto final terá que ser uma nova tabela com os seguintes campos:
[Data ] [Loja] [ Produto] [Qtde ]
Não sei se fui claro o suficiente mas qualquer coisa posso mandar a planilha original para quem puder me ajudar.
Grato e no aguardo de alguem que possa me ajudar