If the target spell is an illegal target when Mana Sculpt tries to resolve, it won't resolve and none of its effects will happen. You won't add mana at the beginning of your next main phase. If the target is legal but not countered (probably because an effect says that spell can't be countered) and you control a Wizard, you'll still add mana.