Tengo una hoja de trabajo de Excel que consta de dos columnas, una de las cuales está llena de cadenas y la otra está vacía. Me gustaría usar VBA para asignar el valor de las celdas en la columna vacía según el valor de la cadena adyacente en la otra columna.
Dim regexAdmin As Object Set regexAdmin = CreateObject("VBScript.RegExp") regexAdmin.IgnoreCase = True regexAdmin.Pattern = "Admin" Dim i As Integer For i = 1 To 10 "let"s say there is 10 rows Dim j As Integer For j = 1 To 2 If regexAdmin.test(Cells(i, j).Value) Then Cells(i, j + 1).Value = "Exploitation" End If Next j Next i
El El problema es que cuando se usa este ciclo para una gran cantidad de datos, tarda demasiado en funcionar y, la mayoría de las veces, simplemente colapsa Excel.
¿Alguien conoce una mejor manera de hacer esto?
Respuesta
La respuesta corta es:
No «use vba , usa una fórmula. En particular, una combinación de IF
y SEARCH .
=IF(SEARCH($A1,"Admin")>0,"Exploitation","")
Pero esto es revisión de código, así que hagámoslo de todos modos.
-
Regex es lento. Parece que sólo lo está utilizando para no distinguir entre mayúsculas y minúsculas. Dado eso, puede comparar directamente los valores de celda usando
StrComp
con la opciónvbTextCompare
. ( artículo útil sobre StrComp ) -
i
yj
se se utilizan normalmente para contadores de bucle, perorow
ycol
tiene más sentido en este caso.
Esto es lo que podría parecer:
Dim row As Integer For row = 1 To 10 "let"s say there is 10 rows Dim col As Integer For col = 1 To 2 If StrComp("Admin",Cells(row, col).Value,vbTextCompare) Then Cells(row, col + 1).Value = "Exploitation" End If Next col Next row
Respuesta
Creo que la comparación de cadenas simple sería mucho más rápida que Regex.
Dim pattern as string pattern = "Admin" Dim i As Integer For i = 1 To 10 "let"s say there is 10 rows Dim j As Integer For j = 1 To 2 If Cells(i, j) = pattern Then Cells(i, j + 1) = "Exploitation" End If Next j Next i
Comentarios
- Este código no ‘ t aborda una comparación que no distingue entre mayúsculas y minúsculas, pero ‘ es correcto. La expresión regular es excesiva y en este caso se prefiere la comparación de cadenas. (¡Bienvenido a Revisión de código, por cierto!)
Respuesta
¿Puedo sugerir una reducción del 50% en el tiempo de ejecución /¿esfuerzo?
Dim row As Integer For row = 1 To 10 "let"s say there is 10 rows If StrComp("Admin",Cells(row, 1).Value,vbTextCompare) Then Cells(row, 2).Value = "Exploitation" End If Next row
¿Nadie notó que el OP habla de «comprobar UNA columna, escribir en la siguiente adyacente», en realidad? Entonces, ¿por qué columnas en bucle? La segunda pasada solo verificaría una celda vacía o una con «Explotación» en ella.
Respuesta
Wow. El solo hecho de leer las primeras líneas me hizo preguntarme:
- ¿Por qué la vinculación tardía?
- ¿Por qué usar una expresión regular?
@ ckuhn203 ya abordó el nombre en su respuesta, pero encuentro esto:
Dim i As Integer For i = 1 To 10 "let"s say there is 10 rows
Convertido en eso:
Dim row As Integer For row = 1 To 10 "let"s say there is 10 rows
… Ya no «necesita el comentario.
Me gustaría usar VBA para asignar el valor de las celdas en la columna vacía según el valor de la cadena adyacente en la otra columna.
Creo que «s [mis | ab] usando VBA: Excel en sí es muy bueno para tratar con la asignación de valores de celda basados en otros Cell «valores.
regexAdmin.Pattern = "Admin"
Creo que «s [mis | ab] usando expresiones regulares: si su patrón es solo una palabra simple, lo más probable es que esté tratando de matar un mosquito con una bazuca. Herramienta incorrecta para t El trabajo aquí.
Respuesta
Siempre que acceda al objeto Range, debe hacerlo con una sola operación de lectura / escritura.
Antes de ingresar al bucle for, debe leer todo el rango con el que desea trabajar.
data = Range(Cells(1,1), Cells(10,2)).Value
Ahora puede trabajar con los datos:
For i = 1 To 10 "let"s say there is 10 rows Dim j As Integer For j = 1 To 2 If regexAdmin.test(data(i, j)) Then data(i, j + 1) = "Exploitation" End If Next j Next i
Finalmente, vuelva a escribir los datos en Excel:
Range(Cells(1,1), Cells(10,2)).Value = data
Responder
En B1:
=if(upper(A1)="ADMIN","Exploitation","")
Luego, llénelo. Esto no distingue entre mayúsculas y minúsculas.
Este autocompletado se puede realizar de dos maneras, ya sea de forma interactiva en la hoja de trabajo o mediante programación:
Interactivamente : Excel tiene una función de autocompletar. Con B1 seleccionado y esa fórmula puesta, simplemente haga doble clic en el controlador de relleno, que es el pequeño cuadrado en la esquina inferior derecha de la celda cuando está seleccionado. Excel copiará inteligentemente la fórmula hasta el final del rango contiguo que tiene lo que significa que si A1-A256 tiene datos sin espacios en blanco, se completará automáticamente en B256. Alternativamente, si hay espacios en blanco, desplácese hasta la parte inferior y seleccione B256 (o cualquiera que sea el final). Luego Ctrl + Shift + Flecha arriba para seleccionar el rango que conduce a B1, y Ctrl + D para copiarlo (piense en d = ídem)
Usando VBA … si debe hacerlo mediante programación: con la fórmula que contiene la celda como su selección:
Selection.AutoFill Destination:=Range("B1:B19")
Hay otras opciones disponibles para autocompletar para hacer algunos trucos interesantes.Puede copiar un valor literal en lugar de una fórmula, o también completar una serie según un patrón. También puede establecer patrones personalizados para que los reconozca, como líneas de negocio que suele repetir en cosas o ciudades en las que tiene tiendas minoristas, etc.
Comentarios
- ¡Bienvenido a Code Review! ¡Me perdí por completo que OP no ‘ no necesitaba buscar en la columna! ++ La mejor solución aquí.
Respuesta
Combinando todas las otras respuestas juntas, no distingue entre mayúsculas y minúsculas como el regex en el original, eliminó la necesidad de especificar cuántas filas y declarar todas las variables porque Option Explicit
evita muchos errores en vba
Option Explicit Sub checkForExploit() Dim row As Integer Dim data() As Variant Dim datarange As Range Set datarange = Range("A1:B10") data = datarange.Value For row = 1 To UBound(data, 1) If LCase(data(row, 1)) = "admin" Then data(row, 2) = "Exploitation" End If Next row datarange.Value = data End Sub