VBA Using IF statement to compare values

[elmako21]

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:

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!

 

[AlphaFrog]

[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]

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]

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.