+ Reply to Thread
Results 1 to 2 of 2

If Found then do something

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    861

    If Found then do something

    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

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: If Found then do something

    'Check for Debit and Credit Columns
    Set rng = Cells.Find(What:="Debit", LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        For Each rCell In Range(Cells(2, rng.Column), Cells(LastRow, rng.Column + 1)) 'assuming your LastRow variable is correct.
            ' Do Something with the Cells in the Range
        Next rCell
    End If

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 04-15-2015, 02:58 PM
  2. Replies: 3
    Last Post: 03-04-2014, 04:55 AM
  3. IF Webpagecontent FOUND then X IF Webpage content NOT FOUND THEN Y
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-01-2013, 02:57 PM
  4. Check for values in a table and if found add value found in column to left to list
    By robhargreaves in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2013, 02:57 PM
  5. [SOLVED] Message (in one window) for each file found/not found
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-08-2013, 04:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1