problem in writing data into an excel cell
Private sub command1_click()
'Open the Workbook
Set wkbSource = Workbooks.Open("C:\test")
Set wksSource = wkbSource.Worksheets("Sheet1")
LastRow = wksSource.UsedRange.End(xlDown).Row
For i = 2 To LastRow
scat = wksSource.Cells(i, 2)
Matching
Next i
'Close the Workbook
wkbSource.Close False
'Clear the Workbook object variable
Set wkbSource = Nothing
end sub
Private Sub Matching()
Rs1.Open "Select * from Category where CategoryDescription like '" & scat & "'", cnn, adOpenStatic, adLockOptimistic
If Rs1.RecordCount = 0 Then
wkbSource.Sheets("Sheet2").Cells(i, 2).Value = "No Value"
Else
wkbSource.Sheets("Sheet2").Cells(i, 2).Value = Rs1!CategoryID
End If
Rs1.Close
End Sub
For example if wksSource.cells(i,2) contains Politics so I use the SQL query to see what's the categoryID of Politics,
for example let's say it's 1,so I want to write 1 in the same cell of the same workbook but in Sheet2,
but the problem with me is that despite Rs1!CategoryID returns a value,this value isn't written to the
wksSource.cells(i,2) sheet2.
what's wrong with my code?
Thanks in advance
Re: problem in writing data into an excel cell
Where have you declared the variable "i"?
The problem looks like the scope of that variable is not clearly defined. You will need to declare that variable with at least a module level scope if you need it to be used in multiple procedures.