VBA Code Required to fill Cell with a Float Value

[ashok_theagarajan]

Public Sub modulechangedata1()

'Declaring variables

Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim counter1 As Integer
Dim check1 As Boolean
Dim counter As Integer
Dim check As Boolean

'Delcaring connection constants
Const ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Q2_Softcall;Data Source=wxp-dqxqf1s\new"
Const ConnectionString1 = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=EMEASchema;Data Source=wxp-dqxqf1s\new"

'Clearing Sheets
With Sheets("Inbound Logs")
Range("A8:M8" & .Range("A60:M60").End(xlUp).Row).Clear
End With

With Sheets("Total Calls")
Range("A8:M8" & .Range("A60:M60").End(xlUp).Row).Clear
End With

With Sheets("Quick Calls")
Range("A8:M8" & .Range("A60:M60").End(xlUp).Row).Clear
End With

'Initializing connection constraints
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

check1 = True

sql = "select textn, count(textn) from dailysc where ([ContactType] like '0008' or [ContactType] = '0017') and ([team] like 'ukdis%' or [team] like 'dis%') and (textn like '7%') and (fyweek='200738') group by textn"

Sheets("Inbound Logs").Select

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.Open ConnectionString1
rs.Open sql, con, adOpenForwardOnly, adLockReadOnly

Range("A8").CopyFromRecordset rs
Range("A8:A65536").Copy

' With this total inbound logs is pulled extension wise for the current week and pasted

Sheets("Total Calls").Select
Range("A8:A65536").PasteSpecial xlPasteAll
Sheets("Quick Calls").Select
Range("A8:A65536").PasteSpecial xlPasteAll
Sheets("Call Logging %").Select
Range("A8:A65536").PasteSpecial xlPasteAll

'The same is pasted on total calls and quick calls sheet

Sheets("Quick Calls").Select
Set con = Nothing
Set rs = Nothing

counter = 8
MsgBox counter
check = True

Do While check = True
data = Range("A" & counter).Text
If data = "" Then
check = False
Exit Do
End If
counter = counter + 1
Loop
' With the above, the counter has the value of how many cells has numbers

counter1 = 8
Do While counter1 < counter
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
data = Range("A" & counter1).Text
sql1 = "select count(*) from qcall_new where textn = " & data & " " & " and fyweek = '200742'"
con.Open ConnectionString
rs.Open sql1, con, adOpenForwardOnly, adLockReadOnly
Range("B" & counter1).CopyFromRecordset rs
counter1 = counter1 + 1
Set con = Nothing
Set rs = Nothing
Loop

' With this, the number of quick calls is saved into the quick calls

Sheets("Total Calls").Select
counter1 = 8
Do While counter1 < counter
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
data = Range("A" & counter1).Text
sql1 = "select sum(handled) from techcalls_raw where textn = " & data & " " & " and fyweek = '200742'"
con.Open ConnectionString
rs.Open sql1, con, adOpenForwardOnly, adLockReadOnly
Range("B" & counter1).CopyFromRecordset rs
counter1 = counter1 + 1
Set con = Nothing
Set rs = Nothing
Loop

counter1 = 8
Dim test1 As Integer
Dim test2 As Integer
Dim test3 As Integer

Do While counter1 < counter
Sheets("Inbound Logs").Select
test1 = Range("B" & counter1).Text
Sheets("Quick Calls").Select
test2 = Range("B" & counter1).Text
Sheets("Total Calls").Select
test3 = Range("B" & counter1).Text

Until here the code works smooth

I take values for test1, test2 and test3 from different sheets
and here i define float as a double and divide it

However when i try to run
Sheets("Call Logging %").Select
Range("B" & counter1).text= float
code, it gives me an error message as "Object Required"


Dim float As Double
float = (test1 + test2) / test3
Sheets("Call Logging %").Select
Range("B" & counter1).text= float
counter1 = counter1 + 1
Loop


End Sub

 

[Andrew Poulsom]

The Text property of a Range object is read-only. It returns the contents as displayed by the number format. Use the Value property instead.