Dear Excel Guru's
I have a code which allows me to extract data from sql server. It works fine until I add new field in where I make a percentage calculations by dividing data from two field.Then it generates error for me. I put the code but i cannot disclose the server name and the field names because of our company policy. Please let me know if you think there is a way to handle that division error. For your info, it only generates error while the division is n/0.Here is the code:
As you can see this is the one causing the error:
The whole code:
I have a code which allows me to extract data from sql server. It works fine until I add new field in where I make a percentage calculations by dividing data from two field.Then it generates error for me. I put the code but i cannot disclose the server name and the field names because of our company policy. Please let me know if you think there is a way to handle that division error. For your info, it only generates error while the division is n/0.Here is the code:
As you can see this is the one causing the error:
Code:
Table2.Field2/(Table2.Field2-Table2.Field1+Table2.Field4)
The whole code:
Code:
Sub GetingDataFromServer()
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim cel As Range
Dim TD As Long
Dim qdate As Double
Dim LastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Columns("A:G").AutoFilter
On Error Resume Next
qdate = Date
Sheets("Data").Range("A2:G20000").ClearContents
Sheets("Data").Select
Range("A2").Select
Selection.Activate
Set TargetRange = Range("A2")
Set Conn = New ADODB.Connection
Conn.Open "driver={SQL Server};" & _
"server=XXXX;database=WWWWWW;"
Set RecSet = New Recordset
RecSet.Open "SELECT Table2.Field1, Table2.Field2, Table2.Field4, Table2.Field5, Table2.Field2-Table2.Field1+Table2.Field4, Table2.Field2/(Table2.Field2-Table2.Field1+Table2.Field4) FROM table1 table1 WHERE table2.game_date='" & qdate & "'And table2.pit_id<>'" & 899 & "'", Conn, , , adCmdText
TargetRange.CopyFromRecordset RecSet
RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
'Calculating Hold
LastRow = Sheets("Data").Range("A" & Sheets("Data").Rows.Count).End(xlUp).Row - 1
For Each cel In Sheets("Data").Range("A2:A" & LastRow)
If cel.Text <> "" And Len(cel.Offset(0, 1)) < 8 Then
cel.Offset(0, 5) = Format(cel.Offset(0, 3).Value / cel.Offset(0, 4).Value, "0.0%")
cel.Offset(0, 6) = Left(cel.Offset(0, 1), 2)
End If
If cel.Text <> "" And Len(cel.Offset(0, 1)) > 7 Then
cel.Offset(0, 5) = Format(cel.Offset(0, 3).Value / cel.Offset(0, 4).Value, "0.0%")
cel.Offset(0, 6) = Left(cel.Offset(0, 1), 3)
End If
Next cel
Columns("A:G").AutoFilter
End Sub