division error while extracting data from sql server

baha17

Board Regular
Joined
May 12, 2010
Messages
181
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:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,215,437
Messages
6,124,871
Members
449,192
Latest member
MoonDancer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top