Find in Excel VBA

[LisaGreen]

Hi,

It's possible I'm having a grey ... no... not blonde... moment.

The attached workbook has code to find two curly brackets... or not.. in a range.

Can someone explain to me please why, when stepping through, it seems to ALWAYS find a second curly in column one when there isn't one??

TIA
Almost bald from tearing hair out... Lisa

[LisaGreen]

ARRGGHHHH!!!!!

After all this time I have a prob posting images in PMs!!

Here's a screen shot of the err I get when posting. I'm pretty sure the message is longer but not visible.

Lisa

[HansV]

Eileen's Lounge has problems with https, unfortunately. If you change the URL in the address bar to use http:// instead of https:// the error message shouldn't occur.

[HansV]

Even though you specify that the second Find starts after rlCurlyCell, it simply loops around and finds the same cell again.
You have to check whether the address of the second cell is different from that of the first cell. (In this specific example, you could check the row number instead.)

Code: Select all

Sub subTest()

Dim rlCurlyCell As Range
Dim rlLastCellInRange As Range
Dim rl1stCurlyCell  As Range
Dim rl2ndCurlyCell As Range
Dim rlFirstColumn As Range
Dim lnglColumn As Long
Dim lnglRows As Long
Dim strAddress As String

Application.FindFormat.Clear

lnglColumn = 1

lnglRows = Cells(1, lnglColumn).End(xlDown).Row

Set rlFirstColumn = Range(Cells(1, lnglColumn), Cells(lnglRows, lnglColumn))
Set rlLastCellInRange = Cells(lnglRows, lnglColumn)

' Curly?
Application.FindFormat.Clear
Set rlCurlyCell = rlFirstColumn.Find("{", rlLastCellInRange)
If Not rlCurlyCell Is Nothing Then
  ' Store the address of the 1st cell
  strAddress = rlCurlyCell.Address
  Set rl1stCurlyCell = rlCurlyCell
Else
  Set rl1stCurlyCell = Nothing
End If

' 2nd Curly?
If Not rlCurlyCell Is Nothing Then
  Set rlCurlyCell = rlFirstColumn.FindNext(After:=rlCurlyCell)
  If Not rlCurlyCell Is Nothing Then
    ' Compare the address of the 2nd cell to that of the 1st one
    If rlCurlyCell.Address <> strAddress Then
      Set rl2ndCurlyCell = rlCurlyCell
    Else
      Set rl2ndCurlyCell = Nothing
    End If
  Else
    Set rl2ndCurlyCell = Nothing
  End If
End If

' *********************************************************************
End Sub

[LisaGreen]

Eileen's Lounge has problems with https, unfortunately. If you change the URL in the address bar to use http:// instead of https:// the error message shouldn't occur.

Thank you Hans!

Lisa

[LisaGreen]

Even though you specify that the second Find starts after rlCurlyCell, it simply loops around and finds the same cell again.
You have to check whether the address of the second cell is different from that of the first cell. (In this specific example, you could check the row number instead.)

Code: Select all

Sub subTest()

Dim rlCurlyCell As Range
Dim rlLastCellInRange As Range
Dim rl1stCurlyCell  As Range
Dim rl2ndCurlyCell As Range
Dim rlFirstColumn As Range
Dim lnglColumn As Long
Dim lnglRows As Long
Dim strAddress As String

Application.FindFormat.Clear

lnglColumn = 1

lnglRows = Cells(1, lnglColumn).End(xlDown).Row

Set rlFirstColumn = Range(Cells(1, lnglColumn), Cells(lnglRows, lnglColumn))
Set rlLastCellInRange = Cells(lnglRows, lnglColumn)

' Curly?
Application.FindFormat.Clear
Set rlCurlyCell = rlFirstColumn.Find("{", rlLastCellInRange)
If Not rlCurlyCell Is Nothing Then
  ' Store the address of the 1st cell
  strAddress = rlCurlyCell.Address
  Set rl1stCurlyCell = rlCurlyCell
Else
  Set rl1stCurlyCell = Nothing
End If

' 2nd Curly?
If Not rlCurlyCell Is Nothing Then
  Set rlCurlyCell = rlFirstColumn.FindNext(After:=rlCurlyCell)
  If Not rlCurlyCell Is Nothing Then
    ' Compare the address of the 2nd cell to that of the 1st one
    If rlCurlyCell.Address <> strAddress Then
      Set rl2ndCurlyCell = rlCurlyCell
    Else
      Set rl2ndCurlyCell = Nothing
    End If
  Else
    Set rl2ndCurlyCell = Nothing
  End If
End If

' *********************************************************************
End Sub

Ohhhhh I just soooo knew it was something simple that I was misssing!!!

Thank you very much Hans... Again.

Hugs
Lisa