viernes, 24 de octubre de 2008

Transferir los datos de un Rango a un Array

Descargar el fichero pasa_matriz.xlsm.

Deseamos transferir los datos que existen en el rango de una hoja a una matriz (ARRAY) mediante una macro. Vamos a verlo por varios métodos. El primer método es el clásico, cargando dato a dato en la matriz. Otro método nos permite cargar la matriz de una sola vez. También vamos a conocer cómo se determina la dimensión de una matriz.

Método Clásico

Podemos utilizar el método clásico que consiste en tomar los datos uno a uno del rango e ir alimentando la matriz. Esto se puede hacer con bucles FOR..NEXT.

Imagine que el rango es B4:D13 y esta lleno de datos (algunos numéricos y otros alfanuméricos). Queremos trasladar todos los datos a la matriz X de dimensión X(1 to 10, 1 to 3).

Utilicemos el método clásico consistente en pasar los datos del rango a la matriz de uno en uno. Esto se hace utilizando, en este caso, dos bucles FOR..NEXT anidados.
Sub pasardatos()
Dim MiMatriz(1 To 10, 1 To 3)
Dim i As Integer, j As Integer
For i = 1 To 10
  For j = 1 To 3
    MiMatriz(i, j) = Worksheets("Hoja1").Cells(3 + i, j + 1).Value
  Next j
Next i
End Sub
El otro método es pasar el rango de datos de una sola vez a la matriz.

Pruebe con esto:
MiMatriz = [B4:D13]
o bien
MiMatriz = Range("B4:D13")
y tambien funciona al reves:
[F4:H13] = MiMatriz
o bien
Range("F4:H13") = MiMatriz
Se ha de tener en cuenta que si declaras la matriz como una matriz dará error.

Declarando la variable como una matriz no funciona.

Me explico. Si prueba las cuatro siguientes macros:
Sub Matrix1()
Dim X(11, 5)
X = Range("B4:D13")
End Sub

Sub Matrix2()
Dim X As Double
X = Range("B4:D13")
End Sub

Sub Matrix3()
Dim X As Variant
X = Range("B4:D13")
End Sub

Sub Matrix4()
Dim X
X = Range("B4:D13")
End Sub
comprobara que las dos primeras macros dan error. Las que funcionan bien son la tercera y la cuarta.

Si lo que queremos es tomar un valor de la matriz (por ejemplo el primero, el de la celda B4) y luego dejarlo en la celda H1 hacemos lo siguiente.
Sub test()
Dim x
x = [B4:D13]
[H1]= x(1, 1)
End Sub

¿Cómo se puede conocer la dimensión de la matriz creada?

Supongamos que creo una función, como esta:
Function MiRef(Rng)
Dim X
X = [Rng]
'......
End Function
Para tratar los elementos de la matriz debo conocer su dimensión. Por
ejemplo, sería interesante saber si tiene 10 filas y 4 columnas, o la
dimensión de que se trate. ¿Cómo saber esto?.

Disponemos de dos métodos:

