Convertir fórmulas en valores según criterio.

En ciertas situaciones en Excel tenemos que convertir fórmulas en constantes. Por ejemplo, si extraemos valores de un cuaderno remoto usando BUSCARV, y queremos eliminar las fórmulas para evitar errores involuntarios, reducir el peso del archivo o mejorar la velocidad de cálculo.

Ya hemos mostrado como usar Ir A-Especial para convertir seleccionar todas las celdas que contengan fórmulas. Una vez seleccionadas, usamos Copiar-Pegado Especial-Valores.

Pero, ¿qué hacemos cuando queremos convertir sólo las celdas que contienen una función específica? Por ejemplo, todas las celdas que contienen BUSCARV en este cuadro de ganancias. Los datos los extraemos de un archivo remoto usando BUSCARV.

Luego de extraer los valores queremos eliminar las fórmulas BUSCARV pero no las fórmulas con las que calculamos las ganancias en las filas 6, 9 ,11 y 13.

Una posibilidad sería usar buscar y reemplazar (Ctrl+L) – buscar todos, seleccionar todos los resultados y apretar cerrar. Como ya mostramos, Excel selecciona todos los valores; luego podemos usar Copiar, pero si intentamos usar Pegado Especial-Valores esto es lo que veremos

La solución es usar una macro.

Sub formula_to_number_with_criteria()
    Dim strStringCriteria As String
    Dim rngCell As Range
    Dim lCounter As Long
   
    If Selection.Count < 2 Then
        MsgBox "Debe seleccionar por lo menos dos celdas", vbInformation
        Exit Sub
    End If
   
    'criterio de busqueda
    strStringCriteria = Application.InputBox(prompt:="Enter formula identifier", _
                                                Title:="Indentifier", Type:=2)
                                               
    'si no se ingresa criterio se cierra la rutina
    If Len(strStringCriteria) = 0 Then
        MsgBox "No se ingreso ningun criterio - no se puede realizar la operacion", vbCritical
        Exit Sub
    End If
   
   
    lCounter = 0
    For Each rngCell In Selection
        If rngCell.HasFormula Then
            If InStr(1, rngCell.FormulaLocal, strStringCriteria) > 0 Then
                rngCell = rngCell.Value
                lCounter = lCounter + 1
            End If
        End If
    Next rngCell
   
    If lCounter = 0 Then
        MsgBox "No se encontro ninguna celda con el criterio", vbInformation
    Else
        MsgBox lCounter & " celdas fueron modificada", vbInformation
    End If
     
End Sub


Esta macro hace lo siguiente:

• Comprueba que se hayan elegido por lo menos dos celdas
• Abre un formulario para que el usuario ingrese el criterio de búsqueda
• Busca todas las celdas en el rango seleccionado que cumplen con el criterio y reemplaza la fórmula por el valor
• Al terminar las operaciones produce un mensaje con el número de celdas que se han modificado.

Un detalle a tomar en cuenta es el uso de la propiedad FormulaLocal. Esto es necesario para que el código vea la versión local de la función (BUSCARV en nuestro caso) y no la versión nativa (inglés, VLOOKUP).
Podemos comprobar esto usando la ventana Inmediate del editor de Vba