Busco método más eficaz y sobre todo más rápido que BuscarV o Indice+Coincidir para vba.

Juan Karlos · el 1 de julio

Hola:

Gracias de antemano por la atención de leerme.

Mi problema es que mis macros son muy rudimentarias, por mi falta de formación.

Cuando tengo que extraer un dato determinado de otra columna, hoja, libro, etc...

Siempre utilizo el mismo sistema:

Sub Ejemplo()[/b]

Range("AJ2").Select

ActiveCell.FormulaR1C1 = _

"=INDEX(CLIENTE_GESTOR!C3,MATCH(RC[-35],CLIENTE_GESTOR!C1,0),1)"

A = 2

While Cells(A, 1) <> ""

A = A + 1

Wend

Range("AJ2").Select

Selection.Copy

Range(Cells(3, 36), Cells((A - 1), 36)).Select

ActiveSheet.Paste

Range("AJ:AJ").Copy

Range("AJ:AJ").PasteSpecial xlPasteValues

Range("AJ:AJ").Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

End Sub

Formulo en una celda y copio la fórmula en el rango que necesito.

Posteriormente, copio el mismo rango y pego valores

Cuando esto lo haces con herramientas que mueven tablas de 45 columnas y ciento y pico o doscientas mil líneas, la macro se hace eterna, cuando no te deja el pc, completamente "nokeado" durante una lapso demasiado largo de tiempo, como para que pueda ser rentable utilizarla.

Os ruego que, por favor y cuando os fuera posible, me comentarais si existe algún otro medio o modo en vba que haga lo mismo (buscar un dato relacionado con un referente, en la misma línea) y que no deje a la cpu envuelta en un mar de fórmulas, quedándose atascada en el limbo de los cálculos.

Es mi 1ª vez, por lo que pido de antemano, que sepáis perdonar mis defectos y/o excesos, ajustándome gustosamente a las indicaciones que me deis.

Gracias sinceras por cualquier tipo de ayuda o guía.

Saludos cordiales.

JC

bigpetroman · el 1 de julio

amigo Juan Karlos, si subes un pequeño ejemplo (archivo de excel) con la tabla real no importa que no sean real lo valores, de seguro te podremos ayudar mas facil, gracias

Juan Karlos · el 2 de julio

Hola Big:

Y gracias por tu rápida respuesta...

Ayer no adjunté ningún ejemplo, porque (novato yo) no dí con la forma de subirlo. Tan solo ante tu insistencía, he tenido la suerte de ver finalmente la opción para adjuntar el archivo.

La cosa sería (ciñendonos al ejemplo), que en la 1ª hoja "FÓRMULA" tengo una serie de datos pero no el Stock, que si lo tengo en la 2ª hoja "DATOS LOG".

Entre las dos hojas tan solo hay un dato en común que me relaciona toda la información que es la columna "CODIGO".

Con respecto a este referente, en la columna "J:J" formulo en macro, utilizando el "INDICE/COINCIDIR":

[b][b]Sub Ejemplo()[/b]

[/b] Range("J1").Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ColorIndex = 6

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

End With

Selection.NumberFormat = "@"

ActiveCell.FormulaR1C1 = "Stock Util"

Range("J2").Select

ActiveCell.FormulaR1C1 = "=INDEX('DATOS LOG'!C9,MATCH(RC1,'DATOS LOG'!C1,0),1)"

A = 2

While Cells(A, 1) <> ""

A = A + 1

Wend

Range("J2").Select

Selection.Copy

Range(Cells(3, 10), Cells((A - 1), 10)).Select

ActiveSheet.Paste

Range("J:J").Copy

Range("J:J").PasteSpecial xlPasteValues

Range("J:J").Replace What:="#N/A", Replacement:=""

End Sub

El problema es cuando esto se multiplica por muchas columnas a copiar y encima son doscientas mil líneas, en vez de 30.

El proceso se eterniza, dejando, además, sin recursos al ordenador y al operario (o sea yo) cruzado de manos durante media hora... Y no es lo que se espera de un proceso automático.

