Solved

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

Posted on 2016-07-28
6
25 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 51

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
Independent Software Vendors: 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 51

Expert Comment

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

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

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!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 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