01/05/2023 - By65Franco ha scritto:
' CHECK IF RECORD ALREADY INSERTED
Function myCheckAlreadyInserted(vRangeAreas As Variant, vStrSearch As Variant) As Integer
myCheckAlreadyInserted = 0
Dim strRng As Range
With Application.Range(vRangeAreas)
Set strRng = .Find(vStrSearch, LookIn:=xlValues, LookAt:=xlWhole)
If Not strRng Is Nothing Then myCheckAlreadyInserted = strRng.Row
End With
End Function
Salve a tutti… mi auto cito ;))
Dopo alcune verifiche e test, chiudo comunque il codice con la funzione Find e FindNext in questo modo:
I controlli che ho inserito hanno lo scopo di ottimizzare la ricerca in quanto il valore da ricercare viene inserito nella stessa colonna dove viene effettuata la ricerca.
Pertanto deve essere escluso dalla ricerca Find la cella con il valore appena inserito, ma ricercare solo se ci sono altri valori uguali già inseriti nelle altre celle nella medesima colonna,
..
....
......
' check if record already inserted
Dim nRowSearch As Integer
nRowSearch = myCheckAlreadyInserted(Target.EntireColumn.Address, Target)
If nRowSearch > 0 Then
If MsgBox("Record already inserted in line [ " & nRowSearch & " ]... Add anyway?", vbYesNo + vbCritical, "Error") = vbNo Then
Range(Target.Address).Select
......
....
..
____________________________________________________________________________________________________________________________
' CHECK IF RECORD ALREADY INSERTED AND THE FIRST FOUND LINE RETURNS
Function myCheckAlreadyInserted(vRangeAreas As Variant, rngSearch As Range) As Integer
' set default value function
myCheckAlreadyInserted = 0
' search in range
With Application.Range(vRangeAreas)
' start the search
Dim strRng As Range
Set strRng = .Find(rngSearch.Value, LookAt:=xlWhole)
' loop search
Do While Not strRng Is Nothing
' if row found and is not the current one
If strRng.Row <> rngSearch.Row Then
' returns the first line found to the function and exit loop
myCheckAlreadyInserted = strRng.Row
Exit Do
End If
' next research
Set strRng = .FindNext(strRng)
' check if there are no other occurrences and exit loop
If strRng.Address = rngSearch.Address Then Exit Do
Loop
End With
End Function
Comunque sono sempre ben accetti consigli e/o correzioni … Grazie