I didn't say they were useless, merely wasteful.
For what they're designed to do the Space Marines are highly effective, the problem is that making them is insanely expensive. If you have one that's great but if it dies then it's almost genuinely irreplaceable and that's kind of a big problem. Also it's worth noting that what they're designed to do and what the tabletop/rts games like dawn of war kind of implies they do are different things. Flavour/lore wise Space Marines are the mono-molecular tip of the spear, and they're meant to be used in that way. If it's gotten to the point that you have 500 Marines in tanks in a pitched battle then someone has actually fucked up, concentrating that kind of force in that small of a space is meant to be a) beyond necessary and b) quite risky because if something hits one of them it might hit all of them and then you've lost 500 at once. They're arguably at their most useful when they internally police breakaway states/rebellions inside the Imperium, because there they can just show up at a place with all the infrastructure already set, decapitate the leadership and install the new Imperial governor, and then leave again without wrecking the manufactorium or whatever makes the planet important.