Formula arithmetic

[R]

Hi folks,

I'm sure the question has come-up before so please bear with me.

Here is the basic problem:

I have a formula that looks something like this: =sheetname!$E$101

What I want to do is change the cell reference from 101 to 105, i.e. add
4 to the numerical part of the formula. Any ideas? I have hundreds of
these formulas that need changing and I would prefer to do a global
change rather than do all these manually.

Thanks a lot.

-R

 

[Don Guillett]

edit>replace
or
name the cell and change the refers to when you want.

 

[R]

Don,

Thanks for your assistance. The problem is more complicated than it
appears. I have hundreds of cells that contain a formula of the form
=sheetname!$E$number

Where number could be any combination of digits. so what is really
needed is a way to extract the number part of the formula and add 4 to it.

e.g consider the formula =sheetname!$E$100. In this case I want to
extract the 100 and do 100+4 so that the new formula is
=sheetname!$E$104. Hope this helps to explain the problem.

-R

 

[Don Guillett]

I just recorded this and cleaned up a bit where I had 2 formulas =$e$5 and
=$f$5

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/28/2004 by Don Guillett
'

'
Range("I5:I17").Replace What:="$5", Replacement:="$9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[Stephen Dunn]

This piece of VBA will add 4 to each cell reference within any cells that
are selected on the active sheet.

Sub IncRowRefs()

Dim c As Range

For Each c In Selection
c.Formula = Left(c.Formula, InStrRev(c.Formula, "$")) & _
Val(Right(c.Formula, Len(c.Formula) - InStrRev(c.Formula, "$"))) + 4
Next c

End Sub


HTH
Steve D

 

[R]

Don,

this code will not work. This is not a simple replace problem. Using
your example $e$5, I need a function that extracts the 5 and does
something like =sum(5,4). the result of this evaluation should then
replace 5 in $e$5.