Apply Find and Replace Formula to cells in specific row in selected column

[HelvetiiOnExcel]

Hi Everyone,

Long time lurker and first time poster. I am looking for advice on creating a macro that will apply the find and replace function to cells in specific rows of a selected column. I.e. user will select column 'E', from there cells in rows 5,9,10,23,45, would have all text after a '-' in their formula removed (-* with find and replace) and then cells in row 7, 16, 34, would have all text before a '+' removed (*+) with find and replace. Would appreciate any and all support. I've gotten the part of identifying the column value and such, but applying that to specific cells has been tough.
Thanks.

 

[Zot]

You meant user will select only column(s) or just specific group of rows in column(s)?
If just column, then the Find will look for any row in that column for '-' or '+' and perform task according to sign found?

 

[HelvetiiOnExcel]

Hi! Thanks for responding! Allow me to clarify. The user will select the column to apply the macro. Since there are other rows that use the - or + functions, the macro would have to work in only specific rows 5,9,10,23,45 (for -) and 7, 16, 34 (for +). The other rows remain untouched.

 

[Zot]

Hi! Thanks for responding! Allow me to clarify. The user will select the column to apply the macro. Since there are other rows that use the - or + functions, the macro would have to work in only specific rows 5,9,10,23,45 (for -) and 7, 16, 34 (for +). The other rows remain untouched.

Those rows specified are always fixed?

 

[HelvetiiOnExcel]

That's right! It's always fixed rows

 

[HelvetiiOnExcel]

That's right! It's always fixed rows

 

[Zot]

Try this. Select the column and run the code

Sub Test()

Dim strFind As String
Dim nCol As Long, nLoc As Long
Dim Element, ArryPlus(), ArryMinus()
Dim rngData As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.ActiveSheet
nCol = ActiveCell.Column

ArryPlus = Array(7, 16, 34)
ArryMinus = Array(5, 9, 10, 23, 45)

For Each Element In ArryPlus
    nLoc = InStr(ws.Cells(Element, nCol), "+")
    If Not nLoc = 0 Then
        strFind = Left(ws.Cells(Element, nCol), nLoc - 1)
        ws.Cells(Element, nCol) = Replace(ws.Cells(Element, nCol), strFind, "")
    End If
Next
For Each Element In ArryMinus
    nLoc = InStr(ws.Cells(Element, nCol), "-")
    If Not nLoc = 0 Then
        strFind = Right(ws.Cells(Element, nCol), Len(ws.Cells(Element, nCol)) - nLoc)
        ws.Cells(Element, nCol) = Replace(ws.Cells(Element, nCol), strFind, "")
    End If
Next

End Sub