Cómo recorrer cada elemento de la lista de validación de datos con VBA
Esta publicación analiza cómo recorrer todos los elementos en una lista de Validación de datos con VBA. Esto se inspiró en una pregunta de un lector. Quería guardar un PDF para cada elemento de una lista de Validación de datos. Sin embargo, una vez que dominamos el proceso de bucle, no estamos restringidos a archivos PDF; Podemos cambiar fácilmente las acciones a realizar para cada elemento.
Cuando abordé situaciones similares, utilicé una tabla o un rango con nombre como fuente para la lista de validación de datos. Por lo tanto, recorrer la tabla o el rango con nombre logra un resultado similar. Sin embargo, una lista de validación de datos también puede incluir elementos separados por comas; en consecuencia, proporciona un caso de uso diferente.
La pregunta despertó mi interés. Entonces, a continuación encontrarás mi enfoque.
Tabla de contenido
- Diferentes tipos de menú desplegable de Validación de datos
- El código VBA
- Usando el código para su escenario
- Ubicación de la lista de Validación de datos
- Acciones a realizar para cada elemento de la lista
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0119 Recorriendo la lista de validación de datos.zip
Tener acceso
No todas las listas de validación de datos son iguales. Pueden basarse en lo siguiente:
- Rangos de celdas estándar
- rangos con nombre
- Rangos de derrame
- Listas separadas por comas
Las capturas de pantalla siguientes ilustran cómo se configuran estos cuatro métodos.
Rango de celdas estándar
rango con nombre
Rango de derrame
Lista separada por comas
Los métodos de rango de celdas, rango con nombre y rango de derrame se pueden tratar de manera similar desde una perspectiva VBA; todos son rangos. Una lista separada por comas es ligeramente diferente y requiere un enfoque alternativo. Sin embargo, el código siguiente se ha escrito para abordar los cuatro escenarios.
El código VBA
El siguiente código recorre cada elemento de una lista de validación de datos.
Sub LoopThroughDataValidationList()Dim rng As RangeDim dataValidationArray As VariantDim i As IntegerDim rows As Integer'Set the cell which contains the Data Validation listSet rng = Sheets("Sheet1").Range("C2")'If Data Validation list is not a range, ignore errorsOn Error Resume Next'Create an array from the Data Validation formula, without creating'a multi-dimensional array from the rangerows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.CountReDim dataValidationArray(1 To rows)For i = 1 To rows dataValidationArray(i) = _ Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)Next i'If not a range, then try splitting a stri.webp Err.Number 0 Then Err.Clear dataValidationArray = Split(rng.Validation.Formula1, ",")End If'Some other error has occured so exit subIf Err.Number 0 Then Exit Sub'Reinstate error checkingOn Error GoTo 0'Loop through all the values in the Data Validation ArrayFor i = LBound(dataValidationArray) To UBound(dataValidationArray) 'Change the value in the data validation cell rng.Value = dataValidationArray(i) 'Force the sheet to recalculate Application.Calculate 'Insert the code to perform for each item here'Next iEnd SubUsando el código para su escenario
Hay dos elementos involucrados en la adaptación del código para su escenario:
- La ubicación de la lista de validación de datos.
- La acción a realizar para cada elemento de la lista.
Ubicación de la lista de Validación de datos
Para usar el código con su lista de Validación de datos, cambie lo siguiente para hacer referencia a la hoja y la celda de su escenario.
'Set the cell which contains the Data Validation listSet rng = Sheets("Sheet1").Range("C2")Acciones a realizar para cada elemento de la lista
Dado el contexto del escenario, creo que las acciones que probablemente requeriremos para esto generalmente implican escenarios de informes en ráfaga. Por lo tanto, los siguientes ejemplos pueden resultarle útiles.
Ingrese los fragmentos de código a continuación debajo del código: 'Inserte aquí el código a realizar para cada elemento'.
Guardar como pdf
A continuación se guarda un PDF de la hoja de trabajo activa para cada elemento de la lista de Validación de datos. Cambie el parámetro de nombre de archivo para guardar el archivo en la ubicación adecuada de su red. El valor seleccionado se utiliza en el nombre del archivo para garantizar que sea único.
'Save as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:Test FolderPDF - " rng.Value ".pdf"Para obtener más ejemplos de cómo guardar archivos PDF, consulte nuestra publicación sobre cómo guardar archivos de Excel como PDF con VBA .
Enviar a la última impresora seleccionada
Lo siguiente envía la hoja activa a la última impresora seleccionada.
'Send to last selected printer ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=FalseNota: Si es una impresora PDF, es posible que solicite un nombre de archivo para cada elemento. Por lo tanto, el ejemplo de PDF anterior puede resultar más útil.
Guardar una copia del libro de trabajo
A continuación se guarda una copia del libro de trabajo para cada elemento de la lista. Cambie el parámetro de nombre de archivo para guardar el libro en la ubicación correspondiente. El valor seleccionado se utiliza en el nombre del archivo para garantizar que los nombres de los archivos sean únicos.
'Save a copy of the workbook ActiveWorkbook.SaveCopyAs _ Filename:="C:Test FolderExcel Workbook - " rng.Value ".xlsm"Conclusión
Para guardar por lotes o imprimir informes individuales basados en una lista de Validación de datos, esta es una técnica muy útil. Como se repite mucho más rápido de lo que podemos hacer clic, puede ahorrar mucho tiempo.
Aquí hay algunas publicaciones relacionadas que pueden resultarle útiles.
- Recorrer hojas seleccionadas con VBA
- Guardar archivos de Excel como PDF con VBA
- 30 macros VBA útiles, listas para usar
Deja una respuesta