This VBA code is working but sometimes the Find and Replace happens on the specified sheet "CSV" and other "Function" sheet with the same cell content. I'm guessing it because the "Function" sheet also has the range of cells with values to then find in the "CSV" sheet. But the .Replace function calls out the "CSV" sheet. ???
VBA Code:
Sub Button3_Click()
'***Find & Replace - Link to Function Sheet Cells
Dim sh As Worksheet, rng As Range, SFind As String, SReplace As String, fRng As Range, CL As Range
Set sh = Worksheets("CSV")
With sh
Set fRng = Worksheets("Function").Range("A10:A1000")
Worksheets("Function").Range("B10:C1000").Replace What:="-", Replacement:="_"
For Each CL In fRng
'If Not CL Is Nothing Then
If Not IsEmpty(CL.Value) Then
SFind = CL.Value
SReplace = "=Function!" & CL.Offset(, 1).Address
If Not IsEmpty(CL.Offset(, 1)) Then
Worksheets("CSV").Range("A1:D1000").Replace What:=SFind, Replacement:=SReplace, LookAt:=xlWhole
End If
End If
Next CL
End With
End Sub