How to create a Button to convert a range of cells in Excel?

[chojin]

Hi everyone!

I am new to this forum and also to Excel's Macros (VBA) itself, but really do need help so any input would be much appreciated.

I am in the process of creating a spreadsheet that will assist me with my work.

Within the spreadsheet I have a range of cells, for example: "E3:E100" that contain figures such as "100.00D, 50.00C" and so on. These are "Credits(positive)" and "Debits(negative)". As you will all be aware, by having 'Alphanumeric' characters within the cells the figures within the column will not calculate. I want to create a button on the spreadsheet that will once clicked remove the letter from the end of the cells and add a "-" to the left of the negative values (that initially contained the 'D' at the end).

Thank you in advance!

[ptmuldoon]

This will put the Debit and Credit into the columns next to it.

Please Login or Register  to view this content.

[mikerickson]

You could format the cells with the custom format #.00"C";#.00"D" and then the D/C will show, but the underlying value will be numeric. No conversion will be needed to do arithmetic.

[chojin]

This will put the Debit and Credit into the columns next to it.

Please Login or Register  to view this content.

Thank you for the prompt response guys! PTMuldoon. The function successfully converts the numbers and places them in the column to the right however, the negative values are showing as positive! How would I remedy this and is there any way to have the function change the figures in the existing cell instead of putting the new values in the cell next to it.

[chojin]

You could format the cells with the custom format #.00"C";#.00"D" and then the D/C will show, but the underlying value will be numeric. No conversion will be needed to do arithmetic.

Hi Mikerickson. Thank you also for your reply. I have tried entering the format that you suggested and there doesnt seem to be any change! Any ideas what I might be doing wrong?

[FDibbins]

Hi, welcome to the forum

As you will all be aware, by having 'Alphanumeric' characters within the cells the figures within the column will not calculate.

Not strictly true,, check the table below...

	A	B
1	10d	1110
2	100d	2220
3	1000d
4	20c
5	200c
6	2000c

B2=SUMPRODUCT(--(LEFT($A$1:$A$6,LEN($A$1:$A$6)-1)),--(RIGHT($A$1:$A$6,1)="d"))
C2=SUMPRODUCT(--(LEFT($A$1:$A$6,LEN($A$1:$A$6)-1)),--(RIGHT($A$1:$A$6,1)="c"))

[ptmuldoon]

I often like to keep the original data to compare to. but If you want to continue with the Macro route, this is the same code from above just modified to convert it the same column range.

Please Login or Register  to view this content.

[mikerickson]

Hi Mikerickson. Thank you also for your reply. I have tried entering the format that you suggested and there doesnt seem to be any change! Any ideas what I might be doing wrong?

Once you've applied that format, put 23 in a cell (don't type a D or a C), it should show 23.00C

Then put -134 in the cell, it should show 134.00D.


The reason that the format change did nothing is that the format only effects numbers, your cells currently hold strings.
I'm suggesting a completed conversion from entering strings (i.e. with the D or C) to entering numbers and having Excel show the D or C, but not have it part of the cell's value.