Better way....
 

I've written this different before, but I can't remember what I did to speed
it up. Also, I would like it to look at the entire sheet but only the cells
that have a formula so you don't have to select a range...It's too slow to
be practical right now. Btw the code is finding all the vlookup's and
copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!

Better way....
 

Are the only formulas in the spreadsheet vlookup formulas? If so then it
would obviously make more sense simply to copy the entire sheet/range and
paste special as values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Job" wrote in message
...
I've written this different before, but I can't remember what I did to
speed
it up. Also, I would like it to look at the entire sheet but only the
cells
that have a formula so you don't have to select a range...It's too slow to
be practical right now. Btw the code is finding all the vlookup's and
copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!

Better way....
 

Nope, that's the point...I want to keep all formula's except the vlookups...
"Ken Wright" wrote in message
...
Are the only formulas in the spreadsheet vlookup formulas? If so then it
would obviously make more sense simply to copy the entire sheet/range and
paste special as values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Job" wrote in message
...
I've written this different before, but I can't remember what I did to
speed
it up. Also, I would like it to look at the entire sheet but only the
cells
that have a formula so you don't have to select a range...It's too slow
to
be practical right now. Btw the code is finding all the vlookup's and
copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!

Any Takers?
 

Anyone...


"Job" wrote in message
...
I've written this different before, but I can't remember what I did to
speed it up. Also, I would like it to look at the entire sheet but only
the cells that have a formula so you don't have to select a range...It's
too slow to be practical right now. Btw the code is finding all the
vlookup's and copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!

Better way....
 

Thanks Dave...

"Dave Peterson" wrote in message
...
How about:

Option Explicit
Sub VlookupToValues()

Dim myFormulaRng As Range
Dim FoundCell As Range
Dim WhatToFind As String

Application.ScreenUpdating = False

With ActiveSheet
On Error Resume Next
Set myFormulaRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myFormulaRng Is Nothing Then
MsgBox "No Formulas found!"
Else
WhatToFind = "vlookup"
With myFormulaRng
Do
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(1),
LookIn:=xlFormulas, _
lookat:=xlPart)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Value = FoundCell.Value
End If
Loop
End With
End If
End With

Application.ScreenUpdating = True

End Sub

You had a loop increment "a" at the top of your code. If you want the
number of
cells with formulas, you could use:

msgbox myformularng.cells.count

But both this and the loop will slow your code down. (So I wouldn't do
it!)

job wrote:

Nope, that's the point...I want to keep all formula's except the
vlookups...
"Ken Wright" wrote in message
...
Are the only formulas in the spreadsheet vlookup formulas? If so then
it
would obviously make more sense simply to copy the entire sheet/range
and
paste special as values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Job" wrote in message
...
I've written this different before, but I can't remember what I did to
speed
it up. Also, I would like it to look at the entire sheet but only the
cells
that have a formula so you don't have to select a range...It's too
slow
to
be practical right now. Btw the code is finding all the vlookup's and
copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!





--

Dave Peterson