Copiar fórmula en filas alternas

eedomfr · el 23 de diciembre

Hola:

Tengo una serie de datos en una "Hoja2".

El caso es que en "Hoja1" necesito crear una tabla con el doble de filas, de manera que

fila n: una fórmula que se refiere a la una fila x de la hoja2

fila n+1: otra fórmula que se refiere también a la fila x de la hoja2

He conseguido hacer lo siguiente:

- He creado una hoja1 con n filas, sólo con la fórmula1, arrastrando, y luego con esta macro

Sub InsertRows1()

    Dim lIni As Long

    Dim lTot As Long

    Dim lRow As Long

    lTot = Selection.Rows.Count + Selection.Row

    lIni = Selection.Row + 1

    For lRow = lTot To lIni Step -1

    Rows(lRow).EntireRow.Insert

    Next lRow

End Sub[/CODE]

He conseguido crear una fila en blanco entre cada fórmula

Pero el problema es que no consigo ver cómo rellenar las filas vacías para que me referencia bien a la fórmula

Si la creo, por ejemplo, en la fila 3, y copio y pego en el siguiente hueco en blanco, me sale referenciado a la fila 5, en lugar de a la 4 que es lo que yo querría.

¿Cómo puedo hacer?

- Se me ocurría hacer lo mismo con la fórmula2 y luego copiarlas ambas en paralelo y usar "eliminar celdas y desplazar a la izquierda" pero no sé hacerlo en VBA.

El caso, claro, es que son muchísimas celdas, de ahí que no quiera hacerlo a mano

Gracias!!!

el 23 de diciembre

Tu descripción, eedomfr, es muy insuficiente.

Si subes al Foro un archivo con una muestra de lo que tienes y de lo que quieres obtener: ¡Será otro cantar!...

eedomfr · el 23 de diciembre

Hola:

Gracias por la recomendación.

He añadido un excel, espero que se vea.

Tengo una hoja, "Base", en la que tengo los datos de partida (cada ítem ocupa una fila).

Luego tengo que generar una hoja como la que he llamado "Bien", que he tenido que hacer a mano porque no sé generar automáticamente como explicaba antes, en la que de cada ítem de la hoja "Base" tengo que generar varias filas. La cuestión es que si "arrastro" las fórmulas, me sale lo que tengo en la hoja "Mal", no lo que yo pretendo.

El motivo de ello es que excel, al arrastrar, si te mueves 6 filas, entiende que la celda origen también se ha movido 6, cosa que aquí no sucede porque yo "genero" 3 filas para cada una de la hoja base, por lo que en realidad lo que tengo son 2 filas, no 6. Entiendo que esto sólo se podrá hacer con VBA, pero no se me ocurre cómo (tampoco es que domine mucho, en realidad nada).

Espero haberme aclarado ahora.

ejemplo.xls

el 24 de diciembre

Intenta incorporando en un módulo común algo como esto:

Sub GeneraInfo()
'-------------------
' By Cacho Rodríguez
'-------------------
Dim mySh As Worksheet, C As Range, Q1%
Application.ScreenUpdating = False
Set mySh = ActiveSheet
Q1 = Cells(1, Columns.Count).End(xlToLeft).Column - 1
Worksheets.Add
mySh.Range("a1", mySh.Cells(1, 1 + Q1)).Copy [a1]
For Each C In mySh.Range("a2", mySh.Cells(Rows.Count, "a").End(xlUp))
  With Cells(Rows.Count, "a").End(xlUp)
    C.Copy .Offset(3)
    .Offset(3, 1).Resize(2, Q1) = WorksheetFunction.Transpose(Array("=LEFT('" & _
      mySh.Name & "'!B" & C.Row & ",3)", "=RIGHT('" & _
      mySh.Name & "'!B" & C.Row & ",2)"))
  End With
Next C
[2:3].Delete
Application.ScreenUpdating = True
End Sub[/PHP]

Saludos, Cacho R.

mjrofra · el 24 de diciembre

hola,

otra alternativa,espero sea de ayuda .

ejemplo.xls

eedomfr · el 24 de diciembre

Intenta incorporando en un módulo común algo como esto:
Sub GeneraInfo()
'-------------------
' By Cacho Rodríguez
'-------------------
Dim mySh As Worksheet, C As Range, Q1%
Application.ScreenUpdating = False
Set mySh = ActiveSheet
Q1 = Cells(1, Columns.Count).End(xlToLeft).Column - 1
Worksheets.Add
mySh.Range("a1", mySh.Cells(1, 1 + Q1)).Copy [a1]
For Each C In mySh.Range("a2", mySh.Cells(Rows.Count, "a").End(xlUp))
  With Cells(Rows.Count, "a").End(xlUp)
    C.Copy .Offset(3)
    .Offset(3, 1).Resize(2, Q1) = WorksheetFunction.Transpose(Array("=LEFT('" & _
      mySh.Name & "'!B" & C.Row & ",3)", "=RIGHT('" & _
      mySh.Name & "'!B" & C.Row & ",2)"))
  End With
