Macro to Add Message Based on Cell Value

[spacle]

Hi! I need a macro that will go to the first empty column in a spreadsheet, and then name the cell in Row 1 "Full Comment" and then in that column, row 2, look at the amount in column AC (and in some cases compare it to that in AD) and then plug in a message based on that amount. Once it has put the message there, I need it to copy and paste special down the column so that only the value is left in the cell (removing the formula).

Here is what I used to use (Sub AddGiftMessage_6) which worked fine for a long time. Now however there are more parameters and messages required and I am trying to use the code in the second code section (Sub NewGiftPoss) below (based on a previous post) but I can't make it work plus it isn't doing everything I need it to (copying the formula down, paste special to overwrite the formula, etc.).

Any help you could give me would be greatly appreciated.

Thanks in advance!
Spacle

P.S. I had to add a space in every "select" because of the Sucuri firewall issue.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Pauleyb]

First, the case statement. As soon as a true case statement occurs, it runs the code until it hits the next Case statement and then exits the entire select case block of code. So, since your first case statement is if it is > 1, then that is likely true for most of your line items, so it then says $5 per pay period. Maybe you meant < 1? I also don't see how 'Is < Range("AD2").Value" is supposed to work. For example, what if AD2 is 400 and giftValue is 300? In the statement above, the first, third, and sixth case statements are true. It is not clear which one you would want to work. As it stands, excel will only perform the first one.

Second, you are using AC2 in your text string. Use giftValue instead since you already have the value of AC2 in that variable. You are also using AD2 out of context, and you need to use "Range("AD2")" to properly reference that cell in a macro.

Third, TEXT is not a VBA function (it's an excel spreadsheet function). The pseudo-equivalent in VBA is FORMAT.

Fourth, it is not copying and pasting since you don't have that code in it. In your top code, you have the Selection.Copy and Selection.PasteSpecial commands which are not present in your bottom code.

Fifth, I would recommend steering away from VBA for this case statement and just use some nested IF statements in your cells. It won't be pretty with all of that text, but it may be simpler. I'd recommend using a cheater column to determine the amount, and then use that result in your message text. Something like (for the amount):
=if(AC2<1,5,if(AC2<900,AC2*1.1,if(AC2<980,1000,AD2)))
Note that the above does not handle your 'Is < Range("AD2").Value' statement, since it is not clear how that is supposed to work.

[spacle]

Thanks very much for the reply, Pauleyb!

I am now trying to modify my original code with the nested IFs as you suggested but am still getting an error. Any ideas why it isn't working would be greatly appreciated.

Thanks!
Spacle

Please Login or Register  to view this content.