omg, this worked PERFECTLY. thank you so much, I spent 2 weeks trying to figure this out on my own. Much appreciated!
Posts by crock
-
-
Hello! I'm trying to create VBA code to essentially do:
1. "Find" search for the word "Offer ID"
2. Select that row and then "SHIFT+DOWNARROW" to select all rows from "Offer ID" until the next blank cell
3. "Custom Sort" based on the values in Column N
4. Loop back to step 1 and find the next occurrence of the "Offer ID"
I tried to piece together some VBA code from a few different projects and from manually doing it using the "Record Macro" button to create the code, but I keep hitting road blocks. I'm looking at the "Range("A" & c & ":T")" portion and I'm sure that's part of the problem. In theory, I was hoping that code translated to grab A:T for the row of the current occurrence of "Offer ID" (ie: if the macro has found "Offer ID" on Row 20, it would be grabbing A20:T20. Then End(xlDown).Select would select all cells below until the next blank row. However, I think there might be a slew of problems with the sorting logic. I set Key:=Columns(14) to represent sorting based on the values in Column N, which is the 14th column over.
Code
Display MoreSub AlphaSORT() ' ' ' Dim LastRow As Integer Dim Offer ID As String With Worksheets("Backup").Range("A1:A15000") Set c = .Find(What:="Offer ID", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Range("A" & c & ":T").End(xlDown).Select ActiveWorkbook.Worksheets("Backup").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Backup").Sort.SortFields.Add2 Key:=Columns(14), _ Order:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, DataOption:= _ xlSortNormal, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Backup").Sort .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply Set c = .FindNext(c) If c Is Nothing Then GoTo DoneFinding End If Loop While c.Address <> firstAddress End If DoneFinding: End With End Sub