If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Advanced cell function. is it possible
got ot wrong in the end it should be A1+1000 qhen A1=B1. Try this
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Range("A1:b1")) Is Nothing Then Exit Sub Else x = Worksheets("Sheet1").Cells(1, 1).Value y = Worksheets("Sheet1").Cells(1, 2).Value If x = y Then Worksheets("Sheet1").Cells(1, 3).Value = x + 1000 End If End If End Sub "Jop Duyvendak" wrote: Tosco, Can you reconsider your example? I'm confused about the last line of your example: A1 = 300, B1=500 and C1 = 1500 . Shouldn't that be C1=1300??? And then, how do you want this to work. I'm unclear what you mean with 'the last time they were equal..." Are A1 and B1 over time steadily rising in value and you want C1 to rise with it in a step-wise manner? So the sheet should somehow remember what the previous values were? "Tosco" wrote: Hi all, I`m having a problem on excel and I would like to know if it`s possible to be done without programming in VB: Let's say i have the following: A B C 1 100 100 1100 The formula should be on C1, and it should evaluate to: Everytime cells A1 and B1 have the same value, C1 = A1 + 1000 If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes to 200 also, when C1 should change to 1200. i'll try to do some simulation to make it clear: A B C 1 100 100 1100 A B C 1 200 100 1100 A B C 1 300 100 1100 A B C 1 300 300 1300 A B C 1 300 500 1500 Or, in other words, C1 should be equal to 1000 + the value of A1 or B1 on the last time they were equal. Thanks in advance. |
#12
|
|||
|
|||
Advanced cell function. is it possible
Mike,
You're right. The last line of my sample was worng. The correct value should be 1300. A1 and B1 are rising over the tima, and i want C1 to rise together the way i've shown in my sample. thanks for your VB solution, but maybe i wasen't clear in my question, as there are going to be more than a hundred rows that should behave the same way, and as the worksheet is feed with financial market data, and change a lot, a don't think the solution using the change event should be the best for me. I was thinking in develop a function in VB and use it. don't you think this way i would have a better performance? Considering just the performance and that i'm going to have more than a hundred of this calculation on my worksheet, what should be the best solution? Circular reference, the change event of the worksheet or a worksheet function developed in vb? Thanks again. On Jan 26, 6:42 pm, Jop Duyvendak ) wrote: Tosco, Can you reconsider your example? I'm confused about the last line of your example: A1 = 300, B1=500 and C1 = 1500 . Shouldn't that be C1=1300??? And then, how do you want this to work. I'm unclear what you mean with 'the last time they were equal..." Are A1 and B1 over time steadily rising in value and you want C1 to rise with it in a step-wise manner? So the sheet should somehow remember what the previous values were? "Tosco" wrote: Hi all, I`m having a problem on excel and I would like to know if it`s possible to be done without programming in VB: Let's say i have the following: A B C 1 100 100 1100 The formula should be on C1, and it should evaluate to: Everytime cells A1 and B1 have the same value, C1 = A1 + 1000 If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes to 200 also, when C1 should change to 1200. i'll try to do some simulation to make it clear: A B C 1 100 100 1100 A B C 1 200 100 1100 A B C 1 300 100 1100 A B C 1 300 300 1300 A B C 1 300 500 1500 Or, in other words, C1 should be equal to 1000 + the value of A1 or B1 on the last time they were equal. Thanks in advance.- Hide quoted text -- Show quoted text - |
|
Thread Tools | |
Display Modes | |
|
|