Solved Excel 2010 if function

March 5, 2013 at 08:46:16
Specs: Windows 7
I,m trying to create a function to do the following --

IF CELL A2 IS LESS THAN C2 DO NOTHING
IF CELL A2 IS MORE THAN C2 THEN REPLACE C2 WITH A2

Any solution much appreciated.
Thanks
Bryan


See More: Excel 2010 if function

Report •


✔ Best Answer
March 10, 2013 at 11:18:11
Since you are already using macros in the workbook, let's add one more.

VBA has a feature known as Event Macros which fire when a specific event occurs. One such Event is the Calculate event, which will fire whenever a sheet recalculates.

Each time the sheet calculates, this code will compare the values in A2 and C2 and replace the value in C2 with the value in A2 if the value in A2 is greater than the value in C2:

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    If Range("A2") > Range("C2") Then Range("C2") = Range("A2")
  Application.EnableEvents = True
End Sub

Another option is the Worksheet_Change event, which will monitor changes in a worksheet and run the instructions when a change is made to a specific range.

The following code will compare A2 with C2 when A2, and only A2, is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
   If Target.Address = "$A$2" Then
     If Target > Range("C2") Then Range("C2") = Target
   End If
 Application.EnableEvents = True
End Sub

The following code will compare A2 with C2 when any range that includes A2 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Range("A2") > Range("C2") Then Range("C2") = Range("A2")
  End If
 Application.EnableEvents = True
End Sub

Note: All of the above suggestions include these 2 lines:

 Application.EnableEvents = False
 Application.EnableEvents = True

The reason this is done is so that the changes made by the macro don't cause the code(s) to fire again. e.g. When the Worksheet_Change event changes C2, that is seen as a change and the code will sort of loop back and run again. It's not a big deal in this case, but its just more efficient to not let that happen.

However, and this is a big however, if the code throws up an error and stops before the Application.EnableEvents = True line, Events will be disabled and no Event code will work until Events are enabled again. This can sometimes be confusing when you are testing Event code and something fails. Unless you are aware that events have been disabled, you might be wondering why none of your Event code works.

A simple fix is to simply keep this macro handy and to run it to ensure that Events are enabled:


Sub EnableMyEvents()
Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 5, 2013 at 14:02:47
Please don't post in all caps. All caps is the Internet equivalent of yelling, and no one likes to be yelled at.

I'm not sure what you are asking for when you say "replace C2 with A2".

Do you want this formula to be in C2 so that it sets C2 equal to either nothing (blank) or A2, or does C2 already contain something that you want to change?

You also don't say what you want to happen if C2 = A2.

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 9, 2013 at 09:19:38
Sorry about the caps.Not aware of the correct internet protocol.

I have stock price changing daily in cell A2, Cell C2 contains highest achieved price over time. If the price in A2 is less than or equal C2 then C2 remains as previous. If the stock price in A2 exceeds previous high in C2 then the new high price goes into C2. I hope this clarifies what I am trying to achieve

Many thanks for your response
Bryan


Report •

#3
March 9, 2013 at 12:33:13
How is the stock price changing "daily" in A2? Is it via manual entry, or via a download, or via a macro or...?

BTW...a daily price change might not indicate the highest price achieved over time. It might indicate the highest closing price, or the highest opening price, or the highest price at whatever time the price is checked, but unless you are monitoring every trade, you could easily miss the highest intraday trading price.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 10, 2013 at 08:39:53
I have a macro that downloads a large amount of data fron Yahoo for a list of stocks on that page (sheet 1). On sheet 2 I then put the data into a form that I want and perform various calculations.
The worksheet I'm attempting to make (sheet 3) is to calculate stop/losses. A2 would =sheet1!D5 for latest price for example. C2 would be pushed up as the price rises in A2 so always showing the highest price achieved I can get day Hi/Lo and 52 week Hi/Lo from Yahoo which one can use but would prefer make reference to purchase price for further calculations
Many thanks
Bryan

Report •

#5
March 10, 2013 at 11:18:11
✔ Best Answer
Since you are already using macros in the workbook, let's add one more.

VBA has a feature known as Event Macros which fire when a specific event occurs. One such Event is the Calculate event, which will fire whenever a sheet recalculates.

Each time the sheet calculates, this code will compare the values in A2 and C2 and replace the value in C2 with the value in A2 if the value in A2 is greater than the value in C2:

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    If Range("A2") > Range("C2") Then Range("C2") = Range("A2")
  Application.EnableEvents = True
End Sub

Another option is the Worksheet_Change event, which will monitor changes in a worksheet and run the instructions when a change is made to a specific range.

The following code will compare A2 with C2 when A2, and only A2, is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
   If Target.Address = "$A$2" Then
     If Target > Range("C2") Then Range("C2") = Target
   End If
 Application.EnableEvents = True
End Sub

The following code will compare A2 with C2 when any range that includes A2 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Range("A2") > Range("C2") Then Range("C2") = Range("A2")
  End If
 Application.EnableEvents = True
End Sub

Note: All of the above suggestions include these 2 lines:

 Application.EnableEvents = False
 Application.EnableEvents = True

The reason this is done is so that the changes made by the macro don't cause the code(s) to fire again. e.g. When the Worksheet_Change event changes C2, that is seen as a change and the code will sort of loop back and run again. It's not a big deal in this case, but its just more efficient to not let that happen.

However, and this is a big however, if the code throws up an error and stops before the Application.EnableEvents = True line, Events will be disabled and no Event code will work until Events are enabled again. This can sometimes be confusing when you are testing Event code and something fails. Unless you are aware that events have been disabled, you might be wondering why none of your Event code works.

A simple fix is to simply keep this macro handy and to run it to ensure that Events are enabled:


Sub EnableMyEvents()
Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 12, 2013 at 05:48:59
Many thanks for your excellent detailed solution
I created a blank new workbook to try the routines out. If i run the routine from the VB editor that is good. If I recalculate (F9) there is no change, even if I run the enableevents macro before recalculate.
I am very happy with the solution. i am not a programmer but usually make macros by recording and then modifying. When I get stuck I have a useful book on Excel that i refer too , this way I am slowly learning..Your solution got me off first base and pointed me in the right direction , from here I'm confident I can reach the desired result.
Again, Many thanks.
Bryan

Report •

#7
March 12, 2013 at 08:57:28
Since the code I suggested is based on a "sheet level event" the Calculate Event code has to be placed in the VBA module for the sheet where the desired calculation will occur.

If you place the code in the sheet module for Sheet2 and perform a calculation on Sheet1, the code will not fire. If you need to monitor a calcualtion on a sheet other then the sheet where you want to change to occur, then you need to refer to the other sheet in the code.

For example, if you are doing a calculation in Sheet1 but want the code to affect Sheet2, you would use something like this in Sheet1:

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    If Sheets(2).Range("A2") > Sheets(2).Range("C2") Then _
       Sheets(2).Range("C2") = Sheets(2).Range("A2")
  Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 14, 2013 at 14:20:39
That was the problem. I've now got a working version of my spreadsheet and I've learned some more about excel programming. Thanks
Bryan

Report •


Ask Question