Circular Problem needs Macro

[Guest]

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

 

[NC]

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

 

[Bob Phillips]

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)