Need to increase the percentage in a cell containing the original value

[jrub]

I am trying to write a code in vba to change a target cell by a user defined percentage which already contains a value, basically yearly inflat

As an example.

If G4 contains a value of 100 and Start!$C$15 contains the 3% increase, i want the contents of cell G4 to increase to the value of 6%, so multiply by 1.06. Also I then wish to repeat this down from G4 to as many cells as required all using G4 as the target % increase. Not all cells contain data. The adjacent column would then increase a multiplier of the year difference associated with the column, BUILDING SUMMARY'!G$3-'BUILDING SUMMARY'$F$3, giving rise to 1, 2, 3, etc. by column. The multiplier would then be 1*6%=1.06, 2*6%=1.12, 3*6%=1.18, etc.

Any help or suggestions, or even someone who can write the VBA code and post would be most appreciated.

[jrub]

The code Im trying is:
[VBA]
Range("Start!$C$15").Value = ((Range("Start!$C$15").Value / 100) * ("=BUILDING SUMMARY'!G$3" - "BUILDING SUMMARY'!$F$3")) + 1
Range("Start!$C$15").Copy
Range("$G4", Cells(Rows.Count, "G").End(xlUp)).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlMultiply
Range("Start!$C$15").Value = (Range("Start!$C$15").Value - 1) * 100[/VBA]

But I keep getting errors, any help guys?

[Teeroy]

Hi jrub,

Your description of what you want is a bit convoluted and I'm not sure it's mathematically correct. If you are assessing inflation over years you shouldn't be multiplying by n years it should be to the power of n.

For your info I wouldn't have even used VBA for this problem I would have done it with worksheet formulas.

The problem with your referencing is that you are using the style from within the worksheet in VBA where it is different. The following will do what your example intended to help you with your coding.

Good luck with the project.



[vba]
Sub test()
With Sheets("Start")
.Range("C15").Value = ((.Range("C15").Value / 100) _
* (Sheets("BUILDING SUMMARY").Range("G3").Value - Sheets("BUILDING SUMMARY").Range("F3").Value)) + 1
.Range("C15").Copy
.Range("G4", Cells(Rows.Count, "G").End(xlUp)).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlMultiply
.Range("C15").Value = (.Range("C15").Value - 1) * 100
End With
End Sub[/vba]