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.
