encontrar sin bucles 1er nro que falte en serie

hola, Ivan !

... como puedo buscar [via codigo] la primera ocurrencia que falte [en una columna]
en una serie numerica consecutiva (y claro, sin repeticiones)
con bucles no creo que tenga ningun problema, pero si la lista es larga
y como el proceso ya va englobado con otros cuantos, podria suponer una demora a#adida
supongo que debe haber alguna funcion de hoja (CountIf pej????)
que se pueda aplicar para agilizar el proceso, pero no se me ocurre cual ni como...

[en tanto *encontramos* alguna forma mas... *universal*/eficiente/...] :D

[hasta donde se] excel *acostumbra* resolver +/- rapido este tipo de consultas [en hoja de calculo] con formulas matriciales
solo que no son muy *apropiadas* para administrar columnas completas :-((
si comparas el *salto* de numeracion en dos *grupos* de un rango especifico [es decir]:
1er grupo: desde la primera hasta la penultima del rango
2do grupo: desde la segunda hasta la ultima del rango

si suponemos que el rango con la numeracion que debiera ser consecutiva es 'B2:B36'
la siguiente formula matricial te devuelve la posicion de la celda donde NO se cumple el *salto* de uno en uno:
=coincidir(falso,b2:b35³:b36-1,0)+1

dado que vba no puede *asimilar* el uso de formulas matriciales [al menos, hasta donde se]...
puedes aprovechar la funcion Evaluate("texto de la formula") +/- como sigue:

Sub Salto_en_consecutivo()
Dim Salto
Salto = Evaluate("match(false,b2:b35³:b36-1,0)+1")
If IsError(Salto) Then
MsgBox "No hay saltos en el consecutivo numerado."
Else
MsgBox "Existe un salto en el consecutivo " & Salto
End If
End Sub

[obviamente] el *truco* esta en *construir* las variables de texto para los dos grupos en el mismo rango
rango: 'B2:B36'
gpo 1: 'B2:B35'
gpo 2: 'B3:B36'

saludos,
hector.

muchas gracias Hector,

ahora se me estan cerrando los ojos, pero mañana la pruebo/adapto (e
intento entenderla) y te comento

un saludo y hasta mañana
Ivan

hola de nuevo, Hector

solo para confirmarte, tras las pruebas, que la propuesta realmente es una maravilla mas [de las tuyas].

te pongo la funcion/adaptacion que he hecho, por si ves algo modificable o que pueda faltar para prevenir algun posible
error mas (el principal lo da cuando no falta ningun dato [Error 2042] y el otro, <que realmente no es un error para vba
pero si para el fin de la macro> es si el rango incluye alguna fila vacia al final (improbable ,pero...),te devuelve el
mayor nº mas 1)

o simplemente por si quieres 'achicarla' un poco, y por si le es de utilidad a alguien mas

muchas gracias de nuevo y hasta pronto
Ivan

'************ funcion para encontrar el 1er nº que falte en una serie numerica consecutiva *******
' el argumento rng es el rango de datos de la lista (sin titulos)
'
Public Function Salto_Serie(rng As Range) As Long
Dim uf As Long, ref1 As String, ref2 As String, hoja As String, salto
With rng
hoja = .Parent.Name
uf = .Rows.Count
ref1 = Range(.Cells(1, 1), .Cells(uf - 1, 1)).Address(0, 0)
ref2 = Range(.Cells(2, 1), .Cells(uf, 1)).Address(0, 0)
On Error Resume Next
salto = Evaluate("match(false," & hoja & "!" & ref1 & "=" & _
hoja & "!" & ref2 & "-1,0)+1")
If Err.Number <> 0 Or salto > Application.Max(rng) Then salto = 0
End With
Salto_Serie = salto
End Function



"Héctor Miguel" escribió en el mensaje news:u1aY$

hola, Ivan !

... como puedo buscar [via codigo] la primera ocurrencia que falte [en una columna]
en una serie numerica consecutiva (y claro, sin repeticiones)
con bucles no creo que tenga ningun problema, pero si la lista es larga
y como el proceso ya va englobado con otros cuantos, podria suponer una demora a#adida
supongo que debe haber alguna funcion de hoja (CountIf pej????)
que se pueda aplicar para agilizar el proceso, pero no se me ocurre cual ni como...

[en tanto *encontramos* alguna forma mas... *universal*/eficiente/...] :D

[hasta donde se] excel *acostumbra* resolver +/- rapido este tipo de consultas [en hoja de calculo] con formulas
matriciales
solo que no son muy *apropiadas* para administrar columnas completas :-((
si comparas el *salto* de numeracion en dos *grupos* de un rango especifico [es decir]:
1er grupo: desde la primera hasta la penultima del rango
2do grupo: desde la segunda hasta la ultima del rango

si suponemos que el rango con la numeracion que debiera ser consecutiva es 'B2:B36'
la siguiente formula matricial te devuelve la posicion de la celda donde NO se cumple el *salto* de uno en uno:
=coincidir(falso,b2:b35³:b36-1,0)+1

dado que vba no puede *asimilar* el uso de formulas matriciales [al menos, hasta donde se]...
puedes aprovechar la funcion Evaluate("texto de la formula") +/- como sigue:

Sub Salto_en_consecutivo()
Dim Salto
Salto = Evaluate("match(false,b2:b35³:b36-1,0)+1")
If IsError(Salto) Then
MsgBox "No hay saltos en el consecutivo numerado."
Else
MsgBox "Existe un salto en el consecutivo " & Salto
End If
End Sub

[obviamente] el *truco* esta en *construir* las variables de texto para los dos grupos en el mismo rango
rango: 'B2:B36'
gpo 1: 'B2:B35'
gpo 2: 'B3:B36'

saludos,
hector.

hola de nuevo, discupas por lo pesado, pero solo dos aclaraciones
[sobre todo por si un 3º intenta utilizar la funcion]

1ª donde digo =>>

es si el rango incluye alguna fila vacia al final (improbable ,pero...),te devuelve el mayor nº mas 1)

->> solo ocurriria si no falta ningun dato en la serie, pues si falta
alguno devuelve este

2ª quizas convendria convertir la funcion en variant y devolver una
cadena vacia en vez de 0, pues tal como esta, evidentemente la serie
no podria contener el propio 0. No lo he probado pero parece logico

3º y, aunque no lo habia pensado, pues no entraba en mis necesidades,
supongo que tambien se podria adptar a un intervalo diferente de 1
para la serie pasandoselo como argumento y sustituyendo el 1 por este
(solo como idea por si a alguien le interesa)

un saludo de nuevo
Ivan

hola, Ivan !

te pongo la funcion/adaptacion que he hecho, por si ves algo modificable o que pueda faltar para prevenir algun posible error mas
(el principal lo da cuando no falta ningun dato [Error 2042] y el otro, <que realmente no es un error para vba pero si para el fin de la macro
es si el rango incluye alguna fila vacia al final (improbable, pero...),te devuelve el mayor nº mas 1)
o simplemente por si quieres 'achicarla' un poco, y por si le es de utilidad a alguien mas

solo con relacion a la instruccion donde verificas si hubo error en la evaluacion de los rangos...

If Err.Number <> 0 Or Salto > Application.Max(rng) Then Salto = 0
-> falla si la numeracion NO inicia desde 1

If Err.Number <> 0 Or Salto > .Rows.Count Then Salto = 0
-> falla si la ultima celda esta vacia

If Err.Number <> 0 Or Salto >= .Rows.Count Then Salto = 0
-> falla si el salto ocurre precisamente en la ultima celda

tratando de *rizar el rizo* [como dicen por ahi]... prueba las siguientes modificaciones:
[no se requiere del *manejo de errores*] :))

Function SaltoEnSerie(Rango As Range) As Long
Dim Hoja As String, Gpo1 As String, Gpo2 As String, Max As Long, Salto
With Rango: Hoja = .Parent.Name & "!": Max = .Rows.Count
Gpo1 = .Resize(Max - 1, 1).Address(0, 0): End With
Gpo2 = Range(Gpo1).Offset(1).Address(0, 0)
Salto = Evaluate("match(false," & Hoja & Gpo1 & "=" & Hoja & Gpo2 & "-1,0)+1")
SaltoEnSerie = IIf(IsError(Salto), 0, Salto) * -(Application.Count(Rango) = Max)
End Function

saludos,
hector.

'************ funcion para encontrar el 1er nº que falte en una serie numerica consecutiva *******
' el argumento rng es el rango de datos de la lista (sin titulos)
'
Public Function Salto_Serie(rng As Range) As Long
Dim uf As Long, ref1 As String, ref2 As String, hoja As String, salto
With rng
hoja = .Parent.Name
uf = .Rows.Count
ref1 = Range(.Cells(1, 1), .Cells(uf - 1, 1)).Address(0, 0)
ref2 = Range(.Cells(2, 1), .Cells(uf, 1)).Address(0, 0)
On Error Resume Next
salto = Evaluate("match(false," & hoja & "!" & ref1 & "=" & _
hoja & "!" & ref2 & "-1,0)+1")
If Err.Number <> 0 Or salto > Application.Max(rng) Then salto = 0
End With
Salto_Serie = salto
End Function