Loop to find string, copy down until it sees the next (diff) string from list

Royzer · Jul 17th 2013

I could really use some help with this. I have revenue reports for around 90 stations and each report will have a variable number of accounts (rows). I need a loop that will identify the station's call letters and copy them down until it sees the call letters of the next station on the list, then switch to filling with that new stations's name. I don't have to use a station list for reference, but it's the only way I can figure out for the loop to know when to change the fill string to the new station. I've attached a workbook that shows what I'm trying to do. Thanks! forum.ozgrid.com/index.php?attachment/55062/

nilem · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Hi Royzer,
try it

Code

Sub ertert()
Dim x, i&, s$
x = Range("C10", Cells(Rows.Count, 3).End(xlUp)).Value: s = x(1, 1)
For i = 1 To UBound(x)
    If InStr(x(i, 1), "Local") + InStr(x(i, 1), "National") + InStr(x(i, 1), "Regional") Then
        x(i, 1) = s
    Else
        s = x(i, 1): x(i, 1) = s
    End If
Next i
Range("B10").Resize(i - 1).Value = x
End Sub

Display More

jindon · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

No loop

Code

Sub test()
    With Range("c10", Range("c" & Rows.Count).End(xlUp)).Offset(, -1)
        .Formula = "=if(or(c10={""EIBW"",""GAKE"",""KGBD"",""KHDS""}),c10,b9)"
        .Value = .Value
    End With
End Sub

Philosophaie · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

If you are using Excel 2007 or above you need to add to the "Quick AccessToolbar". Right-Click the down arrow on that toolbar and select "MoreCommands". From the left ComboBox select "All Commands". Youwill need: Command Button (ActiveX Control) and all the rest of "ActiveXControls", also needed are View Code, View Macro, Record Macro, DesignMode, and whatever else you think you may need. Group the "ActiveX Controls" and the rest with Separators to neaten things up.

Code

[FONT=Times New Roman][size=12][COLOR=#000000]Sub [/COLOR][/SIZE][/FONT]Fill()
         For k = 10 To Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
                   'copies column C to B
                   Sheets("Sheet1").Cells(k, "B") = Sheets("Sheet1").Cells(k, "C")
                   'copies column C to column B in Sheet2
                   Sheets("Sheet2").Cells(k, "B") = Sheets("Sheet1").Cells(k, "C")
                   'copies column D to Column C in Sheet2
                   Sheets("Sheet2").Cells(k, "C") = Sheets("Sheet1").Cells(k, "E")
         Next k
End Sub

Royzer · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Nilem, I don't know if anyone has told you lately, but you are AWESOME. That's exactly what I needed. Can't thank you enough!

Quote from nilem;674944

Hi Royzer,
try it

Code

Sub ertert()
Dim x, i&, s$
x = Range("C10", Cells(Rows.Count, 3).End(xlUp)).Value: s = x(1, 1)
For i = 1 To UBound(x)
    If InStr(x(i, 1), "Local") + InStr(x(i, 1), "National") + InStr(x(i, 1), "Regional") Then
        x(i, 1) = s
    Else
        s = x(i, 1): x(i, 1) = s
    End If
Next i
Range("B10").Resize(i - 1).Value = x
End Sub

Display More

Royzer · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Jindon and Philosophie: Thanks to both of you for your help, as well. I appreciate you taking the time to help me.:smile:

holycow · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Impressive formula jindonsan, I'll save that one away for future use.

Royzer · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Is there some way I can mark this thread "SOLVED"?

Quote from Royzer;674938

I could really use some help with this. I have revenue reports for around 90 stations and each report will have a variable number of accounts (rows). I need a loop that will identify the station's call letters and copy them down until it sees the call letters of the next station on the list, then switch to filling with that new stations's name. I don't have to use a station list for reference, but it's the only way I can figure out for the loop to know when to change the fill string to the new station. I've attached a workbook that shows what I'm trying to do. Thanks! forum.ozgrid.com/index.php?attachment/55062/

**Ger Plante** · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

No, we dont use a "solved" mechanism on this forum.

Thanks
Ger

jindon · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

It is not allowed to mark the thread like "Solved" here.

Just leave it as it is.

snb · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

An alternative:

Code

Sub M_snb()
    [B10:B200] = [if(C10:C200="","",if(row(C10:C200)<match(G11,$c$1:$c$200,0),G10,if(row(C10:C200)<match(G12,$c$1:$c$200,0),G11,if(row(C10:C200)<match(G13,$c$1:$c$200,0),G12,G13))))]
End Sub

nilem · Jul 17th 2013

Re: Loop to find string, copy down until it sees the next (diff) string from list

Hi SNB, good to see you. You do not cheating on your habit of writing code in a single line
Coolest code

Loop to find string, copy down until it sees the next (diff) string from list

Participate now!

Share

Users Viewing This Thread