Next C
[2:3].Delete
Application.ScreenUpdating = True
End Sub[/PHP]
Saludos, Cacho R.

Gracias!

No obstante, aplicándolo en ejemplo.xls obtengo un error 1004 en tiempo de ejecución (office 2003).

en la siguiente parte:

[CODE] .Offset(3, 1).Resize(2, Q1) = WorksheetFunction.Transpose(Array("=LEFT('" & _
mySh.Name & "'!B" & C.Row & ",3)", "=RIGHT('" & _
mySh.Name & "!B" & C.Row & ",2)"))[/CODE]

[color=blue]- - - - - Mensaje combinado - - - - -[/color]

hola,
otra alternativa,espero sea de ayuda .

Gracias!

Me es muy útil y viene muy bien documentado (el de Cacho R no soy capaz de entenderlo )

Pero lo cierto es que me gustaría tener la opción de que lo que me ponga sea la fórmula vinculada, no el valor directamente. ¿Cómo puedo hacer eso? Me parece que es lo que hace la función de Cacho R, pero no tengo ni idea de dónde falla ni por qué...

mjrofra · el 24 de diciembre

...lo cierto es que me gustaría tener la opción de que lo que me ponga sea la fórmula vinculada, no el valor directamente...

hola,

modifica la macro para que en lugar de copiar el dato de la tabla original, almacene la fórmula que te interesa con la referencia a dicho dato, algo así:

Public Sub Prueba2()    Const HOJA_ORIGEN = "BASE"

    Dim columnas As Long    ' número de columnas resultante, igual al número de columnas originales

    Dim filas As Long       ' número de filas resultante, igual al número de filas originales por 3

    Dim columna As Long     ' contador para iterar las columnas

    Dim fila As Long        ' contador para iterar las filas

    Dim subfila As Long     ' posición en cada grupo de elementos, cada fila genera 3 subfilas:

                            '   subfila 1 = izquierda(dato, 3)

                            '   subfila 2 = derecha(dato, 2)

                            '   subfila 3 = "" (fila en blanco)



    With Sheets(HOJA_ORIGEN).Range("a1").CurrentRegion

        ' total de filas resultantes, la cantidad de datos originales, para

        ' cada dato original se crean 3 subfilas. Se resta la primera fila

        ' por ser el encabezado

        filas = (.Rows.Count - 1) * 3

        ' las columnas totales en los datos

        columnas = .Columns.Count

    End With


    ' se dimensiona el array resultado con las filas y columnas totales

    ReDim resultado(1 To filas, 1 To columnas)


    ' crea la formula que referencia a los encabezados que se encuentran

    ' en la fila 1 de la tabla de la hoja de origen

    For columna = 1 To columnas

        resultado(1, columna) = "=" & HOJA_ORIGEN & "!r1c" & columna

    Next columna


    ' se recorre el total de filas resultantes para crear la fórmula

    ' de acuerdo con la subfila y con la columna en la que se carga.

    ' se inicia en la segunda fila pues la primera es de encabezados

    For fila = 2 To filas

        For columna = 1 To columnas


            ' calcula la subfila:   subfila 1 = izquierda(dato, 3)

            '                       subfila 2 = derecha(dato, 2)

            '                       subfila 3 = "" (fila en blanco)

            subfila = ((fila + 1) Mod 3) + 1


            If subfila = 1 Then

                ' para la columna 1 se copia el dato tal cual

                ' para las demás se copia izquierda(dato, 3)

                If columna = 1 Then

                    resultado(fila, columna) = "=" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna

                Else

                    resultado(fila, columna) = "=Left(" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna & ",3)"

                End If

            ElseIf subfila = 2 Then

                ' No se copia nada de la primera columna.

                ' de las siguientes se copia derecha(dato, 2)

                If columna > 1 Then

                    resultado(fila, columna) = "=Right(" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna & ",2)"

                End If

            End If

        Next columna

    Next fila


    ' se crea una nueva hoja y se copia allí la tabla resultante

    Sheets.Add.Range("a1").Resize(filas, columnas) = resultado


End Sub[/CODE]

el 24 de diciembre

¿Puedes creer, eedomfr, que acabo de verificar que el Foro "se deglutió" una comilla y es por ello que no te ha funcionado correctamente?...

Para evitar otras "cosillas" te adjunto el archivo correspondiente.

Saludos, Cacho R.

Ejemplo-2.xls

eedomfr · el 26 de diciembre

hola,