Por ello, os preguntaba con toda la ilusión del mundo, si conocéis algún modo o forma en VBA que haga lo mismo sin tener que acudir a la formulación y que acorte sustancialmente los tiempos, cuando se opera con grandes BBDD y/o con tablas (archivos planos) con mucha información.

Estoy abierto a cualquier método, sistema o idea que se pueda emplear en ello.

Sinceras gracias de antemano y mis respetos por vuestra enorme labor de ayuda y apoyo a los neófitos en estas lides.

JK

Ejemplo Juan Karlos.xls

bigpetroman · el 2 de julio

Amigo Juan Karlos, anexo el archivo, te dejo 3 macros, la primera es simplemente comentarios en tu macro de como mejorarla, la segunda es la misma macro mejorada, y la tercera es la mejor y creo debería correr muchos mas rápido, prueba y nos avisas, suerte

Ejemplo Juan Karlos_SM.xls

Juan Karlos · el 3 de julio

Millón de gracias Mr. Bigman Pedro:

Por la rápidez y por la sencillez de tu exposición y solución a mi problema.

Corro a aplicar la solución a una de las herramientas de actualización que más demora tienen y os cuento.

No pensaba que la resolución podría ir de la mano del "BuscarV", que por aquello de que te obligaba a que el dato de referencia tenía que estar a la fuerza a la izquierda siempre del dato a buscar, no me ha hecho nunca mucha gracias y por ello el utilizar en su lugar el "Indice/Coincidir".

Lo dicho, no me entretengo... Mil sinceras gracias y un muy cordial y atento saludo.

Hablamos...

Una vez realizada la comprobación práctica...

Bueno... Pues... Mi gozo en un pozo, como decimos por aquí...

Para hacernos una idea en realizar esto:

A = Range("A" & Rows.Count).End(xlUp).Row

Range("K2:K" & A).ClearContents

Filafin = Workbooks("DATOS_BPG.xlsm").Worksheets("04").Range("A" & Rows.Count).End(xlUp).Row

Range("K2:K" & A).Select

Selection.FormulaArray = _

"=IF(ISERROR(VLOOKUP(R2C60:R" & A & "C60,[DATOS_BPG.xlsm]04!R1C1:R" & Filafin & "C35,9, FALSE)),0,VLOOKUP(R2C60:R" _

& A & "C60,[DATOS_BPG.xlsm]04!R1C1:R" & Filafin & "C35,9, FALSE))"

'Range("K:K").Select

Range("K2:K" & A).Value = Range("K2:K" & A).Value

Range("A1").Select

Con algo más de 10.500 líneas, tarda 1 minuto con 19 segundos

Y en realizar esto otro:

[code]

Cells(2, 11).Select

Selection.FormulaR1C1 = "=INDEX([DATOS_BPG.xlsm]04!C9,MATCH(RC[49],[DATOS_BPG.xlsm]04!C31,0),1)"

Selection.Copy

Range(Cells(3, 11), Cells((A - 1), 11)).Select

ActiveSheet.Paste

Range("K:K").Copy

Range("K:K").PasteSpecial Paste:=xlPasteValues

Para el mismo número de líneas, tan solo tarda 31 segundos. O~o

Me imagino que tarda más por la comprobación que hace el "buscarV" de si es error o no y eso...

Pues bien que lo siento... Pero la diferencia es demasiado grande, hasta para ponerla en duda.

Millón de gracias por vuestro tiempo, de verdad que sois un grupo extraordinario y con una respuesta hyper-rápida, con soluciones viables y muy prácticas.

Seguiré buscando, por si encuentro algún método en programación vba, que sustituya a la formulación (el copiar miles de fórmulas a la vez siempre va a llevar mucho más tiempo que el dar un valor determinado directamente), si lo hubiera y realmente existe algún metodo que haga algo así...

También me ha parecido ver de refilón algo a "Defrag..." o algo así, que puede ser parecido a "BuscarV" y/o "Indice/Coincidir".

Muchas y sinceras gracias de nuevo Pedro "El Grande" y el mejor de mis abrazos para Tí y el resto del Equipo.

JK