Hi,
This code was built to get Data from Database and use the data as a source
for a Chart. I am finding it difficult to get the Range into an variable
where I am getting a Error MEssage as saying as :

<ERROR
Error Number 91
object variable or With block Variable not set
</ERROR

This error prompts once the following code is executed:

<CODE
' Assign the address of the selected range of cells to a variable.
rRange = Selection.Address()
</CODE


The full Code is give below.

<CODE
Private Sub CommandButton1_Click()
On Error GoTo Err_Execute

Dim oCon As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sSql, sID As String
Dim rRange As Range
Dim i As Integer

'Delete the Previous Request
Range("A9", Range("A9").End(xlToRight)).Select

' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns.
Selection.CurrentRegion.Select


' Assign the address of the selected range of cells to a variable.
rRange = Selection.Address()

Selection.Clear

i = 9

If cmbStock.ListIndex 0 Then
sID = Left(cmbStock.Value, 9)
Else
sID = "JKH N0000"
End If

sSql = "SELECT XSACTDATE, EQUITYID, CLOSINGPRICE FROM EQUITYPRICES " & _
"WHERE XSACTDATE = '" & Format(dtpFrom.Value, "DD-MMM-YYYY") &
"' " & _
"AND XSACTDATE <= '" & Format(dtpTo.Value, "DD-MMM-YYYY") & "' "
& _
"AND EQUITYID = '" & sID & "' ORDER BY XSACTDATE ASC"

MsgBox sSql

Set oCon = New ADODB.Connection
Set oRs = New ADODB.Recordset

oRs.CursorLocation = adUseClient

'oCon.Open "DSN=JKSB_BACKEND", "JKSBWEB", "webjksb"
oCon.Open "Provider=MSDAORA.1;Password=mypwd;User ID=myuser;Data
Source=MYORACLE;Persist Security Info=True"
oRs.Open sSql, oCon, adOpenDynamic, adLockOptimistic

If Not oRs.EOF Then
oRs.MoveFirst

While Not oRs.EOF
Cells(i, 1) = oRs.Fields("xsactdate").Value
Cells(i, 2) = oRs.Fields("closingprice").Value

i = i + 1

oRs.MoveNext
Wend
Else
MsgBox "No Records Found!", vbExclamation, "Error"
End If

'Clean up
If Not oRs Is Nothing Then
If oRs.State = adStateOpen Then oRs.Close
End If
Set oRs = Nothing

If Not oCon Is Nothing Then
If oCon.State = adStateOpen Then oCon.Close
End If
Set oCon = Nothing


ActiveChart.SetSourceData (CVar(rRange))

MsgBox "Records Filled: " & CStr(i - 9)


Err_Execute:
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
End Sub

</CODE
--
Hifni Shahzard Nazeer M.

Hi

Variable rRange is not correctly defined use either:
Dim rRange As String
rRange = Selection.Address

or:
Dim rRange As Range
Set rRange = Selection

HTH
Cordially
Pascal


"Hifni" a écrit dans le message de news:
...
Hi,
This code was built to get Data from Database and use the data as a source
for a Chart. I am finding it difficult to get the Range into an variable
where I am getting a Error MEssage as saying as :

<ERROR
Error Number 91
object variable or With block Variable not set
</ERROR

This error prompts once the following code is executed:

<CODE
' Assign the address of the selected range of cells to a variable.
rRange = Selection.Address()
</CODE


The full Code is give below.

<CODE
Private Sub CommandButton1_Click()
On Error GoTo Err_Execute

Dim oCon As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sSql, sID As String
Dim rRange As Range
Dim i As Integer

'Delete the Previous Request
Range("A9", Range("A9").End(xlToRight)).Select

' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns.
Selection.CurrentRegion.Select


' Assign the address of the selected range of cells to a variable.
rRange = Selection.Address()

Selection.Clear

i = 9

If cmbStock.ListIndex 0 Then
sID = Left(cmbStock.Value, 9)
Else
sID = "JKH N0000"
End If

sSql = "SELECT XSACTDATE, EQUITYID, CLOSINGPRICE FROM EQUITYPRICES " &
_
"WHERE XSACTDATE = '" & Format(dtpFrom.Value, "DD-MMM-YYYY") &
"' " & _
"AND XSACTDATE <= '" & Format(dtpTo.Value, "DD-MMM-YYYY") & "'
"
& _
"AND EQUITYID = '" & sID & "' ORDER BY XSACTDATE ASC"

MsgBox sSql

Set oCon = New ADODB.Connection
Set oRs = New ADODB.Recordset

oRs.CursorLocation = adUseClient

'oCon.Open "DSN=JKSB_BACKEND", "JKSBWEB", "webjksb"
oCon.Open "Provider=MSDAORA.1;Password=mypwd;User ID=myuser;Data
Source=MYORACLE;Persist Security Info=True"
oRs.Open sSql, oCon, adOpenDynamic, adLockOptimistic

If Not oRs.EOF Then
oRs.MoveFirst

While Not oRs.EOF
Cells(i, 1) = oRs.Fields("xsactdate").Value
Cells(i, 2) = oRs.Fields("closingprice").Value

i = i + 1

oRs.MoveNext
Wend
Else
MsgBox "No Records Found!", vbExclamation, "Error"
End If

'Clean up
If Not oRs Is Nothing Then
If oRs.State = adStateOpen Then oRs.Close
End If
Set oRs = Nothing

If Not oCon Is Nothing Then
If oCon.State = adStateOpen Then oCon.Close
End If
Set oCon = Nothing


ActiveChart.SetSourceData (CVar(rRange))

MsgBox "Records Filled: " & CStr(i - 9)


Err_Execute:
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
End Sub

</CODE
--
Hifni Shahzard Nazeer M.