Error al quiere poner una fórmula en una celda desde vba
Tengo una hoja la cual se ha construido con ayuda de este foro en otras publicaciones.
La misma llama a un libro y copia todos sus datos a una hoja temporal gracias a @Elsa Matilde luego en esta hoja temporal se inserta una columna en B. Esta columna insertada debe usarse una fórmula provista por Dante Amor. La cual debe repetirse desde la columna b2, hasta la ultima fila con datos.
El problema es que al colocar la fórmula en vba para que sea colocada en las celdas, la misma no funciona arrojando un error
adjunto la macro hasta el momento.
Dim RutaArchivo As String
Dim nombre As String 'nombre de la hoja activa
Dim nombre1 As String 'nombre de la hoja activa
Dim RangoDatos As Range
Dim uFila As Long
On Error Resume Next
RutaArchivo = Application.GetOpenFilename(Title:="Carga Info Proveniente De Laudus", _
filefilter:="Excel Files (*.xlsx), *.xlsx")
If RutaArchivo <> "Falso" Then
nombre = ActiveWorkbook.Name 'nombre del libro activo
Workbooks.Open RutaArchivo
nombre1 = ActiveWorkbook.Name 'nombre del libro que se abre
Set RangoDatos = Workbooks(nombre1).ActiveSheet.UsedRange 'rango completo que se copiará
'hoja = ThisWorkbook.ActiveSheet
' Hoja.Range("A1:H" & uFila). Copy
RangoDatos. Copy
Workbooks(nombre). Activate 'se activa el libro destino
Sheets.Add.Name = "temporal" 'se agrega una hoja. Queda como hoja activa
ActiveSheet. Paste 'se pega la copia a partir de A1
Workbooks(nombre1). Close False 'opcional: se cierra el libro buscado sin guardar cambios
With temporal.Range("A2").CurrentRegion
uFila = temporal.Range("A" & Rows.Count).End(xlUp).Row ' determina la ultima fila con datos
Call insertacolumna 'llama a una insercion de columna B
temporal.Range("B2:B" & uFila).Formula= "=SI(LARGO(A2)-LARGO(SUSTITUIR(A2;"\";""))>1;EXTRAE(A2;ENCONTRAR("\";A2;2)+1;9);ESPACIOS(EXTRAE(SUSTITUIR(A2;" ";REPETIR(" ";99));2;99)))"
End With
End If
3 Respuestas
Cambia estas líneas:
uFila = .Range("A" & Rows.Count).End(xlUp).Row ' determina la ultima fila con datos
Call insertacolumna 'llama a una insercion de columna B
'temporal.Range("B2:B" & uFila).Formula= "=SI(LARGO(A2)-LARGO(SUSTITUIR(A2;"\";""))>1;EXTRAE(A2;ENCONTRAR("\";A2;2)+1;9);ESPACIOS(EXTRAE(SUSTITUIR(A2;" ";REPETIR(" ";99));2;99)))"Por estas:
With Sheets("temporal")
uFila = .Range("A" & Rows.Count).End(xlUp).Row ' determina la ultima fila con datos
.Range("B2:B" & uFila) = "=IF(LEN(A2)-LEN(SUBSTITUTE(A2,""\"",""""))>1,MID(A2,FIND(""\"",A2,2)+1,9),TRIM(MID(SUBSTITUTE(A2,"" "",REPT("" "",99)),2,99)))"
End With
Por estas:
With Sheets("temporal")
call insertacolumna
uFila = .Range("A" & Rows.Count).End(xlUp).Row ' determina la ultima fila con datos
.Range("B2:B" & uFila) = "=IF(LEN(A2)-LEN(SUBSTITUTE(A2,""\"",""""))>1,MID(A2,FIND(""\"",A2,2)+1,9),TRIM(MID(SUBSTITUTE(A2,"" "",REPT("" "",99)),2,99)))"
End With
A manera de aclaración.
La instrucción "With" no es un bucle, es una estructura.
La instrucción With te permite realizar una serie de instrucciones en un objeto específico sin volver a especificar el nombre del objeto.
En mi ejemplo estoy utilizando el objeto de la hoja Sheets("temporal"), para encontrar la última fila con datos y para poner la fórmula.
Referencia:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement
Pasame la fórmula completa desde la hoja tal como debiera aparecer en la primer celda para que pueda convertirla a VBA. Desde ya te aclaro que dentro de la fórmula, lo que lleva comillas debiera ir entre comillas dobles.
Además a simple vista parece que no necesitas el bucle With...End With ya que buscas el fin del rango directamente desde la col A:
uFila = Sheets("temporal").Range("A" & Rows.Count).End(xlUp).Row ' determina la ultima fila con datos
Call insertacolumna 'llama a una insercion de columna B
Sheets("temporal").Range("B2:B" & uFila).Formula= "=SI(LARGO(A2)-LARGO(SUSTITUIR(A2;"\";""))>1;EXTRAE(A2;ENCONTRAR("\";A2;2)+1;9);ESPACIOS(EXTRAE(SUSTITUIR(A2;" ";REPETIR(" ";99));2;99)))"Y 'temporal' no es una variable, sino el nombre de una hoja por lo que debiera ir como Sheets("temporal")
Sdos y espero tu fórmula para ajustar la instrucción.
Hola mi estimada, la fórmula es esta
=SI(LARGO(A2)-LARGO(SUSTITUIR(A2;"\";""))>1;EXTRAE(A2;ENCONTRAR("\";A2;2)+1;9);ESPACIOS(EXTRAE(SUSTITUIR(A2;" ";REPETIR(" ";99));2;99)))Al colocarla en la hoja me extrae datos en especifico de la columna A
Para pasar una fórmula a VBA lo recomendable es que la coloques en la primer celda de la hoja. Actives la grabadora de macros, te posiciones en la barra de fórmula, presiones Enter y detengas la grabadora.
En un módulo del Editor encontrarás una instrucción como ésta:
[B2].FormulaR1C1 = _
"=IF(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],""\"",""""))>1,MID(RC[-1],FIND(""\"",RC[-1],2)+1,9),TRIM(MID(SUBSTITUTE(RC[-1],"" "",REPT("" "",99)),2,99)))"Como no la necesitas en formato FormulaR1C1, sino como local (Formula), te servirá para guiarte en el reemplazo, donde rigen estas reglas:
- Los nombres de las funciones van en inglés. (Desde la sección 'Funciones' de mi sitio podrás descargar un libro con todas las conversiones de funciones desde el 2007 hasta la fecha).
- lo que va entre comillas, aquí debe ir entre comillas dobles
- se utiliza siempre coma en lugar de punto y coma.
Entonces tu fórmula queda de este modo:
"=IF(LEN(A2)-LEN(SUBSTITUTE(A2,""\"",""""))>1,MID(A2,FIND(""\"",A2,2)+1,9),TRIM(MID(SUBSTITUTE(A2,"" "",REPT("" "",99)),2,99)))"Ahora, en tu macro quedaría de este modo:
Sheets("temporal").Range("B2:B" & uFila).Formula= _
"=IF(LEN(A2)-LEN(SUBSTITUTE(A2,""\"",""""))>1, MID(A2, FIND(""\"", A2, 2)+1,9), TRIM(MID(SUBSTITUTE(A2,"" "", REPT("" "", 99)), 2,99)))"PD) No estoy utilizando el bucle With ... End With porque desconozco como está redactada la macro 'InsertaColumna'. De todos modos, eso no afecta el funcionamiento del proceso para nada ... es solo un modo más abreviado de escribir código sin repetir el texto Sheets("temporal").
* Te recomiendo los videos N° 15 (Fórmulas con VBA) y N° 19 (Bucles) de mi canal.
Sdos!