Incomplete code! help please

Joseph.Marro

Board Regular
Joined
Nov 24, 2008
Messages
153
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I have two pieces of code that I need to talk to each other. Script 1 is complete and works as intended. Script 2 is a work in progress and includes notes that have been commented explaining what needs to occur. I don’t know how to pass defined variables from script to script and make the two work together. Any help would be greatly appreciated.<o:p></o:p>
<o:p></o:p>
Note: for additional information please see http://www.mrexcel.com/forum/showthread.php?t=485506<o:p></o:p>
<o:p></o:p>
<o:p></o:p>Script 1
Code:
Sub Replacer()
     'Does a Find and Replace on whole words throughout the selected range. Uses a table of _
    Find And Replace strings taken from Sheet2 columns A And B _
    Uses regular expressions For search To make sure found strings are complete words _
    Uses arrays For speed For range To be searched And For source of Find/Replace strings. _
    Note: will wipe out all formulas In the selected range!
    Dim RgExp As Object
    Dim rg As Range
    Dim X As Variant, Y As Variant
    Dim i As Long, j As Long, k As Long, nColumns As Long, nFindReplace As Long, nRows As Long
    Dim FindReplacePrompt As String
    FindReplacePrompt = "I couldn't find the Find/Replace strings at Sheet2!A1:Bxx. Please select them now." & _
    "  No blanks allowed in first column!"
 
    If Selection.Cells.Count = 1 Then
        If Selection = "" Then
            MsgBox "Please select some cells to run the macro on, then try again"
            Exit Sub
        Else
            ReDim X(1 To 1, 1 To 1)
            X(1, 1) = Selection
        End If
    Else
        X = Selection.Value
    End If
 
     'Populate the array variable Y with Find/Replace strings. Default source is Sheet2, A1:Bxx
    On Error Resume Next
    Set rg = Worksheets("Standard Abbreviations").Range("A2")
    If rg Is Nothing Then
        Set rg = Application.InputBox(prompt:=FindReplacePrompt, Title:="Source of Find/Replace strings", Type:=8)
        If rg Is Nothing Then Exit Sub
    Else
        If rg.Cells(1, 1) = "" Then
            Set rg = Application.InputBox(prompt:=FindReplacePrompt, Title:="Source of Find/Replace strings", Type:=8)
            If rg Is Nothing Then Exit Sub
        Else
            Set rg = Range(rg, rg.End(xlDown).Offset(0, 1))
        End If
    End If
    On Error GoTo 0
    Y = rg.Value
    nFindReplace = UBound(Y)
 
    Set RgExp = CreateObject("VBScript.RegExp")
    With RgExp
        .Global = True
        .IgnoreCase = True  'True if search is case insensitive. False otherwise
    End With
 
    nRows = UBound(X)
    nColumns = UBound(X, 2)
    For i = 1 To nFindReplace
        RgExp.Pattern = "\b" & Y(i, 1) & "\b"
        For j = 1 To nRows
            For k = 1 To nColumns
                X(j, k) = RgExp.Replace(X(j, k), Y(i, 2))
            Next k
        Next j
    Next i
 
    Set RgExp = Nothing
    Selection.Value = X 'Replace cell values with the edited strings
End Sub

Script 2
Code:
Public Sub Array_Replace()
 
    Dim txt_in As String, txt_out As String
    Dim ary_ele As Variant
    Dim i As Long, j As Long, iLen As Long
 
   'Here is where I begin the second loop using the selection from Sub Replacer
 
    txt_in = "I need to have this equal to the selection in the range based off of selection used in Sub Replacer()"
 
        ary_ele = Split(txt_in, " ")
            For i = UBound(ary_ele) To LBound(ary_ele) Step -1
 
                'find and replace procedure goes here - find the ary_ele value in column "A" (Exact Match) on sheet3
                'and replace with corresponding in column "B" (or maybe i can call Sub Replacer() to do the find and
                'replace within the array.
                    txt_out = ""
                    For j = LBound(ary) To UBound(ary)
                        If (txt_out <> "") Then txt_out = txt_out & " "
                        txt_out = txt_out & ary(j)
                    Next j
                iLen = Len(txt_out)
                If iLen <= 48 Then Exit For
 
            Next i
    'Here i need to replace the Selection.Value from Sub Replacer() with txt_out.
 
    'Now i need to loop to next cell in the original selection used in Sub Replacer()
End Sub

Thank you,

Joseph Marro
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,583
Messages
5,832,594
Members
430,144
Latest member
bruno92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top