verial fonksiyonunu aşağıdaki kodla değiştirip deneyebilirsiniz )
dilerim işinize yarar
Kod:
Sub Verial()
Dim i As Integer, sorgu As String
Call baglanti
Range("a16:L65535").ClearContents
Set rs = CreateObject("adodb.recordset")
With Sheets("sayfa1")
k1 = Range("C1")
k2 = Range("C2")
k3 = Range("C3")
k4 = Range("C4")
k5 = Range("C5")
r1 = Range("C6")
r2 = Range("C7")
r3 = Range("C8")
r4 = Range("C9")
r5 = Range("C10")
r6 = Range("G1")
s = ""
s1 = "select * from sil "
If .Range("C1").Text <> "" Then s = s & " and [YIL] like """ & k1 & """"
If .Range("C2").Text <> "" Then s = s & " and [NO] like """ & k2 & """"
If .Range("C3").Text <> "" Then s = s & " and [DOSYAES] like """ & k3 & """"
If .Range("C4").Text <> "" Then s = s & " and [TARİH] like """ & k4 & """"
If .Range("C5").Text <> "" Then s = s & " and [HESAPNO] like """ & k5 & """"
If .Range("C6").Text <> "" Then s = s & " and ([ACIKLAMA] like ""%" & r1 & "%""" & _
" or [ACIKLAMA] like ""%" & Replace(r1, "i", "İ") & "%""" & _
" or [ACIKLAMA] like ""%" & Replace(r1, "ı", "I") & "%"")"
If .Range("C7").Text <> "" Then s = s & " and [TLDÖVİZ] like """ & r2 & "%"""
If .Range("C8").Text <> "" Then s = s & " and [VADE] like """ & r3 & """"
If .Range("C9").Text <> "" Then s = s & " and [PARA] like """ & r4 & """"
If .Range("C10").Text <> "" Then s = s & " and [DURUM] like ""%" & r5 & "%"""
If .Range("G1").Text <> "" Then s = s & " and [OZET] like ""%" & r6 & "%"""
s = IIf(s = "", s, " where " & Mid(s, 5))
s1 = s1 & s & " order by [YIL],[NO]"
rs.Open s1, con, 1, 1
Range("a16").CopyFromRecordset rs
If WorksheetFunction.Count(Range("A16:A65000").Value) Then
toplambulunan = WorksheetFunction.Count(Range("A16:A65000").Value)
Range("Sayfa1!I4").Value = toplambulunan
Exit Sub
End If
Range("A65000").End(xlUp).Offset(1, 0).Select
End With
End Sub