Solved

macro to remove formulas which  references  a particular sheet

Posted on 2010-11-25
10
360 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel file merge 2 cells with line break 2 23
VLOOKUP Function MS Excel 2010 2 21
TT Status Chang 3 31
Rearrange Macro 7 18
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 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

20 Experts available now in Live!

Get 1:1 Help Now