modify formula

  • Thread starter Thread starter FSt1
  • Start date Start date
F

FSt1

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
 
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.
 
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,
 
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 said:
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.


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
Click to expand...
Click to expand...
 
If you know the formula/cell that's being used for the link, it would make more
sense than parsing the formula.


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 said:
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.


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top