Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    changing links to values (2000)

    Anyone out there have a macro that would change all links to values?

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: changing links to values (2000)

    works great; thanks!

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing links to values (Excel 2000)

    How about this? It keys on the fact that links to other workbooks have the name in brackets and the sheet will have a exclamation, so a link should contain all three:
    "[", "]", "!" in the formula.

    This will also turn to values any formula that contains these three elements, but is not a link.

    Steve

    <pre>Sub LinksToValues()
    Dim wks As Worksheet
    Dim rng As Range
    Dim rArea As Range
    Dim rCell As Range

    For Each wks In Worksheets
    Set rng = Nothing
    On Error Resume Next
    Set rng = wks.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not rng Is Nothing Then
    For Each rArea In rng.Areas
    For Each rCell In rArea
    If InStr(rCell.Formula, "[") <> 0 _
    And InStr(rCell.Formula, "]") <> 0 _
    And InStr(rCell.Formula, "!") <> 0 Then _
    rCell = rCell.Value
    Next
    Next
    End If
    Next
    Set rCell = Nothing
    Set rArea = Nothing
    Set rng = Nothing
    Set wks = Nothing
    End Sub</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •