Circular Problem needs Macro
 

Hi All

I have a problem which I think requires a macro to paste in a result as
values. The problem is I have seven years of data in Column A. Year One is
$95 and each year onwards is indexed at 4% per annum (Yr2 = 99, Yr3 = 103,
Yr4 = 107, Yr5 = 111, Yr6 = 116 and Yr7 = 120). In Column B I have some
another set of hard coded numbers (100, 110, 120, 120, 130, 157 and 160)
which align to Years 1 to 7.

Then the issue is if in any year Column B is =115% to Column A then the
Column B number should replace the Column A number and in the following year
this replacement number should be indexed. Therefore, Year 1 is OK, Year 2
is OK but in Year 3 we have a breach, therefore 120 should replace 103.
Therefore Year 4 Column A changes to 125 but is OK, Year 5 is OK, Year 6 has
another breach, therefore 157 should replace 135. Then Year 7 is OK.

To me I need a macro that picks up when Column B is =15% to Column A and
paste Column B value into Column A. Does anyone have any thoughts? I can
email you the example if needs be.

Thanks in advance!
Nick

Dear Nick
the following macro would do the thing you want.
plz check that all the cells in column A & B you want to run macro on
are numbers. Then in macro code replace the "2" with the start row no.
& "8" with end row no.
& run macro.
Note:-Always take back up copy before running macro.Try this macro
first on sample data
following macro was based on your sample data in question.

Sub Temp()
Dim i As Integer

For i = 2 To 8 'you are supposed to replace this "2" & "8" only

If Cells(i, 2).Value = Cells(i, 1).Value * 1.15 Then
Cells(i, 1).Value = Cells(i, 2).Value
End If

Next i
End Sub

Regards
NC

Nick,

Doesn't this work for cells A1 down

=IF(B2(A1+4)*115%,B2,A1+4)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NICK" wrote in message
...
Hi All

I have a problem which I think requires a macro to paste in a result as
values. The problem is I have seven years of data in Column A. Year One
is
$95 and each year onwards is indexed at 4% per annum (Yr2 = 99, Yr3 = 103,
Yr4 = 107, Yr5 = 111, Yr6 = 116 and Yr7 = 120). In Column B I have some
another set of hard coded numbers (100, 110, 120, 120, 130, 157 and 160)
which align to Years 1 to 7.

Then the issue is if in any year Column B is =115% to Column A then the
Column B number should replace the Column A number and in the following
year
this replacement number should be indexed. Therefore, Year 1 is OK, Year
2
is OK but in Year 3 we have a breach, therefore 120 should replace 103.
Therefore Year 4 Column A changes to 125 but is OK, Year 5 is OK, Year 6
has
another breach, therefore 157 should replace 135. Then Year 7 is OK.

To me I need a macro that picks up when Column B is =15% to Column A and
paste Column B value into Column A. Does anyone have any thoughts? I can
email you the example if needs be.

Thanks in advance!
Nick