none
Formula y macro RRS feed

  • Pregunta

  • Hola a todos

    Que bueno encontrar este grupo, aunque estoy confundido entre los foros, estoy buscando algun foro o grupo de excel y vba y este es lo mas parecido, asi que si mi consulta no corresponde me pueden orientar al correcto. se me presenta en estos dias el siguiente inconveniente.

    Por medio de una macro listo los nombres de los archivos que hay en una carpeta, e identifico la cantidad de filas en que hay en un rango en la hoja1.


    Ejemplo
    Nombres archivos                         cantidad de filas
    Gestor expedientes 1.xlsm                50
    Gestor expedientes 2.xlsm                20
    Gestor expedientes 3.xlsm                80
    Gestor expedientes 4.xlsm                15

    Luego

    1) en una celda concateno los datos:
     ="=BUSCARV(H7;"&"'["&Q1&"]"&"BD'!$H$7:$H$"&Q3&";1;FALSO)"

    H7 es un número de expediente
    Q1 es el nombre del archivo
    Q3 es la cantidad de filas


    2) y obtengo lo siguiente:
    =BUSCARV(H7;'[Gestor expedientes 1.xlsm]BD'!$H$7:$H$8;1;FALSO)

    al correr la macro que tengo, copio y pego como valores las formula del resultado 2 en donde necesito usarla, pero me queda como si fuera texto y no realiza la busqueda, entonces tuve que separar la macro en 2 partes, la primera hasta que copia la formula como valores. Luego manualmente me ubico en la celda y oprimo la tecla "F2" y luego enter y ahí si calcula. y la otra macro continua con lo que sigue.                                                                                                                                                                                                                                                                                                                         Tambien intente la opcion de adicionarle al inicio "aaa"

    "aaa"&"=BUSCARV(H7;'[Gestor expedientes 1.xlsm]BD'!$H$7:$H$8;1;FALSO)"

    y cuando grabo la macro y reemplazo las "aaa" por nada queda la formula lista y realiza la busqueda; pero cuando ejecuto la macro no reemplaza "aaa" y no funciona.

    tambien intente pasar los datos a una variable string pero al pasarla a la celda de excel con la formula, lleva el nombre de la variable y no el valor que contiene

    agradezco de antemano su ayuda

     gracias

    nelsonb

    • Cambiado webJose miércoles, 16 de mayo de 2012 14:55 (De:Desarrollo Web General)
    martes, 15 de mayo de 2012 15:30

Todas las respuestas

  • Pues no veo cómo un foro de desarrollo web es lo más parecido a Excel, pero en fin, existe el foro "Programación con Microsoft Office" que sería el apropiado para esta consulta (ya lo moví).

    Le adelanto que si usted asigna esa cadena de texto a la propiedad Formula de un objeto Range entonces obtendrá el resultado esperado.

    De todas formas lo dejo en el foro más adecuado para el tema.


    Jose R. MCP

    miércoles, 16 de mayo de 2012 14:54
  • Hola Jose

    Gracias por mover la consulta, no conocia el grupo, soy nuevo en estos grupos, no volvera a suceder.

    intente lo que comentas, pero me da error 1004, Error definido por la aplicación o el objeto

    Range("G7").Formula = "=BUSCARV(H7," & "'[" & Q1 & "]" & "BD'!$H$7:$H$" & Q3 & ",1,FALSO)"

    no logro pasar la cadena de texto del Punto 2) mencionada arriba para que funcione

    ya que la formula que hago es la del punto 1) "=BUSCARV(H7;"&"'["&Q1&"]"&"BD'!$H$7:$H$"&Q3&";1;FALSO)"

    pero no se como pasar el resultado de esa formula a cadena de texto por que cambia cada vez que ejecuto otra macro

    agradezco a quien pueda ayudarme con la forma correcta de hacer esto.

    gracias

    Nelsonb


    Nelson

    miércoles, 16 de mayo de 2012 16:09
  • Pues un simple macro como el siguiente me resultó bien, creo yo.  Nótese que aunque mi sistema utiliza punto y coma (;) para separador de listas, en VBA debo usar la coma o de lo contrario obtengo el mismo error que usted.

    Public Sub Test()
    
    Dim rango As Range
    
        Set rango = ActiveWorkbook.ActiveSheet.Range("B6")
        rango.Formula = "=VLOOKUP(3,A1:A2,1,FALSE)"
    End Sub

    Por cierto, eso de la coma me hace pensar que usted no puede usar los nombres de funciones traducidos.  Seguramente usted debe usar VLOOKUP en vez de BUSCARV.


    Jose R. MCP


    • Editado webJose miércoles, 16 de mayo de 2012 18:14
    miércoles, 16 de mayo de 2012 18:14
  • Hola Jose

    Gracias por la atención a mi consulta, tu ejemplo funciona perfectamente;

    pero creo q me falto mencionar que la formula que concateno: es  1)  ="=BUSCARV(H7;"&"'["&Q1&"]"&"BD'!$H$7:$H$"&Q3&";1;FALSO)"

    2) y obtengo lo siguiente:
    =BUSCARV(H7;'[Gestor expedientes 1.xlsm]BD'!$H$7:$H$8;1;FALSO)

    esta formula la concateno en un libro distinto en el que se realiza la busqueda,  por eso la aparece '[Gestor expedientes 1.xlsm] que es la que  hace referencia al otro libro y BD'!$H$7:$H$8 a la hoja especifica "BD" para este caso.

    por lo tanto cuando utilizo el codigo que me diste y lo adapto a mi necesidad, me vuelve a dar el mismo error 1004.

    sub Micodigo()

    Dim rango As Range
      Set rango = ActiveWorkbook.ActiveSheet.Range("G7")   
    rango.Formula = "=VLOOKUP(H7," & "'[" & Q1 & "]" & "BD'!$H$7:$H$" & Q3 & ",1,FALSE)"  

     '=BUSCARV(H7;'[Gestor expedientes 1.xlsm]BD'!$H$7:$H$8;1;FALSO)"   esta linea es comentario
    end sub

    la celda Q1 tiene el nombre del archivo en el que voy a buscar

    y la celda Q3 tiene el numero de filas final del rango a buscar

    debo utilizar esta formula concatenada porque desde un libro principal ejecuto otra macro que abre los libros que voy utilizando, y la formula obtiene de Q1 el nombre del archivo que esta abierto y de Q3 el numero de filas final en el que voy a buscar.

    Gracias nuevamente y te agradezco mucho tu colaboración

    Nelsonb


    Nelson

    miércoles, 16 de mayo de 2012 19:12
  • Pues no probé con múltiples libros, pero le sugeriría comenzar probando una función de celda sencilla, o inclusive la misma función pero sin múltiples libros, asegurándose que funciona.  Una vez que tiene una versión de un solo libro que funciona, pruebe adicionano el otro libro a ver qué sucede.  Así sabrá exactamente qué parte de la fórmula está siendo rechazada.

    Jose R. MCP

    miércoles, 16 de mayo de 2012 19:37
  • OK Jose

    estare utilizando las recomendaciones y comentando los resultados

    gracias


    Nelson

    miércoles, 16 de mayo de 2012 19:53