Excel VBA: Findnext é looping e células de retorno que não contêm a seqüência de pesquisa

Question

O código encontra a célula com "Balance" e salva a célula em uma variável, foundcell , para encontrar a coluna de saldo na planilha. Existem várias colunas de saldo. Eu testei isso com apenas o FIND e funcionou encontrando a primeira coluna com "Balance" no D8.

Depois de adicionar o loop para o findnext , ele retorna D8, G8, H8, I8 e A9, depois falha porque A9 está errado. FINDNEXT só deve retornar I8 e parar, dado que existem 2 células no intervalo de pesquisa com a palavra "balance. G8, H8 e A9 não contêm a palavra" balance ".

    Sub ReFill_Credit()
' Macro - ReFill formula columns for Credit sheet
'https://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba

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

    

Answer 1

Eu troquei o bloco que calculou o LastRow usando FIND :

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

com isso:

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

Espero que este método seja idêntico ao método anterior e não tenha problemas imprevistos.