External references...convert to values

[theta]

Hi. I have macro that copies a sheet ("Section 1") to a new workbook.

Only problem is that it contains vlookups etc to other sheets in the original workbook.

So I need a macro that will go through and change each external reference (linking back to the old sheet) as values

But still have other formulas on the same sheet (=sum etc) that can remain as formulas...just need to external ones to be made values

?

 

[njimack]

Something like this...

Sub convert_to_values()
Application.ScreenUpdating = False

Dim r As Range

    For Each r In Cells
        If InStr(1, r.Formula, ".xls", 1) > 0 Then r.Formula = r.Value
    Next r
Application.ScreenUpdating = True
End Sub

If you know how many rows/columns are affected, this could be amended to run a lot faster

 

[theta]

Could I not put UsedRange in there to speed it up?

Also, using excel 2007, so .xls might not work?

Checking r.Formula

What about checking if Left( is "=" and the cell contains "[" as this would indicate an external ref ?

 

[theta]

You think this would work easier ?.

    For Each r In ws.UsedRange
        cFormula = r.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 1) = "=" Then
                If InStr(cFormula, "[") > 1 Then

 

[njimack]

Yes - UsedRange will be more efficient than Cells

I don't see the value in testing the length of the cell or the first character. You can test whether a cell contains a formula using

If r.HasFormula = True Then

If you're using Excel 2007 or 2010 then yes, test for "[" rather than "'.xls"

 

[theta]

Ah....small problem. Some of my formulas do not show that they link to another sheet as I used named ranges, so you do not get the workbook reference, only the named range

(Although the workbook ref =[....] is contained within the named range definition)

Thoughts?

 

[njimack]

Ah....small problem. Some of my formulas do not show that they link to another sheet as I used named ranges, so you do not get the workbook reference, only the named range

(Although the workbook ref =[....] is contained within the named range definition)

Thoughts?

For Each r In Cells
        If InStr(1, r.Formula, "[", 1) > 0 Then r.Formula = r.Value
    Next r

 

[theta]

So will this cover both standard formula =[...] etc, and those containing named ranges

Or should there be a combination of both with and OR statement?

For Each r In ws.UsedRange
If r.HasFormula = True Then
sFormula = r.Formula
If Len(sFormula) > 0 Then
If Left$(sFormula, 1) = "=" Then
If InStr(sFormula, "[") > 1 OR If InStr(1, sFormula, "[",1) >0 Then

 

[GlennUK]

Have you posted this question twice?

http://www.mrexcel.com/forum/showthread.php?t=534948

 

[njimack]

Why over-complicate things?
Did you try the code in my last post?
I thought you wanted to leave formulae intact and just remove references to other files.
I also don't understand why you would test the length of the formula, or use a separate variable (sFormula), and surely if the length of the formula is >0, then the first character MUST be "="