Replacing formulas in Excel
 

Formulas usually work only in a specific cell because the adjust addresses
depending on where they are used. Are the formulas all exactly
the same ?

Can you give an example of what you have and what you want.

Look at the Replace function in VBE HELP.

Also Ctrl+H replace in the worksheet will also work on formulas.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Vijay" wrote in message ...

I have a workbook with 15 worksheets in it. I need to go through all the cells in the entire workbook and replace certain formulas
with values and some with other formulas. I am currently doing this task using VB 6.0 by looping through all the cells in all the
worksheets of the workbook, but this process goes on forever and ever and I am yet to see if finish. Is there a quicker and easier
way to accomplish this?

Thanks in advance.

- Vijay

Replacing formulas in Excel
 

David

Well, the formulas are not the same everywhere, but the function used in the formulas are the same
e.g. some of the formulas are =SUM(A1:A3), =SUM(B4:B9), SUM(D20:D31), etc. But the common thing in all the formulas is that all the formulas that I need replaced have the SUM() in them. I need these replaced with its resultant values. But at the same time, I need to leave every other cell in the worksheet as it is. I hope I am clear in what I am saying

Here is what I currently have.... its in VB 6.

' wkb is source workboo
' wkb1 is destination workboo
' I copy each worksheet from source to destination and then loop thru' the cells in the destination to check the formulas in them and perform the replace

For Each wks In wkb.Worksheet
isHidden = wks.Visibl

wks.Visible = xlSheetHidde
wks.Activat
Cells.Selec
Selection.Cop
wkb1.Worksheets(i).Activat

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals

Range("A1").Selec
wkb1.Worksheets(i).Name = wks.Nam

' Loop thru' each cell to strip off the formula for specific cells
For Each cel In wkb1.Worksheets(i).UsedRang
If cel.HasFormula The
curcell = cel.Formul
Els
curcell = cel.Valu
End I

cel.Cop

Range(cel.Address).Selec

If InStr(0, curcell, "=SUM(", 1) 0 The
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Els
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
End I

Nex

wkb1.Worksheets(i).Visible = isHidde

i = i +
Nex

Thanks
- Vija

Replacing formulas in Excel
 

Try this:

Sub test()
Dim testrange()
Dim fillrange()
Dim theusedrange As Range
Dim firstrow As Integer, lastrow As Integer, firstcol As Integer,
lastcol As Integer
Dim i As Integer, j As Integer
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
firstrow = ActiveSheet.UsedRange.Row
lastrow = ActiveSheet.UsedRange.Rows.Count + firstrow - 1
firstcol = ActiveSheet.UsedRange.Column
lastcol = ActiveSheet.UsedRange.Columns.Count + firstcol - 1
Set theusedrange = Range(Cells(firstrow, firstcol),
Cells(lastrow, lastcol))
ReDim testrange(firstrow To lastrow, firstcol To lastcol)
ReDim fillrange(firstrow To lastrow, firstcol To lastcol)
For i = firstrow To lastrow
For j = firstcol To lastcol
fillrange(i, j) = Cells(i, j).Value
testrange(i, j) = Cells(i, j).Formula
If Left(testrange(i, j), 5) = "SUM(" Then
fillrange(i, j) = testrange(i, j)
End If
Next j
Next i
theusedrange.Value = fillrange
Set theusedrange = Nothing
Next wks
Application.ScreenUpdating = True
End Sub


Vijay wrote:

David,

Well, the formulas are not the same everywhere, but the function used in the formulas are the same.
e.g. some of the formulas are =SUM(A1:A3), =SUM(B4:B9), SUM(D20:D31), etc. But the common thing in all the formulas is that all the formulas that I need replaced have the SUM() in them. I need these replaced with its resultant values. But at the same time, I need to leave every other cell in the worksheet as it is. I hope I am clear in what I am saying.

Here is what I currently have.... its in VB 6.0

' wkb is source workbook
' wkb1 is destination workbook
' I copy each worksheet from source to destination and then loop thru' the cells in the destination to check the formulas in them and perform the replace.

For Each wks In wkb.Worksheets
isHidden = wks.Visible

wks.Visible = xlSheetHidden
wks.Activate
Cells.Select
Selection.Copy
wkb1.Worksheets(i).Activate

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Range("A1").Select
wkb1.Worksheets(i).Name = wks.Name

' Loop thru' each cell to strip off the formula for specific cells.
For Each cel In wkb1.Worksheets(i).UsedRange
If cel.HasFormula Then
curcell = cel.Formula
Else
curcell = cel.Value
End If

cel.Copy

Range(cel.Address).Select

If InStr(0, curcell, "=SUM(", 1) 0 Then
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Else
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If

Next

wkb1.Worksheets(i).Visible = isHidden

i = i + 1
Next


Thanks!
- Vijay

Replacing formulas in Excel- correction
 

Option Explicit

Sub test()
Dim testrange()
Dim fillrange()
Dim theusedrange As Range
Dim firstrow As Integer, lastrow As Integer, firstcol As Integer,
lastcol As Integer
Dim i As Integer, j As Integer
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
firstrow = ActiveSheet.UsedRange.Row
lastrow = ActiveSheet.UsedRange.Rows.Count + firstrow - 1
firstcol = ActiveSheet.UsedRange.Column
lastcol = ActiveSheet.UsedRange.Columns.Count + firstcol - 1
Set theusedrange = Range(Cells(firstrow, firstcol),
Cells(lastrow, lastcol))
ReDim testrange(firstrow To lastrow, firstcol To lastcol)
ReDim fillrange(firstrow To lastrow, firstcol To lastcol)
For i = firstrow To lastrow
For j = firstcol To lastcol
fillrange(i, j) = Cells(i, j).Formula
testrange(i, j) = Cells(i, j).Value
If Left(fillrange(i, j), 5) = "=SUM(" Then
fillrange(i, j) = testrange(i, j)
End If
Next j
Next i
theusedrange.Formula = fillrange
Set theusedrange = Nothing
Next wks
Application.ScreenUpdating = True
End Sub