search for specific value on a sheet

[de ware]

Hi,

I Have a problem with some code I've added to one of my workbooks.

I'want to be able to type a value (for instance 319,89) in a textbox.
Afterwards computer has to search on a specific sheet to find all the lines containing that cell in column 10 and place the data on these lines in an array.

Code see below.

Problem seems to be in the lines

 Msgbox "a" &  res & "a" & zoekterm & "a"  
 Select Case res
        Case zoekterm
           Msgbox "found"

I've added the Msgbox "a" & res & "a" & zoekterm & "a" for controle which returns a319,89a319,89a. variables res and zoekterm seem to be the same.
Yet Msgbox "Found" doesn't appear

Private Sub cbt_frmzoeken_zoeken_Click()
Call listbox_openstaande(kolom, tekst) 'kolom and tekst are public
Unload Me
End Sub
 
Private Sub txb_frmzoeken_credit_Change()
txb_frmzoeken_debet.Text = ""
txb_frmzoeken_polis.Text = ""
txb_frmzoeken_datum.Text = ""
tekst = txb_frmzoeken_credit.Text
kolom = 11
End Sub
 
Public Sub listbox_openstaande(ByVal kolom As Variant, ByVal zoekterm As Variant)
'var in:    kolom = kolomnr scherm invoer waarin zoekterm moet worden gevonden
'           geselecteerde verrichtingen moeten zoekterm in juiste kolom invoer hebben
'Init
k = 0
Call laatste_rij("Invoer", "F")
Call zoeken_invoer(ni, LR, kolom, zoekterm)
frm_hf.lbx_openstaande.Clear
 
    frm_hf.lbx_openstaande.AddItem "Boeking", k
    frm_hf.lbx_openstaande.List(k, 1) = "Debet"
    frm_hf.lbx_openstaande.List(k, 2) = "Credit"
    frm_hf.lbx_openstaande.List(k, 3) = "Datum"
    frm_hf.lbx_openstaande.List(k, 4) = "Polis"
    frm_hf.lbx_openstaande.List(k, 5) = "Maatschappij"
    frm_hf.lbx_openstaande.List(k, 6) = "Rek"
    frm_hf.lbx_openstaande.List(k, 7) = "Periode"
    frm_hf.lbx_openstaande.List(k, 8) = "Omschrijving"
    k = k + 1
 
    frm_hf.lbx_openstaande.AddItem "", k
    frm_hf.lbx_openstaande.List(k, 1) = ""
    frm_hf.lbx_openstaande.List(k, 2) = ""
    frm_hf.lbx_openstaande.List(k, 3) = ""
    frm_hf.lbx_openstaande.List(k, 4) = ""
    frm_hf.lbx_openstaande.List(k, 5) = ""
    frm_hf.lbx_openstaande.List(k, 6) = ""
    frm_hf.lbx_openstaande.List(k, 7) = ""
    frm_hf.lbx_openstaande.List(k, 8) = ""
    k = k + 1
 
'Invoer
    leeg = res_zoeken_invoer(1, 1)
    Select Case leeg
        Case ""
        Exit Sub
        Case Else
            For i = LBound(res_zoeken_invoer, 2) To UBound(res_zoeken_invoer, 2)
                frm_hf.lbx_openstaande.AddItem res_zoeken_invoer(1, i), k   'boeking
                frm_hf.lbx_openstaande.List(k, 1) = res_zoeken_invoer(5, i)  'debet
                frm_hf.lbx_openstaande.List(k, 2) = res_zoeken_invoer(6, i)   'credit
                frm_hf.lbx_openstaande.List(k, 3) = res_zoeken_invoer(7, i)   'datum
                frm_hf.lbx_openstaande.List(k, 4) = res_zoeken_invoer(8, i)   'polis
                frm_hf.lbx_openstaande.List(k, 5) = res_zoeken_invoer(9, i)   'maatschappij
                frm_hf.lbx_openstaande.List(k, 6) = res_zoeken_invoer(10, i)  'rekening
                frm_hf.lbx_openstaande.List(k, 7) = res_zoeken_invoer(11, i)  'periode
                frm_hf.lbx_openstaande.List(k, 8) = res_zoeken_invoer(12, i)  'omschrijving
                k = k + 1
            Next
            ReDim res_zoeken_invoer(1 To 12, 1 To 1)
    End Select
 
 
