One way would be to simply use a formula in cell D22 like this:
=SUMIF($C$2:$C$20,C22,$D$2:$D$20)
Then copy that formula down next to all the unique names below it.

You could take this formula approach a step further by creating the
formulas through VBA:
for x = 22 to 100
if range("C" & x).value < "" then
range("D" & x).FormulaR1C1 =
"=SUMIF(R2C3:R20C3,RC[-1],R2C4:R20C4)"
end if
next x

Or you could take your For... Each approach by just wrapping it in
another loop:
for each cn2 in myRange4 '(this range would be all your unique names in
C22 to C42)
tally = 0
if cn2 < "" then
'here is your code a little modified
for each cn in myRange3
if cn = cn2 then
qty = cn.offset(0,1)
tally = tally + qty
end if
next cn
range("D" & cn2.row).value = tally
end if
next cn2