Marco to add Percentage to costs

[ExcelNovice2017]

2018	2019	2020	2021	2020
300		1200		500
	100		23000

<tbody>
</tbody>

Hello All

I'm want to add a interactive button onto my Dashboard:

I have a range of costs in columns from 2018 - 2028 example above with costs for each and some without costs, I'm looking to complete a macro for the form Button. I looking for the macro to add 10% to all costs and ignore the 0's when you press the button on the Dashboard and when you press the button again it removes the 10%, I don't have formulas in the cells and I'm using Excel 2013

This is where I have got to any help finishing it of would be great

Sub Add2Formula()
' Add 10
Selection.style="Percent"

For Each c In Selection
c.Activate
ActiveCell.FormulaR1C1 = "= " & ActiveCell.Formula & "+10"
Next c

End Sub

 

[Joe4]

Try this:

Sub Add2Formula()
' Add 10%
    
    Dim c As Range
    
    Selection.Style = "Percent"

    For Each c In Selection
        If Len(c) > 0 Then
            c.Activate
            c.Value = c + 0.1
        End If
    Next c

End Sub

 

[ExcelNovice2017]

Thank you for quick response, I've ran the code this morning but unfortunately it provided a Runtime Error 13

Sub Add2Formula()
' Add 10%

Dim c As Range

Selection.Style = "Percent"

For Each c In Selection
If Len(c) > 0 Then
c.Activate
c.Value = c + 0.1 ‘Run-time error ‘13’: Type mismatch’
End If
Next c

End Sub

the code looks like it has change the values to percentages and not added 10% to the values aswell


Appreciate your help so far

 

[Joe4]

We actually don't need the "c.activate" line (I meant to remove that).
But the code works just fine for me.

Three things to look for:
- do you have any text entries in your selection (or numbers entered as text - left justification usually is a dead giveaway)?
- do you have any merged cells in your selection?
- do you have any protected cells in your selection?

If you still cannot get this to work out, please tell us the range address of your selection, and post some sample data, so I can try to recreate your scenario on my side.

 

[ExcelNovice2017]

Hi Joe4

Thank you for your response an apologises for my long delay in replying, unfortunately its still not working and I have just got back from holidays

Again Thank you for your help

 

[Joe4]

If you would like further assistance, please answer all the questions I posed in my last reply.