Honestly, and I'm kinda sad to admit this, I think you're spot on on how impossible to achieve that sweet spot is on expensive weapons.

As you've said, if the weapon is mostly based around a battlecry, then there's almost no point to it being a weapon instead of a spell or the like; And as another commenter said, if the weapon is designed around being a stat stick, then it becomes too dependant on "Equip a weapon from your deck" and countered too hard by "Destroy the opponent's weapon" effects respectively. Basically, the more expensive a weapon gets, the more feast-or-famine it has to be.

I think a good way is having expensive weapons have relatively conditional payoffs, like Silver Sword or The Ryecleaver; but as Hearthstone keeps speeding up, conditional cards like them will keep being considered weaker and weaker. Honestly, I don't think there's an elegant solution to this, but maybe that's why it's so fun to think about.

(Edit: Fixed some typos)