rutina demasiado larga

30/05/2006 - 11:39 por carmen | Informe spam

Hola a todos!

Tengo dos hojas de excel, del mismo libro, "Fondos" y "Lista original".

La hoja "Fondos" contiene, entre otros, los campos que me interesan,
que son "FondoID", "Precio" y "Fecha", y tiene en total alrededor de
24000 entradas.
La hoja "Lista original" contiene, tambien entre otros, los campos
"FondoID" y "Status",que puede ser Activo o Cerrado y tiene algunas
entradas menos, alrededor de 1600.

El problema que tengo que resolver en este punto es el siguiente: he de
comprobar que todos los valores de "FondoID" con "Status" Activo de la
hoja "Lista original" se encuentran al menos una vez en "FondoID" de la
hoja "Fondos". Si se repiten o no, me da igual, aunque de hecho lo
normal sera que se repitan, pero lo importante para mi es averiguar si
estan todos al menos una vez y, si encuentro algun "habitante" de la
hoja "Lista original" que no aparezca en "Fondos", lanzar un mensaje de
error o dejar constancia en un fichero de texto (pero esto ultimo no es
problema). El problema es que mi rutina tarda muchisimo, ha estado
corriendo durante 36 minutos y solo ha llegado hasta la entrada 106 de
la hoja "Lista original", es decir, ha cubierto mas o menos la decima
parte del proceso, y yo necesito que todo esto se realice en un periodo
de tiempo asequible, unos cuantos minutos, diez a lo sumo, porque esta
comprobacion se debera realizar cada dia tras la llegada de datos
nuevos...

Adjunto la rutina que uso, por si sirve de punto de partida, o alguien
encuentra algun error de principiante...

Suponiendo que "FondoID"=ColumnaA
"Fecha"=ColumnaB
"Precio"=ColumnaC todos estos en la
hoja "Fondos"
y en la hoja "Lista original" "FondoID"=ColumnaA
"Status"=ColumnaB
'###################################################################
For i = 11 To 1600

fondo = Sheets("Lista original").Cells(i, 1).Value

If Sheets("Lista original").Cells(i, 2).Value = "ACTIVE" Then

j = 1
Do While j <= 24000
If Sheets("Fondos").Cells(j, 1).Value = fondo Then
GoTo a
ElseIf Sheets("Fondos").Cells(j, 1).Value <> fund Then
flag=0
End If
j = j + 1
Loop
If flag=0 Then
MsgBox("Fondo " & fondo & " no encontrado")

ElseIf Sheets("Lista original").Cells(i, 2).Value = "CLOSED" Then

End If

a: Next i

'############################################################

Se le ocurre a alguien otro metodo que no implique estos dos "super
bucles"?

Muchas gracias!
Carmen

Siga el debate

2 respuestas

Tengo una respuesta

Preguntas similare

Mostrar todos los temas similares

Leer las respuestas

#1 Juan M

30/05/2006 - 14:38 | Informe spam

hola carmen

Tengo dos hojas de excel, del mismo libro, "Fondos" y "Lista original".

La hoja "Fondos" contiene, entre otros, los campos que me interesan,
que son "FondoID", "Precio" y "Fecha", y tiene en total alrededor de
24000 entradas.
La hoja "Lista original" contiene, tambien entre otros, los campos
"FondoID" y "Status",que puede ser Activo o Cerrado y tiene algunas
entradas menos, alrededor de 1600.

comprobar que todos los valores de "FondoID" con "Status" Activo de la
hoja "Lista original" se encuentran al menos una vez en "FondoID" de la
hoja "Fondos". al menos una vez y, si encuentro algun "habitante" de la
hoja "Lista original" que no aparezca en "Fondos", lanzar un mensaje de
error

Suponiendo que "FondoID"=ColumnaA
"Fecha"=ColumnaB
"Precio"=ColumnaC todos estos en la
hoja "Fondos"
y en la hoja "Lista original" "FondoID"=ColumnaA
"Status"=ColumnaB

prueba el siguiente codigo, modifica lo que creas necesario segun tus
necesidades
en los lugares donde modifica/escribe valores estan como comentarios

Al no indicar que quieres que haga si lo encuentra o no lo encuentra o no es
necesario buscar lo indico como comentario

si tus libros de origen tienen muchas formulas es posible que debas poner el
modo de calculo en manual mientras se ejecuta la macro
y luego dejarlo como al principio

si tienes cualquier duda comentas?

un saludo
juan

Sub comprueba()
Dim celda As Range
Dim MiRango As Range
Dim MiOtroRango As Range
Dim c As Range

'inicializa los valores de los rangos, modifica segun tus necesidades
With Worksheets("Lista Original")
Set MiRango = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With
With Worksheets("Fondos")
Set MiOtroRango = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With

For Each celda In MiRango
'comprueba el texto de busqueda
If celda.Offset(0, 1).Value = "activo" Then
'busca el valor si el status esta activo
Set c = MiOtroRango.Find(celda.Value, LookIn:=xlValues)
'metodo de busqueda de valores
If c Is Nothing Then
MsgBox "No se encontro dato " & celdda.value
'mensaje si no encuentra lo que busca
' Exit Sub
'sale si no encuentra el valor en fondos quitar el comentario
Else
'aqui ha encontrado coincidencia al menos una
' celda.Offset(0, 5) = "Encontrado"
'escribe que lo ha encontrado 5 columnas a partir de la columna A
End If
Else
' si no debe buscarlo
' celda.Offset(0, 5) = "no buscado"
'si el valor no tiene el status activado
End If
Next celda
End Sub

