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:
<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:
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!