Software

Ask something
Question Locked

Excel Macros

0 helpful
10 replies

by raharr

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to write a macro in Excel that kind of goes like this.

If Cell X = 001, 002 or 003 then Cell XX will be WL, BW or DET.

Anyone have any ideas? I used Case Select and can not get it to fill in Cell XX with what I want.

Thanks
Russ

10 replies

by thumbsup2

Excel does not actually store leading zeros in a numeric field. If you want to search for "001", you have to define the cells as text cells and make sure the zeros are present when you look at the contents of the cell in the formatting line.

by raharr

I have done that, but I can not seem to get it to post what I want in a different cell.

by thumbsup2

Not exactly sure what it is you're saying. Are you saying it won't write the correct thing in cell XX, or it won't write in cell XX at all and writes somewhere else?

You must use the active cell offset, or move the active cell to XX before you can write IN that cell.

Can you give an example of the code you're using and maybe we can decipher it for you? Be specific on what it is you want it to do.

by bizzo

Maybe I'm being a little naive here, but if it's only 3 choices can't you use embedded IF's?

=IF(A1="001","WL",IF(A1="002","BW",IF(A1="003","DET","")))

by thumbsup2

That formula works for me, but ONLY if you type '001 in cell A1. You must type the ' character before the number to force it to be text. If I simply type 001 into cell A1, the formula displays a blank, which is what it SHOULD do. And, that's what I was trying to tell you. Excel will NOT store the leading zeros in the cell unless you type ' first before the number.

By the way. That is not a MACRO. That is a FORMULA. The two are completely different.

by bizzo

I wasn't clear in my post, I meant to say that seeing as it's only 3 choices, is it necessary to use a macro, why not a formula.

by thumbsup2

Didn't see it was you replying to OP. Thought it was him showing what he had done already, so I corrected "him"....

Thummy had not had enough coffee yet! ;)

by raharr

Got it! Thanks!

You were correct. I used a formula and not a macro.

Russ

by bizzo

No problem.

Remember: Coffee first, then keyboard!

This discussion is closed to new replies.