VBA Using IF statement to compare values

elmako21

New Member
Joined
Apr 3, 2018
Messages
2
Hi all! First time poster here and a complete VBA newbie. I'm doing a unit which requires some VBA coding and here is one of the questions I need help on:

Using the data for the Big 4 Australian banks (retrieved from Yahoo Finance) your task is to identify the best and worst performing stock each month
To do this copy the returns from the white cells to the blue cells subject to the following conditions:
(1) Replace the highest monthly return each month with the word "Winner"
(2) Replace the lowest monthly return each month with the word "Loser"

<colgroup><col width="64" style="width:48pt" span="15"> </colgroup><tbody>
</tbody>

There are 4 columns and multiple rows, and I need to compare the value in each column to replace the values with either Winner or Loser or the original value if it's neither of the two.

Here's my code so far, but my output is coming out with all "Winner" and I'm not sure as to why:

Code:
Sub compare()

Dim norows As Integer
Dim nocols As Integer

norows = Range("stock_returns").Rows.Count
nocols = Range("stock_returns").Columns.Count

For i = 1 To norows
[INDENT]For j = 1 To nocols[/INDENT]
[INDENT]    If Range("stock_returns").Cells(i, 1) > Range("stock_returns").Cells(i, 2) And Range("stock_returns").Cells(i, 1) > Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 1) > Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Winner"[/INDENT]
    
[INDENT]    ElseIf Range("stock_returns").Cells(i, 2) > Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 2) > Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 2) > Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Winner"[/INDENT]
    
[INDENT]    ElseIf Range("stock_returns").Cells(i, 3) > Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 3) > Range("stock_returns").Cells(i, 2) And Range("stock_returns").Cells(i, 3) > Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Winner"[/INDENT]
        
[INDENT]    ElseIf Range("stock_returns").Cells(i, 4) > Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 4) > Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 4) > Range("stock_returns").Cells(i, 2) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Winner"[/INDENT]
    
[INDENT]    ElseIf Range("stock_returns").Cells(i, 4) < Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 4) < Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 4) < Range("stock_returns").Cells(i, 2) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Loser"[/INDENT]
        
[INDENT]    ElseIf Range("stock_returns").Cells(i, 1) < Range("stock_returns").Cells(i, 2) And Range("stock_returns").Cells(i, 1) < Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 1) < Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Loser"[/INDENT]
    
[INDENT]    ElseIf Range("stock_returns").Cells(i, 2) < Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 2) < Range("stock_returns").Cells(i, 3) And Range("stock_returns").Cells(i, 2) < Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Loser"[/INDENT]
    
[INDENT]    ElseIf Range("stock_returns").Cells(i, 3) < Range("stock_returns").Cells(i, 1) And Range("stock_returns").Cells(i, 3) < Range("stock_returns").Cells(i, 2) And Range("stock_returns").Cells(i, 3) < Range("stock_returns").Cells(i, 4) Then[/INDENT]
    
[INDENT]        Range("output_stock").Cells(i, j) = "Loser"[/INDENT]
        
[INDENT]    Else[/INDENT]
[INDENT=2]
Range("output_stock").Cells(i, j) = Range("stock_returns").Cells(i, j)[/INDENT]
    
[INDENT]    End If[/INDENT]
    
[INDENT]Next j[/INDENT]
Next I

End Sub

Any help would be much appreciated, especially any criticisms!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Code:
[color=darkblue]Sub[/color] compare()
    
    [color=darkblue]Dim[/color] SRRow [color=darkblue]As[/color] Range
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] SRRow [color=darkblue]In[/color] Range("stock_returns").Rows           [color=green]'loop through each row[/color]
    
        SRRow.Replace Application.Max(SRRow), "Winner"      [color=green]'Replace the max value in the row with 'Winner'[/color]
        SRRow.Replace Application.Min(SRRow), "Loser"       [color=green]'Replace the min value in the row with 'Loser'[/color]
        
    [color=darkblue]Next[/color] SRRow
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

elmako21

New Member
Joined
Apr 3, 2018
Messages
2
Thanks @AlphaFrog for your reply. But is there way to incorporate the IF statement into the code to make the output winner, loser and original value for the highest, lowest and original value respectively per row?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Did my code produce the correct results? If not, please explain.

Can I ask; 1) why do you want to use an IF statement, and 2) I don't understand your original IF statement.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top