Ctrl F, export results

[ellison]

Hi, I'm wondering if there is a way of exporting the results of a Ctrl Find?

I've seen that you can select them all and copy them, but only if the selected cells are next to each other. (But that doesn't happen very often for us!).

(The other challenge we have is that we'd like to use this on different files & they'll often have different headers, sheet names etc

Ctrl-Find-Export-Results-01.xlsx
	A	B	C	D
1	Row	Col-B	Col-C	Col-C
2	2	alpha	alpha 9
3	3	beta
4	4	charlie	charlie 6
5	5	delta	delta	14
6	6	echo	echo 5
7	7	foxtrot	foxtrot 1	12
8	8
9	9	golf	golf 4
10	10	hotel	hotel 4
Sheet1

Thanks for any help you could provide!

 

[Georgiboy]

Perhaps the below will give you some ideas, you could refine the code quite a bit to be honest, at this stage it is more pseudocode :

Sub FindLoop()
    Dim addVar() As String, valVar() As Variant, x As Long
    Dim rngFA As String, outIB As Range
    Dim rng As Range, fRng As Range
    Dim sIn As Integer, VorF As Integer
   
    Set rng = ActiveSheet.Range("B2:E10")
   
    sIn = InputBox("1 = Values, 2 = Formula", "LookIn") ' does not have to be 2, anything other than 1 will search Formula
    VorF = InputBox("1 = Values, 2 = Formula", "Return values or formula") ' does not have to be 2, anything other than 1 will return formula
   
    Set fRng = rng.Find(InputBox("Value to find", "Find what?"), , IIf(sIn = 1, xlValues, xlFormulas), xlPart)
   
    If Not fRng Is Nothing Then
        rngFA = fRng.Address
        Do
            If VorF = 1 Then
                ReDim Preserve addVar(x): addVar(x) = fRng.Address(, , , 1)
                ReDim Preserve valVar(x): valVar(x) = fRng.Value
                x = x + 1
            ElseIf Application.IsFormula(fRng) = True Then
                ReDim Preserve addVar(x): addVar(x) = fRng.Address(, , , 1)
                ReDim Preserve valVar(x): valVar(x) = fRng.Formula
                x = x + 1
            End If
            Set fRng = rng.FindNext(fRng)
        Loop Until fRng Is Nothing Or fRng.Address = rngFA
    End If
   
    Set outIB = Application.InputBox("Select range", Type:=8)
   
    outIB.Resize(UBound(addVar) + 1, 1) = Application.Transpose(addVar)
    outIB.Offset(, 1).Resize(UBound(valVar) + 1, 1) = Application.Transpose(valVar)
End Sub

 

[ellison]

Hi that is astonishingly great!

1 final fly in the ointment seems to be that the code is only displaying values (even if you opt for the formula option)

(I've tried to search a workaround for this but I'm drawing a blank)

Would you have any pointers?!

 

[Georgiboy]

Do you mean is is displaying the value that is the result of a formula?

For example, it will show: "echo" instead of "=+B6"

Have you selected the resulting cell as it should contain the formula.

 

[ellison]

Yes what you've said is right..

so even if I select formula, it's still showing the value (aka "result of the formula") - as opposed to the actual formula

 

[Georgiboy]

You could try changing this line:

ReDim Preserve valVar(x): valVar(x) = fRng.Formula

With:

ReDim Preserve valVar(x): valVar(x) = "'" & fRng.Formula

 

[ellison]

You could try changing this line:

ReDim Preserve valVar(x): valVar(x) = fRng.Formula

With:

ReDim Preserve valVar(x): valVar(x) = "'" & fRng.Formula

That's cracked it!!!! Brilliant .... and H-U-G-E thanks for your help!