Solved

Excel "Find and Replace" VBA - Export results to an array

Posted on 2016-07-28
6
28 Views
Last Modified: 2016-07-28
Hi Experts,

I'm trying to figure out how to make a call using VBA to access the "Find and Replace" menu:

 Screen shot of Find and Replace
I want to place the results (all fields) of the FindAll into an array for further processing.

thanks in advance,
Mark
0
Comment
Question by:SuperLight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41732565
Hi,

pls try ( fo r values, addresses or ranges)

Sub Macro()
Dim Sub Macro()
Dim aValues()
Dim aAddresses()
Dim aRanges()
    Set c = Cells.Find("MP", LookIn:=xlValues, Lookat:=xlPart)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Idx = 0
        Do
        ReDim Preserve aValues(Idx)
        ReDim Preserve aAddresses(Idx)
        ReDim Preserve aRanges(Idx)
        aValues(Idx) = c.Value
        aAddresses(Idx) = c.Address
        Set aRanges(Idx) = c
        Set c = Cells.FindNext(c)
        Idx = Idx + 1
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If

End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:SuperLight
ID: 41732798
Thanks!

Works a treat - with a little tweakage! ;o)

Kind Regards,
Mark
0
 

Author Comment

by:SuperLight
ID: 41732823
Quick follow-up...

Which parameter needs changing to Search the Workbook, not just the worksheet?

thanks,
Mark
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41732836
first what are looking for ranges or values?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41732845
then try
Sub Macro()
Dim aValues()
Dim aAddresses()
Dim aRanges()
Idx = 0
For Each ws In ActiveWorkbook.Worksheets
    With ws.Cells
        Set c = .Find("MP", LookIn:=xlValues, Lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = "'" & ws.Name & "'!" & c.Address
            Do
                ReDim Preserve aValues(Idx)
                ReDim Preserve aAddresses(Idx)
                ReDim Preserve aRanges(Idx)
                aValues(Idx) = c.Value
                aAddresses(Idx) = "'" & ws.Name & "'!" & c.Address
                Set aRanges(Idx) = c
                Set c = .FindNext(c)
                Idx = Idx + 1
            Loop While Not c Is Nothing And "'" & ws.Name & "'!" & c.Address <> firstAddress
        End If
    End With
Next
End Sub

Open in new window

0
 

Author Comment

by:SuperLight
ID: 41732849
superb!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question