Solved

macro to remove formulas which  references  a particular sheet

Posted on 2010-11-25
10
361 Views
Last Modified: 2012-05-10
Dear Experts,

I would like a macro that will evaluate all sheets within the workbook except the "Any Term" sheet and replace any formula that references to the "Any Term" sheet with the value (copy-paste special values).

Many thanks for your advice
0
Comment
Question by:hendrkle
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34215817
How about:

Chris
Sub noForm()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) <> "any term" Then
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 100 total points
ID: 34215821
Just a quick thought ... to avoid leaving the used range selected at all the following selects a single cell in the used range, (the first one).

Chris
Sub noForm()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) <> "any term" Then
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            If ws.UsedRange.Cells.Count > 1 Then
                ws.UsedRange.Cells(1).Select
            End If
            Application.CutCopyMode = False
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub
S

Open in new window

0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 300 total points
ID: 34216470
I think you will need something like this which uses a Find to determine all formula links to a sheet name contained in the constant
StrText
To then paste as values only these specific cells

The Find approach is written up at
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2919-Using-Find-and-FindNext-to-efficiently-delete-any-rows-that-contain-specific-text.html

I have modified the code below to
1) Look at formulas not values
2) Used a regular expresssion to test whether the sheet name is of the form
     a) "x!"
     b  "'x'!"

Cheers

Dave  

Option Explicit

Const strText = "Any Term"

Sub ColSearch_DelRows()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim strFirstAddress As String
    Dim lAppCalc As Long
    Dim bParseString As Boolean
    Dim objRegex
    Dim strWSname As String

    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = "[^\w]+"
    strWSname = IIf(objRegex.test(strText), "'" & strText & "'!", strText & "!")

    'No further processing of matches
    bParseString = True

    With Application
        lAppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Any Term" Then

            'a) match string to entire cell, case insensitive
            Set cel1 = ws.UsedRange.Find(strWSname, , xlFormulas, xlPart, xlByRows, , False)

            'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
            If Not cel1 Is Nothing Then
                Set rng2 = cel1
                strFirstAddress = cel1.Address
                Do
                    Set cel1 = ws.UsedRange.FindNext(cel1)
                    Set rng2 = Union(rng2.EntireRow, cel1)
                Loop While strFirstAddress <> cel1.Address
            End If

            'Further processing of found range if required
            If bParseString Then
                If Not rng2 Is Nothing Then rng2.Value = rng2.Value
            End If
            End If
        Next ws

        With Application
            .ScreenUpdating = True
            .Calculation = lAppCalc
        End With

    End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34216528
Ah! I missed that distinction - glad someone's synapses are firing correctly.

Chris
0
 
LVL 3

Assisted Solution

by:byronwall
byronwall earned 100 total points
ID: 34220830
This is a more direct approach.  For what it's worth.

Sub CheckCells()
    Dim cell As Range
    Dim ws As Worksheet

    For Each ws In Sheets
        If LCase(ws.Name) <> "any term" Then
            For Each cell In ws.UsedRange
                If InStr(1, LCase(cell.Formula), "any term") > 0 Then
                    cell.Value = cell.Value
                End If
            Next cell
        End If
    Next ws
End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Dave Brett
ID: 34220858
>This is a more direct approach.  For what it's worth.

While its shorter code it will be much slower. Plus it should be a more specific search to ensure it is a sheet name

For loops can and should be avoided

Dave
0
 
LVL 3

Expert Comment

by:byronwall
ID: 34220888
Maybe we will get a sheet to try them out on.  I doubt the Find approach is significantly faster.  Also your notion that For loops should be avoided is just incorrect.  Any type of loop can be used to mimic any other type of loop: the application determines the correctness.

Given the nature of most spreadsheets, I doubt that searching explicitly for just the sheet name in formulas will yield false positives.  If it does, we could improve the search term by adding the !.

And there is something to be said for shorter code if you ever have to come back to it (or worse, someone else has to).  This is especially true if the shorter code does not rely on regular expressions and the unintuitive results of a Do loop iterating a Find and Union.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34220978
>  I I doubt the Find approach is significantly faster.  Also your notion that For loops should be avoided is just incorrect

You do well then to up your experience levels before posting in areas you are unfamiliar with. The other prudent approach would be to test your theories before posting

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34221056
byron, you are partially correct in that for loops are often useful tools. However, Excel vba is a scripted language (not a compiled one) so the more you can use the built-in compiled functions (like 'Find'), the faster your code will go. For loops do not get unrolled or optimized in any way at all in VBA.

If you are coding in a compiled language like C, C++, java, even VB.NET, etc. then use for loops all day long. But in VBA, .vbs, MATLAB, etc. use built-in functions whenever possible. They are, in fact, significantly faster.

Dave, come on, he registered, like, maybe four hours ago. Go easy on him. :)
0
 

Author Closing Comment

by:hendrkle
ID: 34221448
Thank you all for your great input, I found the solution from brettdj best fit for my purpose and am using i now, thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for each dropdown 15 46
Formula or Macro to determine variance 17 75
splitting text of cell to columns 14 24
the code is not looping through 11 35
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now