Having just spent almost an hour beating my head against my phone reading through this posts, I wanted to add some additional info.
I have HTC Hero, fresh from Sprint, right out of the box.
I followed Simon's instructions, with an AUDIO folder, and with a RINGTONE folder, and neither worked. So I downloaded Ringdroid to save a file, and it butchered my mp3. It took a 15 second clip, and compressed it into garbage sounds. It's no uninstalled.
But because I'd saved a folder, I had new folders on my sd card. I was then able to create my own notification/alarm folders, and now am merrily loading my custom sounds.
So, on the root sd, create a folder marked media. (Not sure if it's case sensitive, didn't play with it that much. I've just left mine all in lower case.) In media goes audio, and in that are alarms, notifications, and ringtones.
Now that it's that easy for me, I thought I would add my two cents for others having issues.