[VBA]Is my search method bad?

[NessPJ]

Hello all,

I wrote the following piece of VBA to find the right "Searchvalue" and only continue if the Searchvalue was indeed found.
The routine seems to go through every value it finds with this method and if the first one it finds isn't correct, the IF statement will go to the Else condition right away.

Something is wrong in my logic here?

Dim TabelnrBereikLR As Long
Dim Searchvalue As Long, Foundvalue As Long

'....start of my code....

TabelnrBereikLR = Sheets("Masterdata").Range("E65534").End(xlUp).Row

'Loop each cell in range
        For n = TabelnrBereikLR To 4 Step -1
       
        If RecenteInvoer = True Then
            Searchvalue = RecentTabelnr
        Else
            Searchvalue = Tabelnr
        End If
       
        'Debug.Print "Searchvalue = "; Searchvalue
       
        Foundvalue = Sheets("Masterdata").Range("B" & n).Value

            If Format(Foundvalue, "00000") = Format(Searchvalue, "00000") Then
           
            'Debug.Print "Found = "; Sheets("Masterdata").Range("B" & n).Value
           
            GoTo TabelnrGevalideerd
           
            Else
           
            MsgBox "Het gekozen Tabelnummer / Printopdrachtnummer is nog niet gevuld of is niet geldig. De handeling kan niet worden uitgevoerd.", vbCritical, "Bewerking afgebroken"
            GoTo EindeCancel
                   
            End If
   
        Next
       
        n = 0

TabelnrGevalideerd:

'...code to execute when the value was found...


Einde:      '// Einde van de routine

Sheets("Menu").Activate

PROTON (Password)

Application.ActivePrinter = StandaardPrinter

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub


EindeCancel:

Sheets("Menu").Activate

PROTON (Password)

Application.ActivePrinter = StandaardPrinter

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

End Sub

 

[NessPJ]

Anyone please?

 

[Yongle]

please supply sample data
XL2BB is the site's tool to help you

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

and explain what expect VBA to find

 

[MasterVBA]

Hello,

Below a Function Find :

You can Juste Modify Name Sheet ("DATA") and column where you need Find the Value

Function Chercher_Semaine(MaSema As String)
Dim Trouve As Range, PlageDeRecherche As Range,Dim SemTrouve As Boolean
Set PlageDeRecherche = Sheets("Data").Columns(1) ' You Must Change Data = Your Sheet and 1 with you column
Set Trouve = PlageDeRecherche.Cells.Find(what:=MaSema, LookAt:=xlWhole)
'MsgBox Trouve.row
If Trouve Is Nothing Then
SemTrouve = False
Else
SemTrouve = True

End If
Chercher_Semaine = SemTrouve


End Function


Sub Main()
Chercher_Semaine(You Value to Find)

end sub

 

[GWteB]

@NessPJ, no matter what condition is met, due to both GoTo TabelnrGevalideerd and GoTo EindeCancel your code always jumps out of the For/Next loop and will never search for other values.

 

[NessPJ]

Hello,

Below a Function Find :

You can Juste Modify Name Sheet ("DATA") and column where you need Find the Value
......

With your help i tried to make the following function but i am still getting a "Type Mismatch" error on SearchValue

What am i doing wrong?

Private Function Find(SearchString As String, SearchSheet As String, SearchColumn As Range)

    Dim SearchValue As String, SearchRange As Range
    Dim SearchResult As String
   
    SearchValue = SearchString

    Set SearchRange = Sheets(SearchSheet).Range(SearchColumn & """:""" & SearchColumn)
    SearchValue = SearchRange.Cells.Find(what:=SearchValue, LookAt:=xlWhole)
    If (Not SearchValue Is "") Then
        ' Do something
    End If

End Function

 

[MasterVBA]

Test This Code
Code find Value "Mehdi" on Sheet (Sheet1) in All Column 1 ==> A

**********

Private Function Find(SearchString As String, SearchSheet As String, SearchColumn As Integer)
Dim SearchValue As Range, SearchRange As Range, SearchResult As String
Set SearchRange = Sheets("Sheet1").Columns(SearchColumn)
Set SearchValue = SearchRange.Cells.Find(what:=SearchString, LookAt:=xlWhole)
'MsgBox SearchValue.ce
If SearchValue Is Nothing Then
FindValue = False
Else
FindValue = True
End If
Find = FindValue
End Function



Sub main()
Dim res As Boolean
res = Find("Mehdi", "Sheet1", 1)
If res = True Then
MsgBox "Gooood"
Else
MsgBox "Nooo "
End If
End Sub

 

[NessPJ]

Thanks for all the help guys! I got it to work with all your tips. This is the code i ended up using:

Private Sub ValidatePrinting()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim Searchstring As String, SearchValue As String, ValidatieWaarde As Long

Start:      '//Start van de routine

Tabelnr:
Tabelnr = InputBox("Tabelnummer / Printopdrachtnummer invoeren", "Invoer")

    If StrPtr(Tabelnr) = 0 Then
        GoTo EindeCancel
      
    ElseIf Tabelnr = vbNullString Then
        MsgBox "Deze waarde mag niet leeg zijn! Vul een geldige waarde in!", vbCritical, "Ongeldige waarde"
        GoTo Tabelnr
          
    ElseIf Tabelnr = 0 Then
        MsgBox "Deze waarde mag niet 0 zijn! Vul een geldige waarde in of annuleer de handeling!", vbCritical, "Ongeldige waarde"
        GoTo Tabelnr
      
    End If

'Validatie
    SearchString = SearchValue
      
    Foundvalue = Find(SearchString, "Masterdata", "B")
                
    ValidatieWaarde = Sheets("Masterdata").Range(Foundvalue).Offset(0, 3).Value
  
    If Not ValidatieWaarde > 0 Then GoTo EindeValidatieOnjuist


'Put all other VBA code you want to execute here


Einde:      '// Einde van de routine

Sheets("Menu").Activate

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub


EindeCancel:

Sheets("Menu").Activate

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

EindeValidatieOnjuist:

Sheets("Menu").Activate

MsgBox "Het ingevoerde Tabelnummer / Printopdrachtnummer is ongeldig. De bewerking wordt afgebroken.", vbCritical, "Ongeldige waarde"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

End Sub



Private Function Find(SearchString As String, SearchSheet As String, SearchColumn As String)

    'Dim FoundSomething As Range
    Dim SearchValueFUNC As Range, SearchRange As Range, Found As String
    Dim SearchResult As String

    Set SearchRange = Sheets(SearchSheet).Columns(SearchColumn)
    Set SearchValueFUNC = SearchRange.Cells.Find(what:=SearchString, LookAt:=xlWhole)
    If (Not SearchValueFUNC Is Nothing) Then
    Find = SearchValueFUNC.Address
    Else
    Find = vbNullString
    End If

End Function

 

[NessPJ]

SearchValue = <which ever search value you want to use>

This line was still missing right below "'Validatie" sorry (wasn't allowed to edit the message anymore).

 

[Trixterz]

There is more than one way of find a solution, good job on figuring out yours.