VBA to put amounts in columns based on Code in column C.

[mikeburg]

Need sheet VBA that when a code is put in column C, the amount beside it in column B is put in a column D thru U of the same row based on the code on row 4 of each column.

In other words, when the code is put in column C, VBA puts the amount in the same row, column B in the same column whose code is in row 4, for example:

_|__B__|__C_|__D_|__E__|__F__|__G__|__H__|__I__|__J__|__K__|, etc
4|_Amt_|Code|611_|_648_|_694_|_851_|_862_|_884_|_885_|_886_|, etc
5|22.01 862_________________________22.01
6|23.32 851___________________23.32
7|-4.88 611_-4.88
8|66.38 885_____________________________________66.38
9|-4.50 694______________-4.50
etc

Thank you for all your help. mikeburg

[mikerickson]

If you put this in the code module for the sheet in question, it should do what you want. Changing a cell in either column B or C will cause the value in B to be put in the column that matches the column C entry. It does nothing if the entry in C has no match in D4:U4.

If you want to auto-adjust when the headers (D4:U4) are changed, remove the marked Exit Sub line midway through the code.

Please Login or Register  to view this content.

[Leith Ross]

Hello mikeburg,

Here is another version that automatically finds the last code column, and updates the appropriate cell. This would be placed in the Worksheet_Change event code procedure for the worksheet that has the table.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[mikeburg]

Thanks a million. How would you modify it to give a message box when the code could not be found (when entered incorrectly) & remain in the cell where the code was incorrectly entered?

mikeburg

[mikerickson]

Please Login or Register  to view this content.

[mikeburg]

With the modification, I get message "Invalid Code" when enter the amount in column B. Sorry, I can't figure out how to fix it where it checks for invalid code in column C.

Can you help? mikeburg

[mikerickson]

This code will show the message box when the columnC entry is not in D4:U4. The code will run when an entry in either column B or C is changed. Changing column B with a bad entry in column C will trigger the message box.

This has been modified to not alert when columnC is empty.

Please Login or Register  to view this content.