CODEWITHSUNDEEP
vbafindcell
Roberto Bahia
Roberto Bahia

Reputation: 105

Vba excel. Find text insie cell with line break

I have the following code. It searches one column for a specific value. It works fine, but if the cell have line break, the code does not search the second line.

vardestinolinha = ThisWorkbook.Sheets("base").Range("a11").End(xlDown).Row

a = 10
k = a
For i = a To vardestinolinha
    Search = ThisWorkbook.Sheets(NomeTabela).Range("a2")
    Replacement = ThisWorkbook.Sheets(NomeTabela).Range("c" & i)
    varposicao = ThisWorkbook.Sheets(NomeTabela).Range("b" & i) '''''

    Set rngFind = ThisWorkbook.Sheets("base").Columns(2).Find(What:=Search, LookIn:=xlValues, lookat:=xlPart)
    Do While Not rngFind Is Nothing
        tamanho = Len(rngFind)
        p = InStr(1, rngFind, Search, vbTextCompare)

        If p > 0 Then
            ThisWorkbook.Sheets("base").Cells(k, 5) = ThisWorkbook.Sheets("base").Cells(k, 3)
            k = k + 1
        End If
        Set rngFind = ThisWorkbook.Sheets("base").Columns(2).FindNext
    Loop
    k = i + 1
Next

I want the code to search an entire cell even when there are line breaks.

Upvotes: 1

Views: 4863

Answers (1)

Chipperzs
Chipperzs

Reputation: 21

If the text was entered in the cell useing the Alt+Enter method you can use this in your VBA:

" & Chr(10) & "

Here is the .Find method that I have used.

Private Sub CommandButton1_Click()
Set RngClosedDate = Range("A1:Z10").Find(What:="Closed" & Chr(10) & "(Date)", LookAt:=xlWhole, LookIn:=xlValues)
      ' If the text that is searched for from the line above is not found then a message box is displayed and sub function is exitied
      If RngClosedDate Is Nothing Then
        MsgBox "Closed (Date) Column Header Not found.  Cannot sort or format records."
        Exit Sub
      End If
End Sub

Upvotes: 2

Related Questions