VBA gives error after textbox can not find any value

aaaccc

New Member
Joined
Feb 6, 2014
Messages
1
Hello, firstly sorry for my bad english. I have a problem about my little vba code. I make a code in excel about filtering values and write a combobox value to filtered cell. I used te filtered cell address using texbox filter. When I type a value to the cell which is not in that column VBA gives error "object variable or with block variable not set".
This line gves error: Application.Goto Reference:=Range(ilkhucre.address), scroll:=False
I want to ask where I make mistake in my code?

My code is:

Code:
Private Sub ComboBox5_Change()                    End Sub    Private Sub ComboBox1_Change()      ComboBox3.Value = "": ComboBox2.Value = ""      If ComboBox1.Text <> "" Then          Call baglan          ComboBox2.Column  = con.Execute("select [İLÇELER] from [Sayfa3$] where [İLLER]='" &  ComboBox1.Text & "' group by [İLÇELER]").getrows          Set con = Nothing      Else          ComboBox2.Clear: ComboBox3.Clear      End If  End Sub    Private Sub ComboBox2_Change()      ComboBox3.Text = ""      If ComboBox2.Text <> "" Then          Call baglan          ComboBox3.Column = con.Execute("select [ÇALIŞANLAR] from [Sayfa3$] where [İLLER]='" & ComboBox1.Text & "'" & _          " and [İLÇELER]='" & ComboBox2.Text & "'").getrows          Set con = Nothing      Else          ComboBox3.Clear      End If  End Sub    Private Sub ComboBox3_Change()      ComboBox4.Text = ""      If ComboBox3.Text <> "" Then          Call baglan          ComboBox4.Column = con.Execute("select [ÇALIŞANLAR] from [Sayfa3$] where [İLLER]='" & ComboBox1.Text & "'" & _          " and [İLÇELER]='" & ComboBox2.Text & "'").getrows          Set con = Nothing      Else          ComboBox3.Clear      End If  End Sub    Private Sub ComboBox4_Change()        End Sub        Private Sub ComboBox6_Change()                    End Sub    Private Sub CommandButton1_Click()            If SatirSayisiVer > 1 Then          MsgBox "Filtrelenen veriye göre benzersiz kayıt bulunamadı!"      ElseIf SatirSayisiVer = 1 Then          MsgBox "Veri Yaz"          Kaydet      ElseIf SatirSayisiVer = 0 Then          MsgBox "Filtrelenen veriye göre hiçbir kayıt bulunamadı!"      ElseIf SatirSayisiVer = -1 Then          MsgBox "-1"      End If                    End Sub      Private  Sub DTPicker21_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As  Integer, ByVal CallbackField As String, CallbackDate As Date)        End Sub    Private Sub TextBox1_Change()            TextBox1.MaxLength = 6      SadeceSayi TextBox1            Filtrele              End Sub  Sub SadeceSayi(obj)                  Dim i As Integer            Dim str As String            If TypeName(obj) = "TextBox" Then                    With obj                            For i = 1 To Len(obj.Text)                                    str = Mid(.Text, i, 1)                                    If Not IsNumeric(str) And .Value <> vbNullString Then                                            .Text = Mid(obj.Text, 1, i - 1) & Mid(obj.Text, i + 1)                      MsgBox "Sadece Sayı Giriniz"                                        End If                                Next                        End With                End If        End Sub      Function Filtrele()                  ActiveCellDegistir IlkHucreBul      Selection.AutoFilter Field:=9, Criteria1:="*" & TextBox1.Value & "*"            If TextBox1.Value = "" Then          Selection.AutoFilter Field:=9      End If        End Function    Function SatirSayisiVer() As Integer            Range("a1").Select      Selection.CurrentRegion.Select      row_count = Selection.Rows.Count - 3            matched_criteria = 0      check_row = 0            Filtrele            While Not IsEmpty(ActiveCell)          ActiveCell.Offset(1, 0).Select          If ActiveCell.RowHeight = 0 Then              check_row = check_row + 1          Else              matched_criteria = matched_criteria + 1          End If      Wend            ActiveCellDegistir IlkHucreBul            If row_count = check_row Then          SatirSayisiVer = -1      Else          SatirSayisiVer = matched_criteria      End If              End Function    Function IlkHucreBul() As Range            Dim aboneNo As String      Dim ilkhucre As Range      On Error Resume Next       'aboneNo = (TextBox1.Value * 1)     aboneNo = TextBox1.Value      Set ilkhucre = Range("I3: I65000 ").Find(What:=aboneNo)            Set IlkHucreBul = ilkhucre        End Function      Function ActiveCellDegistir(ilkhucre As Range)                        Application.Goto Reference:=Range(ilkhucre.Address), Scroll:=False              End Function      Function Kaydet()            Dim activeSatir As Integer      activeSatir = ActiveCell.Row      Range("B" & activeSatir).Value = ComboBox1.Value      Range("C" & activeSatir).Value = ComboBox2.Value        End Function
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,214,782
Messages
6,121,532
Members
449,037
Latest member
tmmotairi

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