VBA Excel: Findnext is looping & returning cells that do not contain the search string

The code finds the cell with "Balance" in it and saves the cell to a variable, foundcell, in order to find the balance column on the spreadsheet. There are multiple balance columns. I tested this with just the FIND and it worked by finding the first column with "Balance" in D8.

Once I added the loop for the findnext, it returns D8, G8, H8, I8 and A9, then crashes because A9 is wrong. FINDNEXT should only return I8 and stop, given there are 2 cells in the search range with the word "balance. G8, H8, and A9 do not contain the word "balance".

 Sub ReFill_Credit()
' Macro - ReFill formula columns for Credit sheet
'
Dim StartRow As Integer
Dim FindString As String
Dim foundcell As Range
Dim LastRow As Integer
Dim LastRow1 As Integer
Dim CopyCell As Range
Dim endcell As Range
Dim firstaddress As String
Dim SrchRng As Range
Dim Lastcell As Range
Dim coloffset As Integer
Dim cellofffset As Range
Dim colbal As Integer
With Sheets("Credit")
'citi = 4 '"d"
'chase = 9 '"i"
'colbal = citi
FindString = "Balance"
Set SrchRng = .Range("a1:k15")
Set Lastcell = SrchRng.Cells(SrchRng.Cells.Count)
'Find the Balance cell
Set foundcell = SrchRng.Find(What:=FindString, _ LookAt:=xlWhole, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ MatchCase:=False)
Debug.Print ""
'Test to see if anything was found
If Not foundcell Is Nothing Then firstaddress = foundcell.Address 'end If Debug.Print "firstaddress = " & firstaddress
'Loop to next "balance" column ***************************************************
Do Debug.Print "" Debug.Print "**********starting 'do until'*********" Debug.Print "foundcell (1st search) is: " & foundcell.Address
'Test if cycled thru back to the first cell StartRow = foundcell.Row + 1 Debug.Print "startrow is " & StartRow colbal = foundcell.Column FirstCell = Cells(StartRow, colbal)
'Finds the last non-blank cell on a sheet/range. coloffset = colbal - 1 'Debug.Print "colbal is: " & colbal Debug.Print "coloffset is: " & coloffset LastRow = .Columns(coloffset).Find(What:="*", _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Debug.Print "last row is " & LastRow LastRow1 = LastRow + 1
'Fill formula down COL D Set CopyCell = .Cells(StartRow, colbal) Set endcell = .Cells(LastRow1, colbal) Debug.Print "copycell = " & CopyCell Debug.Print "copycell.add = " & CopyCell.Address Debug.Print "endcell is " & endcell.Address
'=IF(ISTEXT(D8),D$7,D8)+C9 / starting at d9 CopyCell.Select Selection.AutoFill Destination:=.Range(CopyCell, endcell), Type:=xlFillValues Debug.Print "findstring is " & FindString
'Find next ********************************************************* Set foundcell = SrchRng.FindNext(foundcell) ' If foundcell.Address = firstfound Then Exit Do
Loop While Not foundcell Is Nothing And foundcell.Address <> firstaddress
End If
End With End Sub

1 Answer

I swapped out the block that calculated the LastRow using FIND:

 LastRow = .Columns(coloffset).Find(What:="*", _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row

with this instead:

LastRow = .Cells(Rows.Count, coloffset).End(xlUp).Row

Hopefully this method is identical to the previous method and has no unforeseen problems.