Link to home
Start Free TrialLog in
Avatar of Alex Campbell
Alex CampbellFlag for United States of America

asked on

How to pull text from inside a formula in Excel 2010?

I have some formulas that are on the wrong rows.
For example, the formula is on row 782 should say
=IF('MS Only Alt & Kept Commands'!M782<>'MS Only Alt & Kept Commands'!J782,'MS Only Alt & Kept Commands'!$J782,"")
but actually says
=IF('MS Only Alt & Kept Commands'!M781<>'MS Only Alt & Kept Commands'!J781,'MS Only Alt & Kept Commands'!$J781,"")

Is there a way to pull 781 out of the formula and show it.?
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

You can do such a thing with the help of VBA.
I don't know what you mean by "show it" but this will update the formulas on row 782.

Sub ReplaceRowNumber()
Dim cel As Range
With ActiveSheet.Rows(782)
    For Each cel In .Cells
        cel.Formula = Replace(cel.Formula, "781", "782")
    Next
End With
End Sub

Open in new window

Avatar of Alex Campbell

ASKER

It would show in a separate cell.
I want to do that since there could be a number of rows that are mismatched.
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think this update to Syed's code solves the problem he refers to. (Edit: changed code to remove "M")

Function chkrowfrmla(frmla As Range)

Dim strParts() As String
Dim strTemp As String
strTemp = Replace(frmla.Formula, "<", "!")
strParts = Split(strTemp, "!")
If UBound(strParts) < 1 Then
    chkrowfrmla = ""
    Exit Function
End If
strTemp = Replace(strParts(1), "M", "")
If strTemp = frmla.Row Then
    chkrowfrmla = ""
Else
    chkrowfrmla = "Mismatch"
End If
End Function

Open in new window

This works just fine. Thanks