+ Reply to Thread
Results 1 to 2 of 2

Loop Macro needs to return blanks in cell for segments that are not found between loops

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    US
    MS-Off Ver
    16
    Posts
    18

    Loop Macro needs to return blanks in cell for segments that are not found between loops

    The following mocked up macro is working for the first piece of searchable criteria (returning a blank cell for the loops in which the string is not found), but when duplicated exactly for the remaining 25 pieces of criteria it needs to search for, it will not return a blank cell. Instead, it will grab the data found in the next loop and duplicate it for both. I cannot figure out why it would work for the first one, and not for the rest when it is written exactly the same way...any help would be so greatly appreciated:

    Sub Loops()

    S = 1
    P = 1

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    R = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

    Loop1:

    S = Range(Cells(S, 1), Cells(LR, 1)).Find("SR~*530~*", LookIn:=xlValues, Lookat:=xlPart).Row
    R(0) = Cells(S, 1).Value

    E = S

    Loop2:
    E = Range(Cells(E, 1), Cells(LR, 1)).Find("TE~*", LookIn:=xlValues, Lookat:=xlPart).Row
    If UCase(Left(Cells(E, 1), 2)) <> "TE" Then GoTo Loop2


    On Error Resume Next
    Set pos = Range(Cells(S, 1), Cells(E, 1)).Find("GRO~*", LookIn:=xlValues, Lookat:=xlPart)

    If IsError(InStr(pos, "GRO~*")) Then

    R(1) = ""

    Else
    Q = Split(pos, "*")

    '***********************
    R(1) = Q(8)
    '***********************

    End If

    On Error Resume Next
    NextOne = Range(Cells(S, 1), Cells(LR, 1)).Find("…

    **Repeats for all 26 ranges**

    …End If

    Range(Cells(P, 4), Cells(P, 31)).Value = R

    P = P + 1
    S = Range(Cells(S, 1), Cells(LR, 1)).Find("TE~*", LookIn:=xlValues, Lookat:=xlPart).Row
    If S < LR Then GoTo Loop1

    End Sub

  2. #2
    Registered User
    Join Date
    08-09-2017
    Location
    US
    MS-Off Ver
    16
    Posts
    18

    Re: Loop Macro needs to return blanks in cell for segments that are not found between loop

    I figured this out. I had to loop through my 2nd loop. Closing.

+ 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. [SOLVED] Loop to highlight cell if value found
    By Fawkes_ in forum Excel General
    Replies: 1
    Last Post: 02-01-2017, 02:55 AM
  2. Loops with saved variables for each loop to be compared after loop is finished?
    By flabb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2016, 07:48 AM
  3. Use VBA loops to search for a list of values, delete columns they are found in
    By jfgay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2013, 07:58 AM
  4. Macro works and loops perfectly in VBA but does not loop on button click
    By evan.sams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2012, 08:25 PM
  5. [SOLVED] Macro to loop in folder and then open the workbook with found value
    By Jey Robbins in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2012, 12:10 PM
  6. Loops to find blanks then loop to find populated
    By Bevy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2006, 03:55 PM
  7. Macro to loop thru sheets & return to a1 cell
    By CassieM in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-21-2005, 12:06 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