EXCEL Find/Replace LookIn: VALUE only Not function in cell

Question

I have a formula in C3 that works great leaving me with TEXT that I can search on.

=IF(IF(B3="0"****"")

There could be hundreds of the word PRINT in my sheet based upon this formula.

When I Find/Replace the word PRINT with SENT -- it changes the cell formula also, so TODAY(),"PRINT","") changes to TODAY(),"SENT","") in all cells.

How do I Find/Replace without effecting the formula ? -- I do not mind if it replaces the formula with the word SENT

I have tried the GoTo - special & constants and that does not seem to work ?

Answer 1

Okay, let's see how your code works. The important part is this:

For i = 1 To N
      If Cells(i, "C").Value = "PRINT" Then
            Cells(i, "C").Value = "=(""SENT "" & TEXT(TODAY(),""m/d/yy""))"

This code reads every cell in column C and compares if the cells contains "PRINT". If so the code writes a formula into the cell.

So far, so good, but I see several issues:

a) The code runs a long time, because of the access of every cell.

b) The formula contains TODAY(), means today you see the correct date. But if you open the file tomorrow, the formula is calculated again and it contains the date of tomorrow! That is obvious wrong.

c) TODAY is a volatile function, means it is recalculated every time Excel performs a calculation.

d) The code did not find "print" or other spellings.

So what's the way out of this?

a) If you want to search for "PRINT" then perform a search! In VBA your can use Range.Find (Write that in the VBA editor, press F1 and read the article for the details how it works).

Range.Find is much faster, because you call a "internal" function in Excel that does the work for you.

b) Do not write formulas, perform the calculation in VBA and write the value. If you do so, the date is fixed and always stays the same.

c) and d) we can forget this issues.

For practice purposes try to solve this problem with Range.Find then it would be easier for your to understand how my Function FindAll below works.

Store the function in a safe place (for example, in your PERSONAL.XLSB) I bet you'll need it often in the future. :-)

Andreas.

Sub Test()
  Dim All As Range
  
  Set All = FindAll(Columns("C"), "PRINT")
  If All Is Nothing Then
    MsgBox "No 'PRINT' cells found.", vbInformation
  Else
    All.Value = "SENT " & Date
  End If
End Sub

Function FindAll(ByVal Where As Range, ByVal What, _
    Optional ByVal After As Variant, _
    Optional ByVal LookIn As XlFindLookIn = xlValues, _
    Optional ByVal LookAt As XlLookAt = xlWhole, _
    Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
    Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
    Optional ByVal MatchCase As Boolean = False, _
    Optional ByVal SearchFormat As Boolean = False) As Range
  'Find all occurrences of What in Where (Windows version)
  Dim FirstAddress As String
  Dim C As Range
  'From FastUnion:
  Dim Stack As New Collection
  Dim Temp() As Range, Item
  Dim i As Long, j As Long

  If Where Is Nothing Then Exit Function
  If SearchDirection = xlNext And IsMissing(After) Then
    'Set After to the last cell in Where to return the first cell in Where in front if _
      it match What
    Set C = Where.Areas(Where.Areas.Count)
    'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
    'Set After = C.Cells(C.Cells.Count)
    Set After = C.Cells(C.Rows.Count * CDec(C.Columns.Count))
  End If

  Set C = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
    SearchDirection, MatchCase, SearchFormat:=SearchFormat)
  If C Is Nothing Then Exit Function

  FirstAddress = C.Address
  Do
    Stack.Add C
    If SearchFormat Then
      'If you call this function from an UDF and _
        you find only the first cell use this instead
      Set C = Where.Find(What, C, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
    Else
      If SearchDirection = xlNext Then
        Set C = Where.FindNext(C)
      Else
        Set C = Where.FindPrevious(C)
      End If
    End If
    'Can happen if we have merged cells
    If C Is Nothing Then Exit Do
  Loop Until FirstAddress = C.Address

  'FastUnion algorithm :copyright: Andreas Killer, 2011:
  'Get all cells as fragments
  ReDim Temp(0 To Stack.Count - 1)
  i = 0
  For Each Item In Stack
    Set Temp(i) = Item
    i = i + 1
  Next
  'Combine each fragment with the next one
  j = 1
  Do
    For i = 0 To UBound(Temp) - j Step j * 2
      Set Temp(i) = Union(Temp(i), Temp(i + j))
    Next
    j = j * 2
  Loop Until j > UBound(Temp)
  'At this point we have all cells in the first fragment
  Set FindAll = Temp(0)
End Function

Answer 2

Hi Andreas,

Thank you very much for your response.

I ended up using the following formula - again I appreciate your time and will store your solution for my future VBA.

Thanks, Ed

Sub FindPRINTchangetoSENTcolumnC()

'

' FindPRINTchangetoSENTcolumnC Macro

'

    Dim N As Long, i As Long

    N = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 1 To N

        If Cells(i, "C").Value = "PRINT" Then

            Cells(i, "C").Value = "=(""SENT "" & TEXT(TODAY(),""m/d/yy""))"

        End If

    Next i

End Sub

Answer 3

Perform a Find with Values and Find All.

After that all cells with PRINT are selected.

Now write SENT into the formula bar and press CTRL+ENTER

Andreas.

Answer 4

Hi..

The Values option is not available under Find/Replace..    Any other suggestion is appreciated ?

I have also written VBA to Find, then type over, but am struggling when the find errors out...

Thanks, Ed...

Note:   Formulas, Values and Comments are only available on the Find tab; only Formulas are available on the Replace tab.

Answer 5

Change the Lookin option to Values and try again.
https://support.office.com/en-us/article/find-o...

Andreas.