+ Reply to Thread
Results 1 to 5 of 5

Creating a VBA loop to search for values, stop when a cell value is 0

Hybrid View

jfgay Creating a VBA loop to search... 07-29-2013, 11:03 AM
ragulduy Re: Creating a VBA loop to... 07-29-2013, 11:27 AM
jfgay Re: Creating a VBA loop to... 07-29-2013, 12:37 PM
ragulduy Re: Creating a VBA loop to... 07-30-2013, 03:38 AM
jfgay Re: Creating a VBA loop to... 07-30-2013, 10:30 AM
  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    MA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Creating a VBA loop to search for values, stop when a cell value is 0

    I currently have a macro that looks up a user inputted value/word on one sheet, "Instructions", and then searches for it on another, "Forecast", then deletes the column the value/word is found in and also deletes the columns to the right of it until the next populated cell. This works perfectly and I don't want to change it. What I would like to do is instead of just looking for one inputted value/word, I want it to search for all the words/values in the column until it reaches a cell with the value 0. I've tried a "Do Until" loop, but I can't seem to have it do what I want. Here is the code.
    Sub SelectVariableRange()
    
    
    Dim strFind As String
    Dim rngFind As Range
    Dim i As Integer
    
    
    strFind = Sheets("Instructions").ActiveCell("H56").Value
    Do Until ActiveCell.Value = 0
    
    
        Set rngFind = Sheets("Forecast").Cells.Find(What:=strFind, LookAt:=xlPart)
    'check if value is found
        Do While Not rngFind Is Nothing
            i = 0
             Do While rngFind.Offset(0, i + 1) = ""
                  i = i + 1
             Loop
         rngFind.Resize(1, i + 1).EntireColumn.Delete Shift:=xlShiftToLeft
         Set rngFind = Sheets("Forecast").Cells.Find(What:=strFind, LookAt:=xlPart)
        Loop
    
    ActiveCell.Offset(1, 0).Select
    Loop
    
    
    End Sub
    The parts I added in (shown above) to try to search for values/text until it reaches a cell with the value 0 are:
    Do Until ActiveCell.Value = 0
    
    ActiveCell.Offset(1, 0).Select
    Loop
    Everything else I have works perfectly, its just this part I need help with. (I'm trying to search starting at cell H56 and searching down the column sequentially- that's why I have the offset of (1,0). I want to search until there is a cell in column H that has a value of 0.)

    Hopefully that isn't worded too poorly. Any help is greatly appreciated. Thank you!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Creating a VBA loop to search for values, stop when a cell value is 0

    You never change the value of strFind because it is outside of your loop, so you will always search for the same word...
    Try
    Sub SelectVariableRange()
    Dim strFind As String
    Dim rngFind As Range
    Dim i As Integer
    Dim count
    count = 56
    Do Until Range("H" & count) = ""
        strFind = Sheets("Instructions").ActiveCell("H" & count).Value
        Set rngFind = Sheets("Forecast").Cells.Find(What:=strFind, LookAt:=xlPart)
        'check if value is found
        Do While Not rngFind Is Nothing
            i = 0
             Do While rngFind.Offset(0, i + 1) = ""
                  i = i + 1
             Loop
            rngFind.Resize(1, i + 1).EntireColumn.Delete Shift:=xlShiftToLeft
            Set rngFind = Sheets("Forecast").Cells.Find(What:=strFind, LookAt:=xlPart)
        Loop
    count = count + 1
    Loop
    End Sub

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    MA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Creating a VBA loop to search for values, stop when a cell value is 0

    Ok I think I understand what you're saying as far as changing the value of StrFind. When I try the count method, I get an error on this line:
    strFind = Sheets("Instructions").ActiveCell("H" & count).Value
    It says "Object doesn't support this method or property..

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Creating a VBA loop to search for values, stop when a cell value is 0

    Sorry, my mistake, change it to:
    strFind = Sheets("Instructions").Range("H" & count).Value

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    MA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Creating a VBA loop to search for values, stop when a cell value is 0

    Ohh yes, that makes sense. Looks like its working! Thanks a lot, saved the day!!

+ 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. Creating a loop which looks at column A values and inserts a row where the data changes
    By markmccarron in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 06:52 AM
  2. [SOLVED] How to make a loop stop running when two values get close to each other instead of equal
    By Clue_Less in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2012, 07:06 PM
  3. Creating a simple loop to compare values
    By marvelous1 in forum Excel General
    Replies: 1
    Last Post: 09-18-2009, 02:35 PM
  4. loop to stop and wait for cell value change.
    By ajwhipple in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-03-2007, 01:04 PM
  5. How to stop a loop at a blank cell
    By sunandm00n in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2007, 11:16 AM

Tags for this Thread

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