Cuando Michael hace una operación «Buscar todo» en Excel, el programa muestra una lista de todas las celdas que contienen lo que está buscando.

A Michael le gustaría copiar esa lista de direcciones de celda en otra hoja de trabajo, por lo que se pregunta si hay una manera de copiar la lista al Portapapeles para poder pegarla en una hoja de trabajo.

Hay algunas formas de realizar esta tarea y la mayoría de ellas implican el uso de macros. Sin embargo, antes de llegar a los enfoques basados ​​en macros, echemos un vistazo a la forma en que podría acceder a las direcciones utilizando rangos con nombre y el Administrador de nombres:

  1. Use FindAll como antes, pero no cierre el cuadro de diálogo Buscar y reemplazar.

  2. En la lista de direcciones que se muestra, desplácese hasta la parte inferior, mantenga presionada la tecla Shift y haga clic en la última coincidencia. Excel selecciona todas las celdas coincidentes.

  3. Presione Esc para cerrar el cuadro de diálogo Buscar y reemplazar. Las celdas coincidentes todavía están seleccionadas.

  4. Escriba un nombre en el Cuadro de nombre (justo a la izquierda de la barra de fórmulas y justo encima de la celda A1). Esto crea un rango con nombre que consta de todas las celdas seleccionadas.

  5. Muestre la pestaña Fórmulas de la cinta.

  6. Haga clic en la herramienta Administrador de nombres. Excel muestra el cuadro de diálogo del Administrador de nombres. (Ver figura 1)

  7. Haga clic en el nombre que creó en el paso 4.

  8. La lista de celdas estará en el cuadro Se refiere a, en la parte inferior del cuadro de diálogo.

En este punto, puede copiar la información en el cuadro Se refiere a y pegarla en lo que desee (incluida otra hoja de trabajo). Deberá modificar un poco los datos después de pegarlos, ya que la lista es solo eso: una lista en serie de direcciones de celda.

Obviamente, esto afecta su libro de trabajo, ya que crea un rango con nombre. Si lo hace varias veces, se crearán varios rangos con nombre.

Por supuesto, esto puede volverse difícil de manejar rápidamente si necesita realizar la tarea con bastante frecuencia. Aquí es donde entran en juego las soluciones macro. El siguiente es un ejemplo de una macro que buscará un valor específico y luego colocará la dirección de cada celda que contenga ese valor en otra hoja de trabajo.

Sub CellAdressList()

Dim c1 As String     Dim nxt As String

Sheets("Sheet1").Select     Range("A1").Select     Cells.Find(What:="qrs", After:=ActiveCell, _       LookIn:=xlValues, LookAt:=xlWhole, _       SearchOrder:=xlByRows, SearchDirection:=xlNext, _       MatchCase:=False, SearchFormat:=False).Activate     c1 = ActiveCell.Address     Sheets("Sheet2").Select     Range("A1").Select     Range("A1").Value = c1     Do Until nxt = c1         Sheets("Sheet1").Select         Cells.FindNext(After:=ActiveCell).Activate         nxt = ActiveCell.Address         Sheets("Sheet2").Select         ActiveCell.Offset(1, 0).Select         ActiveCell.Value = nxt     Loop     ActiveCell.Value = ""

End Sub

La macro hace algunas suposiciones. Primero, asume que está buscando información en la hoja de trabajo llamada Hoja1. En segundo lugar, asume que desea que la lista de direcciones se coloque en la hoja de trabajo denominada Hoja2. Finalmente, asume que está buscando el valor «qrs» dentro de Sheet1. Todos estos elementos de la macro se pueden cambiar, si se desea.

Para algo un poco más flexible, considere la siguiente macro. Asume que ya ha seleccionado todas las celdas que contienen el valor que desea. (En otras palabras, debe realizar los pasos 1 a 3 de los pasos cerca del comienzo de esta sugerencia). Luego, puede ejecutar la macro.

Sub CopyFindAllSelection()

Dim outcell As Range     Dim c As Range

Set outcell = Range("Sheet2!A1")

For Each c In Selection         outcell.Value = c.Address         Set outcell = outcell.Offset(1, 0)

Next End Sub

El resultado es que las direcciones de las celdas seleccionadas se colocan en la hoja de trabajo Sheet2. Esta macro es un poco más flexible porque le permite encontrar cualquier cosa en cualquier hoja de trabajo. La única parte «codificada»

es la hoja de trabajo (Hoja2) en la que se colocan las direcciones.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13581) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.