multiple condition if statement

[scrib3]

hi

i have been given 3 conditions to apply to 6 stocks in an array(10,6) with their returns for 10 years. the first being, if there are 3 other negative returns for the same year and that year happens to be negative then return "message1" in a corresponding array(10,6). and any other positive returns for that year should return "message2". But if there are less than 3 other negative returns in that year across all 6 stocks and the particular stock is less than -2% than it should return "message3". and if there are less than 3 other negative returns in that year across all 6 stocks and the particular stock is greater than 5% than it should return "message4"

my attempt:
Dim arraytwo() As Double

ReDim arraytwo(noRows, noCols) As Double

For i = 1 To noRows
For j = 1 To noCols
arraytwo(i, j) = Range("input").Cells(i, j)
If arraytwo(i, 1) < 0 Then
d_one = d_one + 1
End If

If arraytwo(i, 1) < 0 And d_one > 3 Then
Range("output").Cells(i, 1) = "message1"
ElseIf arraytwo(i, 1) > 0 And d_one > 3 Then
Range("output").Cells(i, 1) = "message2"
ElseIf arraytwo(i, 1) < -0.02 Then
Range("output").Cells(i, 1) = "message3"
ElseIf arraytwo(i, 1) > 0.05 Then
Range("output").Cells(i, 1) = "message4"
End If
any help would be much appreciated

[SamT]

if there are 3 other negative returns for the same year and that year happens to be negative then return "message1" in a corresponding array(10,6). and any other positive returns for that year should return "message2". But if there are less than 3 other negative returns in that year across all 6 stocks and the particular stock is less than -2% than it should return "message3". and if there are less than 3 other negative returns in that year across all 6 stocks and the particular stock is greater than 5% than it should return "message4"

First, you need to convert the conditions into PseudoCode
''''Condition 1
If Sum Stocks < 0 AND Count(Stock < 0) > N then
If Stock < 0 Then msg1
Else Msg2

''''Condition 2
IF Stock < -2% Then Msg3
ElseIf Stock > 5% Then Msb4
EndIf


Next, you should use comments to note the proposed Program Flow. I prefer to use four comment marks ('''') to make Program Flow statements stand out. You can, (should?) combine this step with the previous in more complex programs.
''''Declare Variables 'Obvious, But I like it in this step

''''Load StockByYear Array

''''Check Conditions and Assign Messages

''''Etc, etc, etc.



Arguably, those are the most important steps in Code development.

The most important part of all code is its Documentation, for how else are we supposed to maintain it?

Self Documenting Code:
In order to make you code more intuitive as to what it' doing, give all your variables meaningful names.

Dim StockReports()_ As Variant
Redim StockReports(10,6)
Dim + Redim Messages As Variant
Dim YearNum and StockNum As Long
Dim and Let Messages 1, thru 4.

'Then
For YearNum 1 to 10 and StockNum 1 to 6

[sassora]

The for loops need to be closed with next j and next i

Also when calculated your d_one variable I think using arraytwo(i,j) rahter than arraytwo(i,1) is what you want. i.e. you want to sum over stocks.

Take a look at this:
Sub scrib3()

Dim arraytwo() As Double
Dim noRows As Long, noCols As Long
Dim i As Long, j As Long
Dim d_one As Long

noRows = 10
noCols = 6

ReDim arraytwo(noRows, noCols) As Double

For i = 1 To noRows
d_one = 0
For j = 1 To noCols
arraytwo(i, j) = Range("input").Cells(i, j)
d_one = d_one + arraytwo(i, j)
Next j

With Range("output")
If arraytwo(i, 1) < 0 And d_one > 3 Then
.Cells(i, 1) = "message1"
ElseIf arraytwo(i, 1) > 0 And d_one > 3 Then
.Cells(i, 1) = "message2"
ElseIf arraytwo(i, 1) < -0.02 Then
.Cells(i, 1) = "message3"
ElseIf arraytwo(i, 1) > 0.05 Then
.Cells(i, 1) = "message4"
End If
End With

Next i

End Sub

[scrib3]

Thanks guys!!! :)

[sassora]

No problem

[SamT]

You're welcome.

SamT