Another Question Concerning Replacing Formulae With Values !

[donna.gough]

I know, I know...but yes, another one !
I've seen various methods for this but all of them give me a RunTime
Error. If I select the cells with a formulae in and use a For/Next
loop to change each of those cells individually then it does work BUT
it takes about 5 minutes to do it. Every thing else fails.
I've tried....

1.) .UsedRange.Value = .UsedRange.Value
2.) .UsedRange.Formula = .UsedRange.Value
3.) Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
4.) Set rng = Columns("B:BM").Cells '.SpecialCells(xlCellTypeFormulas)
rng.Value = rng.Value

I have a lot of cells with formulae in a lot of cells with links in.
It's the links I really need to remove, the internal formalue can stay
if need be.

Can anybody suggest any alternatives that I have not tried yet.

These cells that I want to remove the links/formulae in are dotted
around the worksheet and are not therefore continuously next to each
other.

Thanks for any suggestions you can give.
Donna

 

[keepITcool]

Sub Macro1()
Dim vls, vl
With ActiveWorkbook
vls = .LinkSources(xlExcelLinks)
If Not IsEmpty(vls) Then
For Each vl In vls
.BreakLink vl, xlExcelLinks
Next
End If
End With
End Sub

 

[dominicb]

Good afternoon (e-mail address removed)

Try using the For ... Each ... Next structure thus:

Sub Change()
For Each usrcell In Selection
usrcell.Formula = usrcell.Value
Next usrcell
End Sub

Just remember to highlight the required range before running the
macro.

HTH

DominicB

 

[Tom Ogilvy]

All the methods (Except #4) you show should work unless you have merged
cells in your worksheet.

 

[donna.gough]

Folkes, thanks for your replys, but Iam still having problems.

KeepITcool.......I can not find any reference to 'BreakLinks' even
using the VBE Object browser. LinkSources is there but it won't
allow'BreakLinks'. Is it in a seperate library I won't have
referenced?
Dominicb.......For Each works but is too slow for me (~5 mins), hence
trying the other methods.
Tom........I have removed any mergedcells I had and tried the
..usedrange=.usedrange again but it still error out. I have tried
deleting all my charts etc to try and eliminate what may be the
problem, but it still errors out with the error...
Method 'Value' of bject 'Range' failed
...... What does the 'UsedRange' select ? I have cells with values,
formulae and links. I also have charts which reference data on it's
own sheet and have a couple of OptionButtons generated from the Contol
Toolbox toolbar. As far as I can see, the 'UsedRange.select' is only
selecting cells (which is whatI want)...but is it?

What I don't understand is .......
For Each icell In Selection.SpecialCells(xlCellTypeFormulas, 23)
' icell.Value = icell.Value
Next icell

......works (but very slow)

 

[donna.gough]

extension to last post. I pressed Post too early!

but this doesn't work...
Cells.SpecialCells(xlCellTypeFormulas).Select
With Selection
.Value = .Value
End With
..... Can anybody tell my why.

 

[Dave Peterson]

Depending on how the formulas are spreadout this might be a little quicker:

Option Explicit

Sub testme()

Dim myRng As Range
Dim myArea As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
'no formulas
Else
For Each myArea In myRng.Areas
With myArea
.Value = .Value
End With
Next myArea
End If

End Sub

I used the Activesheet and converted all the cells--but you could limit the
range.

But if you want all the formulas converted to values:

with activesheet.usedrange
.value = .value
end with

 

[keepITcool]

I'm the only one that gave the "correct" reply.
but no reaction from OP.

the original question included:

I have a lot of cells with formulae in a lot of cells with links in.
It's the links I really need to remove, the internal formalue can stay
if need be.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dave Peterson wrote :

 

[donna.gough]

Dave,
I have used your method. Below is the code .....
For Each iArea In Cells.SpecialCells(xlCellTypeFormulas).Areas
iArea.Select
On Error GoTo ErrHand:
iArea.Value = iArea.Value
Next iArea

ErrHand:
On Error GoTo 0
Resume Next

.......I have stepped through and it selects and changes all the areas
until the last area where it jumps to the error handling routine and
from there jumps back to Next iArea as you'd expect but from there
jumps back to ErrHand, where it errors out with an error message I
can't remember (useful eh !)....something along the lines of ' Erroring
with no Error'. I will run it again and post the error message butI
have to logout before I do this!
All the links have disappeared (which is what I am after) (Edit - Links
is greyed out) so it has obviously done all the necessay cells but why
does it error out while doing the last iArea ?

The last area contains a load of formuale that results in the cell
being '#N/A' but the previous 2 areas also did and it didn't go to the
ErrHand: and didn;t error out while changing those areas. I don't
think the '#N/A' s are the problem but I mention it incase it is.

Any ideas ?

 

[donna.gough]

Dave,
As promised it errors on the Resume Next with...
Runtime Error 20
Resume Without Error

.......KeepITcool.....As I've said I have tried the .BreakLinks but can
not get it to work without it erroring but as I can not find any help
attached to 'BreakLinks' (even the object browser won't recognise it) I
can not follow this up yet. In my earlier post I asked if it may be
part of a libarary that I have not got referenced. Object browser
finds LinkSources but not BreakLinks. The error message that pops up
with 'BreakLinks' is....
Runtime Error 438
Object doesn't support this property or method

 

[donna.gough]

This code does what I want, but when I press save I get a memory
couldnot be read error so there definately must be a problem somewhere
with the .value=.value line.
I noticed that it only errored on the last iArea even though it had
already changed some similar areas, so I added another sheet into the
loop(Was 1 sheet, now 2 sheets). Now it will jump into the ErrHand line
on the last iArea of the last sheet (it will run through the first
sheet without jumping into the ErrHand !).....

For Each s In ActiveWorkbook.Sheets
s.Activate
s.Unprotect
Cells.Select
Selection.MergeCells = False
Columns("AZ").ColumnWidth = 17.75
For Each iArea In Cells.SpecialCells
_(xlCellTypeFormulas).Areas
iArea.Select
On Error GoTo ErrHand:
iArea.Value = iArea.Value
On Error GoTo 0
Next iArea
Range("AX1").Select
s.Protect
Next s
GoTo End1:
ErrHand:
Resume Next
End1:

......Why should it be erroring out on the last iArea it meets. And why
when I add a sheet does it then complete the sheet that it errored on
before but then error on the last iArea of that new sheet ?

 

[Dave Peterson]

I responded to the current question--not the original.

But doesn't your solution require xl2002+ to use .breaklink?

 

[Dave Peterson]

Why did you add the .select?

And if you're using the whole sheet, why not just use that second suggestion and
avoid the loop altogether?

with activesheet.usedrange
.value = .value
end with

 

[keepITcool]

Donna,

missed your earlier answers. If you do not reply directly to a post
the poster is not notified.

I understand from Dave that BreakLink requires excel XP, which
you apparently do not have and I forgot to check which version it was
available.

 

[donna.gough]

I originally tried the ActiveSheet.UsedRange but it errors out with the
runtime error "Method 'Value' of object 'Range' Failed". Hence why I
have tried your method of iArea to try and pinpoint which area it
errors on. But it has only proved that it seems to error out on the
last iArea (or the last ActiveSheet.UsedRange) it comes to. If there
is only 1 sheet then it will error on that sheet but if you do a FOR
EACH iSheet in ActiveWorkbook.Sheets then it sails through them all but
fails on the last sheet.

 

[Dave Peterson]

Do you have any protected worksheets?

Any merged cells?

You could add a

msgbox iArea.address

to see what the address is--then go see if there's anything special in those
cells.