VBA code to take some string components search them inside a workbook and replace the components with the cell addresses found

0 votes

In an Excel column, I have formulas that return TRUE or FALSE. Each formula consists of alphanumeric strings that always begin with the letter ABC and end with seven digits. The string's center section can change.

For example:

=OR(ABC_XYZ_0001234 >0 , ABC_XYZ_0001235 <0).

These strings of letters and numbers represent cells from another workbook. These strings should be replaced with the cell address where they were located.

For example:

 =OR('[Wrkbook.xlsx]Sheet1'!$A$39 >0 , '[Wrkbook.xlsx]Sheet1'!$A$40 <0).

The strings are particular to the final worksheet.

I tried the following code with no success:

Sub FindStringInOtherWorkbook()

Dim strSearch As String
Dim rng As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim c As Range
Dim rplce As String

'Set the range to search in the active sheet
Set rng = ActiveSheet.Range("A")

'Open the second workbook
Set wb = Workbooks.Open("C:\path\to\Wrkbook.xlsx")

'Loop through each sheet in the second workbook
For Each ws In wb.Sheets
    'Loop through each cell in the range
    For Each c In rng
        strSearch = c.Value
        'Search for the string in the current sheet
        Set c = ws.Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole)
        'If a match is found, return the cell address
        If Not c Is Nothing Then
            rplce = Replace (rng, rplce.Value, c.Address)
        End If
    Next c
Next ws

'Close the second workbook
wb.Close

End Sub
Jan 22, 2023 in Others by Kithuzzz
• 38,010 points
359 views

1 answer to this question.

0 votes

First, look up all ABC_* values in the workbook, then record addresses in a dictionary. After extracting the ABC_ strings from the column using a regular expression, replace the extracted strings with addresses using the dictionary.

Sub FindStringInOtherWorkbook()

    Const FOLDER = "" ' "C:\path\to\"

    Dim wb As Workbook, ws As Worksheet
    Dim dict As Object
    Dim rng As Range, c As Range, fnd As Range, first As Range
    Dim f As String, n As Long
   
    ' dictionary
    Set dict = CreateObject("Scripting.Dictionary")
    
    'Open the second workbook and extract ABC_ addresses
    Set wb = Workbooks.Open(FOLDER & "Wrkbook.xlsx")
    For Each ws In wb.Sheets
        With ws.UsedRange
           Set fnd = .Find("ABC_*", LookIn:=xlValues, lookAT:=xlWhole)
           If Not fnd Is Nothing Then
           
                Do
                   Set first = fnd
                   'Debug.Print fnd.Value, fnd.Address(0, 0, xlA1, True)
                   dict.Add fnd.Value, fnd.Address(0, 0, xlA1, True)
                   Set fnd = .FindNext(fnd)
               Loop Until fnd.Address = first.Address
                   
           End If
        End With
    Next
    
    ' regular expression
    Dim regEx As Object, m As Object
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "(ABC_.*?\d{7})"
    End With
        
    'Set the range to search in the active sheet
    Set rng = ActiveSheet.UsedRange.Columns("A")
    For Each c In rng.Cells
        f = c.Formula
        If regEx.test(f) Then
           For Each m In regEx.Execute(f)
               s = m.submatches(0)
               If dict.exists(s) Then
                   f = Replace(f, s, dict(s))
                   n = n + 1
               End If
           Next
           c.Formula = f
        End If
    Next
    MsgBox n & " replacements made", vbInformation
    
    'Close the second workbook
    wb.Close

End Sub
answered Jan 22, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
1,768 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,700 points
675 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
797 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,617 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,448 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,098 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,700 points
1,594 views
0 votes
1 answer

VBA code help - Add a line for each missing date with the start and end date defined in a cell

Try this: Sub FillDates() Dim ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,700 points
754 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP