Warning: Spoilers/references through the end of the series!
One hardly has to see more than the first quarter of Fullmetal Alchemist before encountering that infamous declaration of a certain Flame Alchemist's uselessness in the rain. However aside from providing some humor at the colonel's expense, this little remark also makes an interesting analogy for Roy's behavior and personality.
The alchemic trick that Roy specializations in and is known for is that of creating and manipulating fire, and in his hands it is a very potent one. Even without a stone to enheance his alchemy he can, and does, subdue an entire mob with a single strike. However the moment the sky darkens and rain begins to fall, it takes mere moments before this is all rendered null.
Roy's alchemy reflects his personality. He is ambitious and relentless, a force to be reckoned with. As long as he has something to aim for, a goal or ambition, Roy is powerful, and nearly unstoppable. But the minute he begins to doubt himself, it all falls to ruin.
Take the situation at Ishbal. Starting when he was first ordered to kill, Roy began to doubt and even despise himself. Each act of bringing about death and destruction compounded this, like swiftly gathering stormclouds, and the fallout of it all simply incapacitated him. So severe was this blow that he even sought the out of his own death.
And this persisted. Roy was unable to function until finally, through the aid of a dear friend, he discovered a goal he could strive for. As soon as he was pulled out of that into the daylight, he was immediately able to take charge and begin making things move. Once he had a sense of ambition, he was able to work single-mindedly to acheive his goal.
But even so, rain still occasionally clouded his skies. The death of that same friend who had once been of so much helped forced Roy to question his means and motives. For him, it truly was raining on that bright and sorrowful day.
This time, however, he has others to keep him from sinking to far, and with renewed ambition we see just how much he is capable of. No where is this clearer than at the end, when he finally shakes all doubt from his mind and sets out upon a daring and dangerous plan. He is a force to be reckoned with, beating the Fuhrer at his own political game and able to set up a coup that outwits even the one pulling the strings. As long as he had something to strive for, as long as he was free of self-doubt, clear skies forcasted his triumph.
Most people certainly can, perhaps even constantly need to, deal with a certain level of self-doubt or lack of ambition. For Roy, however, a man whose personality relies on the ability to be perfectly confident in himself, even a small amount runs the risk of being detrimental. Even beginning to doubt himself can quickly lead to being rendered incapable of any and all action.
And so, while most people would merely ignore or endure the rain, Roy is made useless by it.