Please help (need a code check)

[Aaron Cooper]

I am trying to run a macro that will change any cell that links to a tab
called data! to an absolute reference. Below is the code that I have
developed with help from others an articles so far, but I cannot figure
out why this does not work. Can anyone out there please help me?

Thanks, this is driving me crazy.

Sub ConvertFormulae()

For Each wb In Workbooks
For Each sh In wb.Worksheets
For Each Cell In sh.UsedRange
If Cell Like "*data!*" Then
Formula1 = Cell.Formula
newformula = Application.ConvertFormula _
(Formula:=MyFormula1, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute)
Cell = newformula
End If
Next Cell
Next sh
Next wb

End Sub

 

[Doug Glancy]

Aaron,

It's a good idea to declare variables. It helps you avoid errors like using
MyFormula1 when you meant Formula1. Also changed your Like comparison to
c.formula. Also, changed the variable name from Cell to c, since it's a
good idea not to name variables close to Excel reserved words. I think it
does what you want now:

Sub ConvertFormulae()

Dim wb As Workbook
Dim sh As Worksheet
Dim c As Range
Dim newformula As String, formula1 As String

For Each wb In Workbooks
For Each sh In wb.Worksheets
For Each c In sh.UsedRange
If c.Formula Like "*data!*" Then
formula1 = c.Formula
newformula = Application.ConvertFormula _
(Formula:=formula1, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute)
c = newformula
End If
Next c
Next sh
Next wb

End Sub

hth,

Doug

 

[Bob Phillips]

Hi Aaron,

Couple of problems. Firstly, you should declarev your variables and use
Option Explicit, it would save you the first problem.

Firstly, you set a variable called Formula1, and then try to use one called
myFormula1.

Secondly, you text cell tocontain 'data'. As you don't specify the dcell
property, it assumes the Value property, an that will be the value in the
target cell. You should use the Formula propery.

Here we go.


Sub ConvertFormulae()
Dim wb As Workbook
Dim sh As Worksheet
Dim cell As Range
Dim myFormula1 As String
Dim newFormula As String

For Each wb In Workbooks
For Each sh In wb.Worksheets
For Each cell In sh.UsedRange
If cell.Formula Like "*data!*" Then
myFormula1 = cell.Formula
newFormula = Application.ConvertFormula _
(Formula:=myFormula1, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute)
cell = newFormula
End If
Next cell
Next sh
Next wb

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Also, changed the variable name from Cell to c, since it's a

good idea not to name variables close to Excel reserved words.

How close is too close Doug?

you appear to violate your own guidance with
formula1 = c.Formula

Why is formula1 distinctive but cell isn't?

If the word isn't reserved there is no reason not to use it.

 

[Doug Glancy]

Thanks Tom, I'll remember that.

Doug

How close is too close Doug?

you appear to violate your own guidance with
formula1 = c.Formula

Why is formula1 distinctive but cell isn't?

If the word isn't reserved there is no reason not to use it.

 

[Paulw2k]

Hi Aaron,
The line which is causing you problems is:

If Cell Like "*data!*" Then

Unless expressed the default property for cells is VALUE. What your
procedue is doing is looking at the value, not the formula underneath.
So
If Cell.formula Like "*data!*" Then

will do the necessary.
Also tighten the search over the UsedRange
.......
On Error Resume Next
For each Cell in sh.UsedRange.SpecialCells(xlCellTypeFormulas)
....

Next Cell
On Error GoTo 0

........
There is no need to test each cell, Excel knows the ones that have. Encase
this loop with Error statements which take care of none being on the sheet.

Regards

Paul