Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External data Macro Problem Excel 97 | Excel Discussion (Misc queries) | |||
Problem executing a macro from different workbook where it is | Excel Discussion (Misc queries) | |||
URGENT Mac/PC macro compatibility problem | Excel Discussion (Misc queries) | |||
Macro Problem | Excel Discussion (Misc queries) | |||
Import chart to Power Point and Macro problem | Excel Discussion (Misc queries) |