fin codigo--

Responder a este mensaje

#2 David

30/05/2006 - 19:17 | Informe spam

El problema está en el bucle interno. Cuando tienes muchos datos, buscar uno
a uno, sería como tener un diccionario y al buscar "murciélago", principiar
desde la letra A, palabra a palabra: la búsqueda se vuelve eterna...

Un método de búsqueda más eficiente, es la búsqueda binaria o de burbuja.
El único inconveniente, es que se requiere que los datos a buscar estén
ordenados ascendentemente. Intenta hacer esto manualmente, y luego lo
implementas en tu problema:

1.
Crea una nueva hoja en tu libro, y llámala XBus.

2.
Copia la columna de FondoID de la hoja Fondos hacia la columna A de la hoja
XBus. Luego la ordenas ascendentemente. Si al final del listado quedaran
celdas vacías, asegúrate de eliminarlas.

3.
En la celda B1 de la hoja XBus, ingresas la fórmula =Contara(A:A). En esta
celda deberá quedar el número que corresponde al último valor de los datos
que pegaste en la columna A.

4.
En el editor de VBA, inserta un módulo y pega el siguiente código:

Function BusBin(x As Double) As Integer
Application.Volatile
Dim ja, jf, jm, jl As Integer
Dim Salir As Boolean
Salir = False
ja = 1
jf = Range("XBus!B1").Value
Do
jm = Int((ja + jf) / 2)
If Sheets("XBus").Cells(jm, 1).Value = x Then
jl = jm
Salir = True
Else
If jf = jm Then
If Sheets("XBus").Cells(ja, 1).Value = x Then jl = ja Else
jl = 0
Salir = True
End If
If ja = jm Then
If Sheets("XBus").Cells(jf, 1).Value = x Then jl = jf Else
jl = 0
Salir = True
End If
If Sheets("XBus").Cells(jm, 1) < x Then ja = jm Else jf = jm
End If
Loop Until Salir
BusBin = jl
End Function

5.
En la hoja "Lista original", suponiendo que los datos de FondoID principian
en la celda A11, agrega esta fórmula a la derecha de tu info: =BusBin(A11)
y la copias. Si los datos son muchos, podrías luego copiar los resultados y
pegarlos como valores.

Esta fórmula te devolverá un valor cero si el dato no es encontrado, o un
valor mayor que cero cuando el dato sea encontrado.

Por último, podrás hacer un autofiltro para determinar cuales son los datos
que fueron localizados y los que no.

Comentas si no te funciona.

"carmen" escribió:

Hola a todos!

Tengo dos hojas de excel, del mismo libro, "Fondos" y "Lista original".

La hoja "Fondos" contiene, entre otros, los campos que me interesan,
que son "FondoID", "Precio" y "Fecha", y tiene en total alrededor de
24000 entradas.
La hoja "Lista original" contiene, tambien entre otros, los campos
"FondoID" y "Status",que puede ser Activo o Cerrado y tiene algunas
entradas menos, alrededor de 1600.

El problema que tengo que resolver en este punto es el siguiente: he de
comprobar que todos los valores de "FondoID" con "Status" Activo de la
hoja "Lista original" se encuentran al menos una vez en "FondoID" de la
hoja "Fondos". Si se repiten o no, me da igual, aunque de hecho lo
normal sera que se repitan, pero lo importante para mi es averiguar si
estan todos al menos una vez y, si encuentro algun "habitante" de la
hoja "Lista original" que no aparezca en "Fondos", lanzar un mensaje de
error o dejar constancia en un fichero de texto (pero esto ultimo no es
problema). El problema es que mi rutina tarda muchisimo, ha estado
corriendo durante 36 minutos y solo ha llegado hasta la entrada 106 de
la hoja "Lista original", es decir, ha cubierto mas o menos la decima
parte del proceso, y yo necesito que todo esto se realice en un periodo
de tiempo asequible, unos cuantos minutos, diez a lo sumo, porque esta
comprobacion se debera realizar cada dia tras la llegada de datos
nuevos...

Adjunto la rutina que uso, por si sirve de punto de partida, o alguien
encuentra algun error de principiante...

Suponiendo que "FondoID"=ColumnaA
"Fecha"=ColumnaB
"Precio"=ColumnaC todos estos en la
hoja "Fondos"
y en la hoja "Lista original" "FondoID"=ColumnaA
"Status"=ColumnaB
'###################################################################
For i = 11 To 1600

fondo = Sheets("Lista original").Cells(i, 1).Value

If Sheets("Lista original").Cells(i, 2).Value = "ACTIVE" Then

j = 1
Do While j <= 24000
If Sheets("Fondos").Cells(j, 1).Value = fondo Then
GoTo a
ElseIf Sheets("Fondos").Cells(j, 1).Value <> fund Then
flag=0
End If
j = j + 1
Loop
If flag=0 Then
MsgBox("Fondo " & fondo & " no encontrado")

ElseIf Sheets("Lista original").Cells(i, 2).Value = "CLOSED" Then

End If

a: Next i

'############################################################

Se le ocurre a alguien otro metodo que no implique estos dos "super
bucles"?

Muchas gracias!
Carmen

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

Busqueda sugerida