Paste Value all the cells in a given sheet if Cell has Vlookup formula..

=VLOOKUP(VALUE($B$100);INDIRECT("2011.xls!"&D$101;0);3;0)

I have a sheet to send other people but i cannot send 2011.xls because it contains secret information. How can i copy-paste value all the cells in that sheet ("Centers") which has a formula like =VLOOKUP(VALUE($B$100);INDIRECT or contains VLOOKUP ?

Normally i would break link but because i have linked the table array with Indirect its not seen as a link

Answers


This procedure loops through all cells on the sheet and changes all VLOOKUP formulas to values:

Sub RemoveVlookupFormulas()
    Dim rng As Range
    With Worksheets("Centers")
        For Each rng In .UsedRange
            If rng.Formula Like "*VLOOKUP*" Then rng.Formula = rng.Value
        Next rng
    End With
End Sub

Need Your Help

Use sed to read file into middle of line

perl sed

File A contains this text (assume that "alpha" and "bravo" are arbitrarily long chunks on a single line):

Linking error gsoap in mac in c

c osx gsoap

I try to add gsoap in my application.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.