As part of a larger script, I'm attempting to search a worksheet for the word "Debit" And then if found, I want to loop through a range of cells in that column and the column next to (Credit)
I put this together and it works. But I'm guessing there is may be an easier way? I'm currently first searching, and then activating the cell to get the column number/letter and then working with offsets.
'Check for Debit and Credit Columns
Set rng = Cells.Find(What:="Debit", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
Cells.Find(What:="Debit", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
DebitCol = ColumnLetter(ActiveCell.Column) 'Returns column H, I, etc. Could vary depending on file.
Set InputRng = Range(DebitCol & "2:" & ColumnLetter(ActiveCell.Offset(0, 1).Column) & LastRow)
For Each rCell In InputRng
' Do Something with the Cells in the Range
Next rCell
End If
Bookmarks