Vba code to display the 3 values contained in a specified column in a message box

[jpc_2106]

hi, im very new to vba as you can probably tell by the question, i have a performance summary table with 3 rows of values (mean return, st. dev, sharpe ratio) , for 5 different trading rules, for 5 different stocks, so there are 25 columns containing data, 5 for columns for each stock. i have some code written for a message box to input the preferred stock and trading rule, but i am getting an error 9 out of range after inputting the chosen trading rule, i want to be able to input my stock and my trading rule, then i want a message box to pop up displaying the three values for the trading rule for that stock, i have attatched the code i currently have :

Sub OutOfSampleCalculator()

'Declare Variables
Dim Name As String
Dim Stock As String
Dim TradingRule As String
Dim rng As Range
Dim Mean As Long
Dim SharpeRatio As Long
Dim StandardDeviation As Long


'Input Name"
Name = InputBox("Please Enter Your Name")

'Input Preferred Stock"
Stock = InputBox("Please Choose a Stock From the Following : Hess Corporation, Berkshire Hathaway, Edison International, Robert Half Inc, Ameriprise Inc")

'Input Preferred Trading Rule"
TradingRule = InputBox("Please Choose a Trading Rule From The Following : Moving Average 1, Moving Average 2, Moving Average 3, Oscillator 1, Oscillator 2")


'Find the out of sample Performance Summary Worksheet"
Set ws = ThisWorkbook.Sheets("Out Of Sample PerformanceSummary")

'Based on the selected stock select the performance Range"
If Stock = "Hess Corporation" Then
Set rng = ws.Range("B3:F6")
End If

If Stock = "Berkshire Hathaway" Then
Set rng = ws.Range("G3:K6")
End If

If Stock = "Edison International" Then
Set rng = ws.Range("L3:P6")
End If

If Stock = "Robert Half Inc" Then
Set rng = ws.Range("Q3:U6")
End If

If Stock = "Ameriprise Inc" Then
Set rng = ws.Range("V3:Z6")
End If

'Based on the Selected Trading Rule Locate the Corresponding Column Within the performance range
If TradingRule = "Moving Average 1" Then
searchValue = WorksheetFunction.Lookup(rng.Column(1))
Else
If TradingRule = "Moving Average 2" Then
searchValue = WorksheetFunction.Lookup(rng.Column(2))
Else
If TradingRule = "Moving Average 3" Then
searchValue = WorksheetFunction.Lookup(rng.Column(3))
Else
If TradingRule = "Oscillator 1" Then
searchValue = WorksheetFunction.Lookup(rng.Column(4))
Else
If TradingRule = "Oscillator 2" Then
searchValue = WorksheetFunction.Lookup(rng.Column(5))
End If
End If
End If
End If
End If

'Display The Results In a Message Box"
MsgBox "Mean: " & Format(meanValue, "0.00") & vbCrLf & _
"Standard Deviation: " & Format(stdDevValue, "0.00") & vbCrLf & _
"Sharpe Ratio: " & Format(SharpeRatio, "0.00"), vbInformation

End Sub

 

[rlv01]

A MsgBox is not well suited to displaying columns. What little that can be done, has to be done with Tabs.

Sub MsgBoxTest()
    Dim meanValue, stdDevValue, SharpeRatio
    Dim Msg As String
  
    meanValue = 1.5
    stdDevValue = 2.5
    SharpeRatio = 3.5
  
    Msg = ""
    Msg = Msg & "Mean: " & String(3, vbTab) & Format(meanValue, "0.00") & vbCr
    Msg = Msg & "Standard Deviation: " & String(1, vbTab) & Format(stdDevValue, "0.00") & vbCr
    Msg = Msg & "Sharpe Ratio: " & String(2, vbTab) & Format(SharpeRatio, "0.00")
  
    'Display The Results In a Message Box"
    MsgBox Msg, vbInformation
End Sub

For anything beyond that you must create a userform.

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)