C1 = CStr(Int(Sheets("Invoer").Columns(KIboeking).Width)) & ";"
C2 = CStr(Int(Sheets("Invoer").Columns(KIdebet).Width)) & ";"
C3 = CStr(Int(Sheets("Invoer").Columns(KIcredit).Width)) & ";"
C4 = CStr(Int(Sheets("Invoer").Columns(KIdatum).Width)) & ";"
C5 = CStr(Int(Sheets("Invoer").Columns(KIpolis).Width)) & ";"
C6 = CStr(Int(Sheets("Invoer").Columns(KImij).Width)) & ";"
C7 = CStr(Int(Sheets("Invoer").Columns(KIrekening).Width)) & ";"
C8 = CStr(Int(Sheets("Invoer").Columns(KIperiode).Width)) & ";"
C9 = CStr(Int(Sheets("Invoer").Columns(KIomschrijving).Width))
 
breedtes = C1 & C2 & C3 & C4 & C5 & C6 & C7 & C8 & C9
frm_hf.lbx_openstaande.ColumnWidths = breedtes
End Sub
 
Public Sub zoeken_invoer(ByVal eerste_rij As Integer, ByVal laatste_rij As Integer, ByVal kolom As Integer, ByVal zoekterm As Variant)
'Var in:    eerste_rij = startlijn
'           laatste_rij = eindlijn
'           kolom = kolom waarin de zoekwaarde moet worden gevonden
'           zoekwaarde = te zoeken string
'Var uit:   Array res_zoeken_invoer
'init:
Dim res As Variant
    x = 1
    'array met resultaten leegmaken
    ReDim res_zoeken_invoer(1 To 12, 1 To 1)
For i = eerste_rij To laatste_rij
    res = Sheets("Invoer").Cells(i, kolom).Value
 
 Msgbox "a" &  res & "a" & zoekterm & "a"  
 Select Case res
        Case zoekterm
           Msgbox "found"
            ReDim Preserve res_zoeken_invoer(1 To 12, 1 To x)
            res_zoeken_invoer(1, x) = Sheets("Invoer").Cells(i, KIboeking).FormulaR1C1
            res_zoeken_invoer(2, x) = Sheets("Invoer").Cells(i, KIklantnr).FormulaR1C1
            res_zoeken_invoer(3, x) = Sheets("Invoer").Cells(i, KInaam).FormulaR1C1
            res_zoeken_invoer(4, x) = Sheets("Invoer").Cells(i, KIvoornaam).FormulaR1C1
            debet = Sheets("Invoer").Cells(i, KIdebet).Value
            debet = FormatNumber(debet, 2, 0, 0, 0)
            res_zoeken_invoer(5, x) = debet
            credit = Sheets("Invoer").Cells(i, KIcredit).Value
            credit = FormatNumber(credit, 2, 0, 0, 0)
            res_zoeken_invoer(6, x) = credit
            res_zoeken_invoer(7, x) = Sheets("Invoer").Cells(i, KIdatum).Value
            res_zoeken_invoer(8, x) = Sheets("Invoer").Cells(i, KIpolis).FormulaR1C1
            res_zoeken_invoer(9, x) = Sheets("Invoer").Cells(i, KImij).FormulaR1C1
            res_zoeken_invoer(10, x) = Sheets("Invoer").Cells(i, KIrekening).FormulaR1C1
            res_zoeken_invoer(11, x) = Sheets("Invoer").Cells(i, KIperiode).FormulaR1C1
            res_zoeken_invoer(12, x) = Sheets("Invoer").Cells(i, KIomschrijving).FormulaR1C1
 
            x = x + 1
    End Select
Next
End Sub

 

[Arovin]

Hi de ware,
your code works for me. I created a workbook and added some random numbers in column 10, then I added "184,35" a few times.

Since I don't have the exact layout of frm_hf I called your sub with the line

 Call zoeken_invoer(1, 387, 10, 184.35)

and it found every occurrence of zoekterm in my range. I commented out everything after the msgbox, though. I suspect that the format of your number is the problem. You gave "319,89" as example, which won't be recognized in vba. Could you change the value to "319.89" and give it another try?

 

[de ware]

Thx for the help!

Found the solution myself already.
In the former code variable res was double, variable zoekterm was string.

Changed the code as follows:

Dim res As string    x = 1
    'array met resultaten leegmaken
    ReDim res_zoeken_invoer(1 To 12, 1 To 1)
For i = eerste_rij To laatste_rij
    res = Sheets("Invoer").Cells(i, kolom).text

Works well now...