Replace Word in Cell with Value from Another Cell

[tomgrandy]

I'm new to VBA and am trying to replace a word found in L3 (AUCTION_DATE) with the value from H3 (Date Lot Sold - there are 70,000 entries and not all are 02-04-23) and then have this repeated throughout the worksheet for all items in Row L that have the words "AUCTION_DATE".

Basically replace the words "AUCTION_DATE" in each row of Column L with the value in the corresponding cell value in Column H (for each row).

Spent the best part of two hours trying to search for a site that had the solution, but none of the Find and Replace VBA codes would pull from another cell.

Any help greatly appreciated!!

 

[Akuini]

If an Array would speed up the process (takes about 20 seconds for each VBA to run) then that would be a huge timesaver!:

Another option:

Sub KeywordFlakedArtifacts_2()
Dim i As Long
Dim va, vb, x
Dim t As Double
t = Timer
      va = Range("c2:c70000")
      vb = Range("J2:J70000")
        For Each x In Array("ABBEY", "ACATITA", "ADDISON MICRO-DRILL")
            x = UCase(x)
            For i = 1 To UBound(va, 1)
                If InStr(1, UCase(va(i, 1)), x, vbBinaryCompare) > 0 Then vb(i, 1) = "POINT"
            Next
        Next
     Range("J2").Resize(UBound(vb, 1), 1) = vb
Debug.Print "Completion time:  " & Format(Timer - t, "0.00") & " seconds"
End Sub