Always room for improvement

[swaink]

Hi All

I have reached a small hitch with using VBA to carry out a calulation.

In Cell D8 I have a date field and anchor my vba on that cell and use offset to achieve what I am aiming for.

All works well except for a percentage calulation when I divide cell D10 by D9 and place the value in cell D14 ("ActiveCell.Offset(6, 0).Value")

This is the line I use to do the calulation and I expect to see a percentage value such as 98.3 % but the end result shows as 9.893129770992370.00% and I can't see why

ActiveCell.Offset(6, 0).Value = (ActiveCell.Offset(2, 0).Value / ActiveCell.Offset(1, 0).Value) * 10 & Format("0.0", "Percent")

All advice appreciated

Kevin

 

[Scott Huish]

First it is not generally necessary to Select or Activate cells you can work with ranges directly in VBA.

I'm assuming that in D9 and D10 you have values like 98.3, which you then want to show as a percent:

Sub test()
With Range("D14")
    .NumberFormat = "0.00%"
    .Value = (Range("D10") / Range("D9")) / 100
End With
End Sub

 

[swaink]

Hi Hotpepper

Thanks for the quick responce and it certainly looks cleaner than my approach.

I have applied this but the result is now showing as 0.989% is there a way to make this look like 98.9% or am I doing something wrong

Kev

 

[Scott Huish]

Take out the / 100

 

[swaink]

Thats cracked it Hotpepper

Many thanks for your time I do appreciate it and I have learned something new in the process

Kevin