modifica la macro para que en lugar de copiar el dato de la tabla original, almacene la fórmula que te interesa con la referencia a dicho dato, algo así:

Public Sub Prueba2()    Const HOJA_ORIGEN = "BASE"

    Dim columnas As Long    ' número de columnas resultante, igual al número de columnas originales

    Dim filas As Long       ' número de filas resultante, igual al número de filas originales por 3

    Dim columna As Long     ' contador para iterar las columnas

    Dim fila As Long        ' contador para iterar las filas

    Dim subfila As Long     ' posición en cada grupo de elementos, cada fila genera 3 subfilas:

                            '   subfila 1 = izquierda(dato, 3)

                            '   subfila 2 = derecha(dato, 2)

                            '   subfila 3 = "" (fila en blanco)



    With Sheets(HOJA_ORIGEN).Range("a1").CurrentRegion

        ' total de filas resultantes, la cantidad de datos originales, para

        ' cada dato original se crean 3 subfilas. Se resta la primera fila

        ' por ser el encabezado

        filas = (.Rows.Count - 1) * 3

        ' las columnas totales en los datos

        columnas = .Columns.Count

    End With


    ' se dimensiona el array resultado con las filas y columnas totales

    ReDim resultado(1 To filas, 1 To columnas)


    ' crea la formula que referencia a los encabezados que se encuentran

    ' en la fila 1 de la tabla de la hoja de origen

    For columna = 1 To columnas

        resultado(1, columna) = "=" & HOJA_ORIGEN & "!r1c" & columna

    Next columna


    ' se recorre el total de filas resultantes para crear la fórmula

    ' de acuerdo con la subfila y con la columna en la que se carga.

    ' se inicia en la segunda fila pues la primera es de encabezados

    For fila = 2 To filas

        For columna = 1 To columnas


            ' calcula la subfila:   subfila 1 = izquierda(dato, 3)

            '                       subfila 2 = derecha(dato, 2)

            '                       subfila 3 = "" (fila en blanco)

            subfila = ((fila + 1) Mod 3) + 1


            If subfila = 1 Then

                ' para la columna 1 se copia el dato tal cual

                ' para las demás se copia izquierda(dato, 3)

                If columna = 1 Then

                    resultado(fila, columna) = "=" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna

                Else

                    resultado(fila, columna) = "=Left(" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna & ",3)"

                End If

            ElseIf subfila = 2 Then

                ' No se copia nada de la primera columna.

                ' de las siguientes se copia derecha(dato, 2)

                If columna > 1 Then

                    resultado(fila, columna) = "=Right(" & HOJA_ORIGEN & "!r" & (fila + 4) \ 3 & "c" & columna & ",2)"

                End If

            End If

        Next columna

    Next fila


    ' se crea una nueva hoja y se copia allí la tabla resultante

    Sheets.Add.Range("a1").Resize(filas, columnas) = resultado


End Sub[/CODE]

Gracias!

Estoy probando. Cuando pones en el código & "!r1c", ¿esto qué quiere decir? Porque si voy a la celda en cuestión veo que pone "$a$1" y así sucesivamente...

[color=blue]- - - - - Mensaje combinado - - - - -[/color]

Otra pregunta:

Me da un error si el nombre de la hoja es del tipo "NOMBRE_OTRACOSA". ¿Puede ser por el guión bajo?

[color=blue]- - - - - Mensaje combinado - - - - -[/color]

Otra pregunta:

Me da un error si el nombre de la hoja es del tipo "NOMBRE_OTRACOSA". ¿Puede ser por el guión bajo?

Al darle a depurar me sale, en la última fila (Sheets.Add.Range.......) error 1004

mjrofra · el 29 de diciembre

Gracias!
Estoy probando. Cuando pones en el código & "!r1c", ¿esto qué quiere decir? Porque si voy a la celda en cuestión veo que pone "$a$1" y así sucesivamente...

Hola,

es el tipo de referencia R1C1 en lugar de A1, las filas y las columnas son numeradas. En VBA puedes usar cualquiera de las dos y en la hoja de cáculo se mostrará la opción que tienes seleccionada en tus opciones (R1C1 o A1).

About cell and range references - Support - Office.com

Otra pregunta:
Me da un error si el nombre de la hoja es del tipo "NOMBRE_OTRACOSA". ¿Puede ser por el guión bajo?

Al darle a depurar me sale, en la última fila (Sheets.Add.Range.......) error 1004

Pues no sé. Revisa el adjunto y si puedes sube un archivo en el que se pueda reproducir el error que mencionas. Le he hecho una corrección a la macro pues sí presentaba errores cuando el nombre de la hoja tiene espacios, para lo cual se debe rodear de comillas sencillas. La macro es la misma, salvo que corrige la situación que te menciono.

ejemplo.xls