1

I want to find a word with new line in the excel sheet using VBA. To do that I have written the VBA macro as follow:

Set columnIndex1 = headerRow.Find("AAA " & vbCrLf & "BBB", LookIn:=xlValues)

where the text I have to search is: *AAA newline BBB* VBA not detecting new line. What could be the solution?

Vityata
  • 42,633
  • 8
  • 55
  • 100
Siddharth692
  • 73
  • 9
  • 2
    You search for this `"AAA " & vbCrLf & "BBB"` are you sure your first line `AAA` ends with a space before the line break comes? Shouldn't it be ``"AAA" & vbCrLf & "BBB"``? – Pᴇʜ Jun 15 '18 at 12:00
  • 1
    As @Pᴇʜ mentioned. Ideally use the `TRIM()` function to get rid of any extra whitespaces and ensure the data you are comparing is correct. – Samuel Hulla Jun 15 '18 at 12:09
  • 3
    how do you realize the "newline" in the cell? By alt+enter? this results in "AAA" & Chr(10) & "BBB" and not "AAA" & vbCrLf & "BBB" - Excel 2010 – Denyo Jun 15 '18 at 12:09

1 Answers1

3

Record a macro, writing the value. Then see how excel interprets it and search for it. This is how it takes it, when Alt+Enter:

Sub Makro1()
'
' Makro1 Makro
'

'
    ActiveCell.FormulaR1C1 = "23AAA" & Chr(10) & "BBBSD"
    Range("A2").Select
End Sub

Then something like this works:

Public Sub TestMe()

    Dim colIndex As Range
    Set colIndex = Range("A1:Z100").Find("AAA" & Chr(10) & "BBB", LookIn:=xlValues)
    Debug.Print colIndex.Address

End Sub

Or use VbLf, which is the same as Chr(10). Differences Between vbLf, vbCrLf & vbCr Constants.

Vityata
  • 42,633
  • 8
  • 55
  • 100