1) Tomar las dimensiones del rango a la hora de asignar:
Function test1(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = rng.Columns.Count
y = rng.Rows.Count
test1 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function
2) Tomar las dimensiones de la matriz:
Function test2(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = UBound(Matriz, 2)
y = UBound(Matriz, 1)
test2 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function

También puede interesarle ...

31 comentarios:

  1. Hola, una pregunta, tengo un "nombre" en vez de un rango, es posible asignar este "nombre" a un array?

    ResponderEliminar
  2. Hola M.Vizcarra.

    Prueba esta macro:

    Sub ConRangos()
    Dim A
    Names.Add Name:="numeritos", _
    RefersTo:="=Hoja1!$D$5:$F$13"
    A = [numeritos]
    [I5:K13] = A
    End Sub

    Por unos números en el rango: D5:F13. La macro crea el nombre de rango "numeritos". Luego asigna el rango a la matriz A, y finalmente copia la matriz A en el rango I5:K13.

    Espero que te sirva este ejemplo.

    Un saludo.

    Adolfo Aparicio

    ResponderEliminar
    Respuestas
    1. hola adolfo
      consulta al filtro de una tabla se le puede asignar como rango matriz y reemplazar la columna adyacente con otro valor

      Eliminar
  3. espectacular.
    nunca había visto estas ideas y procedimientos sobre matrices.

    ResponderEliminar
  4. Hola felicitaciones por compartir tus conocimientos, te lo agradezco. Estoy intentando hacer una macro y tu codigo expuesto me ha dado luces pero aun no lo puedo hacer solo, espero me ayudes. Quiero hacer lo sgte:
    En una Hoja1 tengo una columna con datos(numeros enteros, no muchos pero no se sabe cuantos) quiero pasar estos numeros a un array o matriz unidimensional, para luego copiar cada numero(uno por uno) a una celda especifica de la Hoja2 esto para luego mandar a imprimir la Hoja2(Osea si pase al array 10 numeros, habran 10 impresiones). El problema es no se hasta donde recorrer hacia abajo la columna capturando cada numero en la Hoja1 para luego ponerlo en el array. Como? recorro la columna hasta detenerme en la primera celda vacia y saber cuantos elementos he recorrido y crear un array de ese tamaño. Y otra cosa como haria para elegir en que impresora quiero imprimir ya que tengo 2 opciones. Ojala me puedeas comprender y ayudar. Gracias de antemano.

    ResponderEliminar
  5. Hola Marco Antonio.

    Para bajar por una columna hasta llegar a una celda vacía puedes ver el siguiente Post:

    estructuras-repetitivas-do-while-loop

    Existe otro truco que consiste en poner en una celda la fórmula =contar(A:A). Si esta fórmula la pones en la celda B1 y en la columna A pones los numeritos que deseas contar, habrás conseguido que en B1 se vea cuantos números tienes en la columna A. Esto obliga a no escribir otras cosas en la columna A. Revisa =CONTARA ya que esta función cuenta números y texto, por si fuera de tu interés.

    Luego haces una macro con un FOR NEXT, donde ya sabes cuantos números tienes, porque te lo dice la celda B1.

    Lo de las impresoras, puedes intentar, hacer una macro con grabadora que imprima por una de ellas, y luego otra que imprima por la otra. Analizando el código podrás ver como manejarlas.

    Un saludo.

    ResponderEliminar
  6. Hola Adolfo, buenas tardes.

    Muy interesante el Blog. Tengo una inquietud y es la siguiente.
    Deseo que el arreglo sea recorrido y cada vez que encuentre un valor de un criterio de búsqueda, me traiga los 10 primeros registros y los copie en otra Hoja del mismo libro. Tengo el sgte. codigo:

    Sub BuscarDatos()
    Dim arreglo As Variant
    Dim libro As Worksheet
    Dim x As Integer, i As Integer

    x = 2 ' Fila 2

    arreglo = Range("A1:D10").Value
    ' Selecciona libro
    Set libro = ThisWorkbook.Worksheets("Hoja2")

    'Búsqueda dentro del arreglo.
    For i = 1 To UBound(arreglo)
    ' Criterio
    If UCase(ActiveCell) Like "*" & UCase(ActiveSheet.Range("F1")) & "*" Then
    'Pega a partir de la columna 2
    libro.Cells(x, 2) = arreglo(i, 1)
    libro.Cells(x, 2).Offset(0, 1) = arreglo(i, 2)
    libro.Cells(x, 2).Offset(0, 2) = arreglo(i, 3)
    libro.Cells(x, 2).Offset(0, 3) = arreglo(i, 4)
    'MsgBox arreglo(i, 1)
    x = x + 1
    End If
    Next
    End Sub


    Los datos irían de la siguiente manera:
    http://img32.imageshack.us/img32/6660/imgpl.jpg

    Me puede colaborar. Gracias!

    ResponderEliminar
  7. Hola, Se pueden manipular los rangos de excel como Ud lo demuestra con claridad, sin embargo no se si se pueda hacer los mismo con un arreglo, es decir x ejemplo mi arreglo se llama datos y requiero el rango range(datos(2,1):datos(2,30)) donde por supuesto el arreglo datos es mucho mas grande. (la idea es extraer una parte sin necesidad de recorrerlo)

    ResponderEliminar
  8. Buenos días.

    Muy agradecido por encontrar a gente como tu, que compartes tus conocimientos en la nube, para que todos podamos ser ayudados y aprender.

    Tengo una duda sobre matrices.

    He oído por ahí que es mucho más rápido trabajar con matrices dentro el código que hacerlo directamente en las celdas de las hojas de excel. Por ello pasé un grupo de celdas a una matriz (lo he hecho con un While, que me permite transponer filas x columnas, aunque me ha encantado tu explicación: simple y rápida. Ya veremos si lo cambio). Luego mi intención es ir buscando valores en estas celdas y devolver la posición dónde encuentro el valor. Per ahí viene el lío. Yo quería hacerlo sobre la matriz que he preparado, pero no encuentro la forma de utilizar una función nativa de búsqueda (tipo find). O lo hago con un bucle sobre la matriz, o utilizo la función nativa find sobre las celdas de una hoja. ¿Hay alguna función nativa de búsqueda sobre matrices dentro del código de la macro, sin tener que ir a las celdas?

    Muchas gracias de antemano y saludos!

    Alex

    ResponderEliminar
  9. Hola Jandro.
    Prueba este enlace:

    http://www.excelavanzado.com/2013/10/ordenar-un-millon-de-valores.html

    Un saludo.

    ResponderEliminar
    Respuestas
    1. Saludos, como podría quedar una rutina que cree 6 tablas de bingo en una hoja pero que los números aleatorios no se repitan en ninguna tabla osea no se repitan como columna por ejemplo la columna "B" de la primera tabla que quedaría en la parte superior izquierda no posea los mismos números de columa "B" de la segunda tabla y así hasta la 6ta tabla, esto tmabién con las columnas "I", "N", "G", "O". gracias de antemano.

      Eliminar
  10. HOLA soy nuevo en esto de excel y las macros, tengo una duda, me cree un libro en el cual la columna E3 a E30 contiene los nombres de ciertos gastos o compañias (luz agua hipoteca)y la columna J3 a J30 las fechas de pagos de recibos o ingresos de facturas( la columna J esta con formato fechalarga.
    quisiera saber si hay alguna macro para que cuando llegue la fecha indicada en la columna J me avise con una ventana diciendome que cierto gasto vencio de la columna E( ejem E6 "agua" vence hoy ).
    use una macro que encontre.


    Private Sub Workbook_Open()
    Dim Fecha1, Fecha2 As Date


    'si hay valores en J4 se almacena en Fecha1
    If Range("J4").Value <> "" Then
    Fecha1 = Range("J4").Value
    End If
    'ahora guardo la fecha del sistema
    Fecha2 = Date
    'y comparo:
    If Fecha1 = Fecha2 Then
    MsgBox "Alerta, Hay vencimientos Pendientes", vbInformation, "RECORDATORIO"

    End If
    End Sub


    Pero solo me funciona en la celda J4 y no me avisa de que tipo de vencimiento es ( columna E). He probado a cambiar los rangos por ( J3:J30 ) pero me da fallo y no lo admite. Hay forma de poder hacer lo que yo quiero ? Gracias de antemano y felicidades por este blog y compartir con nosotros tus conocimientos.
    Un saludo Angel

    ResponderEliminar
  11. Hola adolfo buenas tardes!
    tengo una columna formada por 70000 datos y quiero format otra matria con los promedios de cada 20 datos de la primera si repetir los que ya he promedidado, es decir, tomo los primeros 20, los promedio, se grea el primer valor, luego tomo los 20 siguientes, los promedio y ya tengo el segundo valor, tomo los 20 siguientes para formar el tercero, y asi hasta que llego a los últimos 20 donde estara el dato 70000. Me podria s ayudar por favor?!!

    ResponderEliminar
  12. COMO HAGO UN MACRO QUE ME TOME DATOS DE EXCEL Y LOS PASE A VISUAL BASIC

    ResponderEliminar
    Respuestas
    1. sub ejemplo()
      'En esta parte lees el valor de la celda A1 y lo asignas a la variable a y asi, copia y pega tal cual esto y veras de lo que hablo
      a=cells(1,1).value
      stop
      end sub

      Eliminar
  13. Hola Xpo.
    Lo que necesitas hacer es algo parecido a una media móvil pero en lugar de sacar la media móvil completa únicamente necesitas que te de la media movil de 20 (MM20) cada 20 datos.
    Esto se puede lograr de forma manual, con fórmulas o con macros.
    Voy a crearte una entrada en el blog que te lo cuente.
    Un saludo.

    ResponderEliminar
  14. hola, he estado probando la manera como se indica para asignar un rango a una matriz pero me ha arrojado un error indicando que no se puede asignar de esa forma, estoy intentando hacer una función que entregue las constantes de la línea de regresión de un rango de datos seleccionado.
    ojala cuente con vuestro apoyo

    ResponderEliminar
  15. hola,

    he leído el blog y me ha dado muchas luces en varios problemas, sin embargo desde hace un tiempo tengo una duda:

    ¿Es posible separar el texto de una cadena en una matriz?

    he intentado con la función Split, pero las cadenas que debo evaluar no tienen ningún delimitador.

    agradezco su ayuda enormemente

    ResponderEliminar
  16. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  17. Espero puedan ayudarme, Buen día
    Necesito imprimir los valores de un vector en un userfrom la idea es:
    sub principal()
    dim vect(3) as variant
    vect(1)=1
    vect(2)=2
    vect(3)=3

    UserFrom1.Show
    end sub

    Private Sub CommandButton1_Click()
    'Aqui creo yo es donde deberia imprimirse el vector "vect" en un listbox pero nose como
    End Sub

    Muchas gracias por leerme, espero puedan ayudarme gracias

    ResponderEliminar
  18. Hola,cómo determino el tamaño de una tabla dinámica con vba??

    ResponderEliminar
  19. Hola,cómo determino el tamaño de una tabla dinámica con vba??

    ResponderEliminar
  20. Ayuda: no me acuerdo cómo ordenar una tabla de 20 datos numéricos que están distribuidos en 5 columnas y seis filas. Gracias.

    ResponderEliminar
  21. Hola, por favor necesito de su ayuda. Quiero crear un archivo de texto, el nombre del archivo es el valor de una celda y el contenido de dicho archivo es otro valor de una celda.

    Actualmente tengo este macro, pero me sale error 400, pero mando un MsgBox con el valor de las variables y si me muestra dicho valor

    Sub pruebitaXML()
    Dim FSO1 As Object
    Dim ArchivoTexto1 As Object
    Dim cont As Integer
    For Each d In Worksheets("Hoja2").Range("c4:c5") 'd tiene el contenido del archivo
    Set FSO1 = CreateObject("Scripting.FileSystemObject")
    For Each c In Worksheets("Hoja2").Range("c5:c4") 'c tiene el nombre del archivo
    Set ArchivoTexto1 = FSO1.CreateTextFile("\\192.148.1.15\img_cont\" & c)
    Next c
    ArchivoTexto1.WriteLine ActiveSheet.Range(c).Value
    ArchivoTexto1.Close
    Next d
    Set ArchivoTexto1 = Nothing
    Set FSO1 = Nothing
    End Sub

    Muchas gracias cualquier aporte

    ResponderEliminar
  22. Buenos días y para saltar un campo que esté en blanco (sin valores) y continue moviendo el resto a la próxima posición libre es posible?ç
    Muchas gracias¡¡

    ResponderEliminar
  23. Buenas tardes,

    Si me pueden apoyar, tengo un rango de la forma $A:$D pero evidentemente no voy a recorrer todas las celdas, por lo que requiero copiar dicho rango sin tener en cuenta las celdas sin valores (Tenia pensado usar .End(xlUp).Row )

    ResponderEliminar
  24. buenas noches
    si me pueden ayudar tengo una macro en la que quiero editar el caption o texto que contiene una etiqueta pero solo e logrado seleccionarla el detalle esta en que estoy trabajando en powerpoin ya importe y almacene los datos que necesitaba de un libro exel,pero quiero automáticamente vaciar el dato que tengo en las variable a la etiqueta que tengo en la hoja o presentación de powerpoin.

    si me dan una idea se los agradecería

    - E logrado seleccionar con: 'ActivePresentation.Slides(1).Shapes.Range (array ("cedula")).select

    ResponderEliminar
  25. Tengo una lista de numeros del 1 al 20 (por ejemplo) en la columna A y deseo agruparlos en las columnas c,d,e,f, y g
    de 5 en 5, pero quiero que se pasen automaticamente.
    Como le hago? Por ejemplo : https://goo.gl/photos/P5KctXZuct2jiiwf6

    ResponderEliminar
  26. Estoy usando una macro que me rellena un documento pdf desde una hoja excel, pero en esta hoja voy introduciendo datos a través de un formulario y quisiera pasar al formulario pdf los datos d cada registro. como bien que el array esta predefinido en la macro, no se como hacer para que cambie este rango en función del registro deseado.

    La macro es la siguiente:

    Sub PasarDatosaPdf()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set h2 = Sheets("Hoja1")
    '
    celdas = Array("A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2")
    '
    ruta = "D:\cosas de gentes\VATELIA\NUEVA DOCUMENTACION\"
    nomb = "Formulario Orden de fabricacion"
    ActiveWorkbook.FollowHyperlink ruta & nomb & ".pdf" '
    Application.Wait Now + TimeValue("00:00:03")
    For I = LBound(celdas) To UBound(celdas)
    DoEvents
    SendKeys "{TAB}", True
    DoEvents
    h2.Range(celdas(I)).Copy
    DoEvents
    SendKeys "^v", True
    DoEvents
    Next
    '
    Application.ScreenUpdating = True
    MsgBox "Se enviaron los datos al pdf"
    End Sub

    Lo que necesito es cambiar de forma dinámica la linea de código celdas = Array("A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2") donde las celdas son otras...

    O quizás algún otro método para rellenar el formulario PDF

    Muchas gracias

    ResponderEliminar