sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

Not sure, but maybe this;
=INDIRECT("bd355")+3


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"FSt1" wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

hi
thanks for the reply but i was looking more of a vb solution which is why i
posted under programing.

regards
FSt1

"ryguy7272" wrote:

Not sure, but maybe this;
=INDIRECT("bd355")+3


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"FSt1" wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

You'd have to parse the formula into strings and numbers.

Option Explicit
Sub testme()

Dim myFormula As String
Dim LetterPart As String
Dim NumberPart As Long
Dim iCtr As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("AB151")

myFormula = myCell.Formula

For iCtr = 1 To Len(myFormula)
If IsNumeric(Mid(myFormula, iCtr, 1)) Then
'found the first number
'letterpart includes the equal sign
LetterPart = Left(myFormula, iCtr - 1)
NumberPart = Mid(myFormula, iCtr)
Exit For 'stop looking!
End If
Next iCtr

myCell.Offset(1, 0).Formula = LetterPart & NumberPart + 3
End Sub

You could parse the .FormulaR1C1 reference style formula, too. But I think that
could get confusing with adjusting the offsets (=r[xxx]c[yy] kind of thing.



FSt1 wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

--

Dave Peterson

Sub FormulaOffset()
' cell AB151 has formula =BD355
' cell AB152 needs formula =BC358 [BD355].offset(3,-1)
Dim a As String
Dim b As String
Dim c As String
a = [ab151].Formula
b = Right(a, Len(a) - 1)
c = Range(b).Offset(3, -1).Address(0, 0)
Range("AB152").Formula = "=" & c
End Sub

HTH,
--
Data Hog


"FSt1" wrote:

hi
thanks for the reply but i was looking more of a vb solution which is why i
posted under programing.

regards
FSt1

"ryguy7272" wrote:

Not sure, but maybe this;
=INDIRECT("bd355")+3


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"FSt1" wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

thanks dave. i appreciatie your effords.
and it looks good. it will have to be done on three different columns but i
think i can handle that.
someone in engineering got tired of coping and pasteing a line in their
summary section every morning and i was drawing a complete blank on how to do
this.

thanks again
regards
FSt1

"Dave Peterson" wrote:

You'd have to parse the formula into strings and numbers.

Option Explicit
Sub testme()

Dim myFormula As String
Dim LetterPart As String
Dim NumberPart As Long
Dim iCtr As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("AB151")

myFormula = myCell.Formula

For iCtr = 1 To Len(myFormula)
If IsNumeric(Mid(myFormula, iCtr, 1)) Then
'found the first number
'letterpart includes the equal sign
LetterPart = Left(myFormula, iCtr - 1)
NumberPart = Mid(myFormula, iCtr)
Exit For 'stop looking!
End If
Next iCtr

myCell.Offset(1, 0).Formula = LetterPart & NumberPart + 3
End Sub

You could parse the .FormulaR1C1 reference style formula, too. But I think that
could get confusing with adjusting the offsets (=r[xxx]c[yy] kind of thing.



FSt1 wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

--

Dave Peterson
.

If you know the formula/cell that's being used for the link, it would make more
sense than parsing the formula.



FSt1 wrote:

thanks dave. i appreciatie your effords.
and it looks good. it will have to be done on three different columns but i
think i can handle that.
someone in engineering got tired of coping and pasteing a line in their
summary section every morning and i was drawing a complete blank on how to do
this.

thanks again
regards
FSt1

"Dave Peterson" wrote:

You'd have to parse the formula into strings and numbers.

Option Explicit
Sub testme()

Dim myFormula As String
Dim LetterPart As String
Dim NumberPart As Long
Dim iCtr As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("AB151")

myFormula = myCell.Formula

For iCtr = 1 To Len(myFormula)
If IsNumeric(Mid(myFormula, iCtr, 1)) Then
'found the first number
'letterpart includes the equal sign
LetterPart = Left(myFormula, iCtr - 1)
NumberPart = Mid(myFormula, iCtr)
Exit For 'stop looking!
End If
Next iCtr

myCell.Offset(1, 0).Formula = LetterPart & NumberPart + 3
End Sub

You could parse the .FormulaR1C1 reference style formula, too. But I think that
could get confusing with adjusting the offsets (=r[xxx]c[yy] kind of thing.



FSt1 wrote:

sorry of the double post. last was a finger glitch.
if one had a formula in AB151 and the formula was =BD355, how would you add
3 to the cell reference in the formula and place formula =BC358 in range AB152

regards
FSt1

--

Dave Peterson
.


--

Dave Peterson