Capturing result in cell from formula and Selection.Replace

[BAKELOVEMORE]

I have the following Selection.Replace code that I have used in other subs but I cannot figure out how to capture the cell contents from a formula and pass it to Selection.
I have used Range(activeRow,"H").copy and Selection.copy and passing formula to another variable. I have used .text in all kinds of ways and nothing changes to the string when I run the sub.
I also have tried other ways to join the text including Range((activeRow, "E") , (activeRow, "G")).select.
It does not error, I just do not get the replacement. Thanks for any help you can give me. I assume it is easy but I have searched and tried everything I can think of.

Sub GetStnProbs()
    Application.ScreenUpdating = False

    Const SubName As String = "GetStnProbs"  
    Const SheetName As String = "OBSERVATIONS"     
    If ActiveSheet.Name <> SheetName Then
        MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
    Exit Sub
    End If
 
    Dim activeRow As Long
    Dim formula As String
    Dim ActiveCell2 As Range
    activeRow = ActiveCell.Row

    formula = "=TEXTJOIN({"" & ""},TRUE,FILTER('STATION LIST'!$E$2:$G$66,'STATION LIST'!$C$2:$C$66 = E" & activeRow & "))"

    ActiveSheet.Cells(activeRow, "H").formula = formula
    ActiveCell = ActiveSheet.Cells(activeRow, "H")
    ActiveCell.Activate
    Set ActiveCell2 = ActiveCell
    Selection.Copy
    Selection.Replace What:="OOF", Replacement:="OUT OF FOCUS ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Clipboard ActiveCell
    Range("H65").PasteSpecial
    ActiveCell2.Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
End Sub

 

[Michael M]

don't have Excel atm, but does this help

Sub GetStnProbs()
    Application.ScreenUpdating = False

    Const SubName As String = "GetStnProbs"
    Const SheetName As String = "OBSERVATIONS"
    If ActiveSheet.Name <> SheetName Then
        MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
    Exit Sub
    End If
 
    Dim activeRow As Long, formula As String, ActiveCell2 As Range
    activeRow = ActiveCell.Row
    ActiveSheet.Cells(activeRow, "H").formula = "=TEXTJOIN({"" & ""},TRUE,FILTER('STATION LIST'!$E$2:$G$66,'STATION LIST'!$C$2:$C$66 = E" & activeRow & "))"
    ActiveCell = ActiveSheet.Cells(activeRow, "H")
    ActiveCell.Replace What:="OOF", Replacement:="OUT OF FOCUS ", LookAt:=xlPart, SearchOrder:=xlByRows
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Range("H65").Value = ActiveCell.Value
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
End Sub

 

[BAKELOVEMORE]

don't have Excel atm, but does this help

Sub GetStnProbs()
    Application.ScreenUpdating = False

    Const SubName As String = "GetStnProbs"
    Const SheetName As String = "OBSERVATIONS"
    If ActiveSheet.Name <> SheetName Then
        MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
    Exit Sub
    End If
 
    Dim activeRow As Long, formula As String, ActiveCell2 As Range
    activeRow = ActiveCell.Row
    ActiveSheet.Cells(activeRow, "H").formula = "=TEXTJOIN({"" & ""},TRUE,FILTER('STATION LIST'!$E$2:$G$66,'STATION LIST'!$C$2:$C$66 = E" & activeRow & "))"
    ActiveCell = ActiveSheet.Cells(activeRow, "H")
    ActiveCell.Replace What:="OOF", Replacement:="OUT OF FOCUS ", LookAt:=xlPart, SearchOrder:=xlByRows
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Range("H65").Value = ActiveCell.Value
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
End Sub

That does exactly what I need and pastes the correct string but I am getting an ERR 1004 PasteSpecial method of Range class failed on the line

ActiveCell.PasteSpecial Paste:=xlPasteValues

I have tried my old PasteSpecial and

wk.Worksheets("OBSERVATIONS").Range(activeRow, "H").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

 

[Michael M]

Change from

ActiveCell.PasteSpecial Paste:=xlPasteValues

to

ActiveCell.value=ActiveCell.value

 

[BAKELOVEMORE]

Change from

ActiveCell.PasteSpecial Paste:=xlPasteValues

to

ActiveCell.value=ActiveCell.value

BINGO! Thanks so much.

 

[Michael M]

Glad to help