Make a replacement in formulas in a certain way-CodePudding

I've got multiple formulas =VLOOKUP() in the workbook. Now they look the following way:

=VLOOKUP(CELL_REFERENCE;BOOK_NAME_AND_COLUMNS;COLUMN_NUMBER;0).

I need to find a solution with VBA or Excels' built-in functions to make replacement for all VLOOKUP formulas to look like this but with preserving all cell references and inserted numbers:

=VLOOKUP(VALUE(CELL_REFERENCE);BOOK_NAME_AND_COLUMNS;COLUMN_NUMBER;0)

CELL_REFERENCE, BOOK_NAME_AND_COLUMNS, COLUMN_NUMBER can be different from formula to formula, for example:

CELL_REFERENCE - A1, 
BOOK_NAME_AND_COLUMNS - LIB!$A:$J; 
COLUMN_NUMBER - 3.

I've tried to use wildcard character with replace built-in function, but it didn't work. Are there any ways to make it possible with VBA or with excel built-in function, which I'm not aware about?

CodePudding user response：

Modify Formula in All Worksheets

Option Explicit

Sub ReplaceVLookup()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    Dim rg As Range
    Dim fCell As Range
    Dim FirstAddress As String
    
    Dim cPos As Long ' comma
    Dim lpPos As Long ' left parentheses
    Dim fString As String
    Dim lString As String
    Dim rString As String
    
    Application.ScreenUpdating = False
    
    For Each ws In wb.Worksheets
        Set rg = ws.UsedRange
        Set fCell = rg.Find("=VLOOKUP(*", , xlFormulas, xlWhole)
        If Not fCell Is Nothing Then
            FirstAddress = fCell.Address
            Do
                fString = fCell.Formula
                If InStr(fString, "=VLOOKUP(VALUE") <> 1 Then
                    cPos = InStr(fString, ",")
                    lString = Left(fString, cPos - 1)
                    rString = Right(fString, Len(fString) - cPos   1)
                    lpPos = InStr(fString, "(")
                    fCell.Formula = Left(lString, lpPos) & "VALUE(" _
                        & Mid(lString, lpPos   1, cPos - lpPos) & ")" & rString
                End If
                Set fCell = rg.FindNext(fCell)
            Loop Until fCell.Address = FirstAddress
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox """VALUE"" added.", vbInformation
    
End Sub

CodePudding user response：

I believe you may find the answer here:

https://stackoverflow.com/a/39874227/15888753

One of the answers must work for you, if it's not the one I referenced, try the most upvoted one(seems complex).

Hope this helps, I tried to find a simpler answer but to no success.