Find nonempty cell above in column and increase number

[davegscott]

Hi,

I have a macro that currently states that if there is a value in column C and a value in column S, then enter a value in column D. The value in column C is just an increasing identifier that populates based on another macro. Column S has generic data keyed by a user. What I want to happen is for the value in column D to populate as 1 and only increase when the value in column S changes. For example:

Column C has an identifier in rows 8-12, and 16-20. If column S has "SHIRT" for rows 8-18 and "PANT" for rows 19-20. Column D should be "1" for rows 8-12, 16-18, and "2" for rows 19-20.

I have the macro almost there but I don't know how to account for the varying gaps in data. Basically, column D only needs to populate if Column C has a value, but it needs to reference column S to determine if the counter increases by 1 or not. Any help is greatly appreciated!!

Sub GroupSortOrder()
'
' GroupSortOrder Macro
'

'
Dim Cell As Range
    Dim Counter As Double
    Dim RegExp As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet
    
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.IgnoreCase = True
        
        RegExp.Pattern = "^(CWA|)\s"
        
        For Each Wks In Worksheets
        
            If RegExp.Test(Wks.Name) = True Then
        
                Set Rng = Wks.Range("C8:S8")
                Set RngEnd = Wks.Cells(Rows.Count, Rng.Offset(0, 16).Column).End(xlUp)
            
                If RngEnd.Row >= Rng.Row Then
            
                    Set Rng = Rng.Resize(RowSize:=RngEnd.Row - Rng.Row + 1)
                
                    For r = 1 To Rng.Rows.Count
                    If Rng.Item(r, 1) <> "" Then
                        If Rng.Item(r, 17) <> "" Then
                            
                                With Worksheets("DoNotEdit")
                                    Counter = .Cells(1, "D")
                                    Rng.Item(r, 2).Value = .Cells(1, "D")
                                    .Cells(1, "D").Value = Counter + 1
                                End With
                            End If
                    End If
                        
                    Next r
                
                End If
                With Worksheets("DoNotEdit")
                .Cells(1, "D").Value = 1
                End With
            End If
            
        Next Wks
        
        
End Sub

[davegscott]

To further clarify, if rows 13-15 had a third value, e.g. "HAT", I would still need the same result above. I had deleted some code prior to posting this that said in the With...End With section that if the value in column S for the next row is the same, stay the same but if it changes, increase by 1. This doesn't resolve the isssue though

[Oredigger90]

A relatively simple solution would be to have an Exit condition. I believe using

 If IsEmpty(Cells(dummy,dummy).Value) Then
   Exit For
   End If

or add another if/else that encompasses most of the other nested if/else loops that says If IsEmpty(Cells(dummy, dummy).Value Then "whateveryourcountervariableis" = "variable" +1

And put that inside of your For Loop. A quick Google search of How to Exit a For Loop will help you with the proper syntax for your exact code, as it is hard to debug without physically seeing the initial and final values desired.

Let me know if that helps!

[davegscott]

Hi,

So I tried to incorporate an Exit condition as you mentioned, but the way I wrote it, it just stopped putting a value in column D once it found a blank value in Column C. It didn't keep going with the next row that has a value in column C. I'm kind of a novice, sorry. I am attaching my workbook for more clarification. I'm working with the GroupSortOrder macro. In my spreadsheet, I have it to the point where cells populate when they should, and are blank when they should be, but in column D, the number starts to increase before I want it to. In this example, when it says Baroque in column S it should be 1, then anything with Iconic Quilting should have a 2, etc, etc. But these values are not static and so I can't just define it to put a 1 for a specific value and so on. So for example in cell D174, when the macro gets here, I want it to look in column S and if that value matches the last column S with a corresponding value in Column D, in this case, S172, then it should populate with a 2. Otherwise, my counter should tick up one to a 3, For example when D170 looks at D145 but their correspong values in column S don't match it moves up to 2. Is this clear at all? Thanks for the help! I really appreciate it.

[Oredigger90]

Yes I think I get what you're aiming for, I've done a similar program a week ago. Unfortunately its on my work computer, so I'll check it out in the morning and try to see if I can lend a hand.

[davegscott]

Any luck? Thanks for your help

[davegscott]

Thanks so much! Let me know if there is any other info I can provide to help me find a solution.

[Oredigger90]

Sorry got side tracked. I'll send you my file of what I did that's kind of similar so you can look over it while I try to think of how yours should be accomplished. If you could make like a 20-30 row spreadsheet with example starting data and finishing data so I can better visualize how to get it done that'd be great.

Sub slam()

Dim SR As Long, k As Long, q As Long, w As Long, x As Long

Sheets("Matching").Select

SR = Range("A" & Rows.Count).End(xlUp).Row

q = 1
w = 1
x = 1
k = 1


Dim n As Long, p As Long


n = 1
p = 1

Dim y As Long, m As Long
   
    m = 1
    
Dim this As String, val As String, ure As Long
    
this = Range("P" & q).Value
val = Range("P" & (q + 1)).Value

For k = 1 To SR

Sheets("Matching").Select



w = (k - 1)
x = (k + 1)

x = Range("K" & x).Value

q = Range("K" & k).Value
Range("A" & k).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("HAHA").Select
    

    
    
    Range("A" & n).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



If w = 0 Then
Sheets("Matching").Select



Range("P" & k).Select
Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("HAHA").Select
    Range("P" & n).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
p = n

        End If
        

        
If w <> 0 Then

Sheets("Matching").Select
Dim Z As Long, l As Long

Z = Range("K" & w).Value

If q <> Z Then





    Range("P" & q).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("HAHA").Select
        Range("P" & n).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  p = n

Cells(p, 1).EntireRow.Insert shift:=xlUp
n = n + 1
p = p + 1
Sheets("Matching").Select


    End If
    
   ure = n
   
If x > (q + 1) Then


this = Range("P" & q).Value
val = Range("P" & (q + 1)).Value


If val = this Then

   
y = x - (q + 1)
    
For y = 1 To y


l = q + y
m = p - 1 + y
Sheets("Matching").Select
    Range("P" & l).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("HAHA").Select
        Range("P" & m).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        


  If (Range("P" & m).Value <> Range("P" & (m + 1))) Then
   Exit For
   End If

        
Application.Wait Now + TimeValue("00:00:01")
        
        Next y
        
End If

        
        
End If



End If



Application.Wait Now + TimeValue("00:00:01")

n = n + 1

If m >= p Then

p = m + 1

End If

If p > n Then

n = p

Cells(p, 1).EntireRow.Insert shift:=xlDown

n = n + 1
p = p + 1
End If



Sheets("Matching").Select

Next k



End Sub

[davegscott]

Here is a Before and After sample of what I want it to look like. I'll take a look at what you were able to provide when I get a chance. Thanks!

[Oredigger90]

So if it HAS to be 1,2,3,4,5,6,7,etc, then this code won't work. If you are fine from skipping from 2-4, or say 5-7, then this code will work. I can play around with it to get it to be incremental, but it's all on what you need. Here is my code for your sortordersample

Sub groupsort()
Dim i As Long, j As Long, k As Long, val As String
j = ActiveSheet.UsedRange.Rows.Count
k = 1
val = Range("S" & 8).Value


For i = 8 To j
    
    If Range("S" & i).Value = "BAROQUE" Then
        k = 1
        Else
            If Range("S" & i).Value = "SHIRT" Then
                k = 2
                Else
                    If Range("S" & i).Value = "HAT" Then
                        k = 3
                        Else
                            If Range("S" & i).Value = "PANT" Then
                                k = 4
                            End If
                    End If
            End If
    End If
            
    If IsEmpty(Range("C" & i).Value) Then
        Else
        Range("D" & i).Value = k
        
    End If
        
   
    
    
    
Next i



End Sub

[davegscott]

I need it to be 1,2,3,4,5,6,7 etc, unfortunately. Additionally, the actual file is pretty big and I want to avoid defining an else statement for each value as there are a ton, and they will change in a few months. I put HAT in there to make it more complicated because in reality my file is complicated, but, like with Hat, when something is in the middle of another group like the Shirt group, there is not a value in column C and therefore it can be skipped. Basically I was hoping there would be a way to say that the first row is 1, and if the next row that has a value in column C has the same value in column S as row 1, then it also gets a 1, otherwise increase my counter by 1 to 2. Then the next row, (if different) would be given that 2 and it would look at the next row with a value in C, etc etc. The values 1,2,3,4,5 will be in order in the end, its just a matter of determining when the row gets a number and whether the counter needs to be increased.

[Oredigger90]

Thank me now, this code was pretty challenging to write, but I'm proud of it.

Sub groupsort()


Dim i As Long, j As Long, k As Long, l As Long
j = Range("S" & Rows.Count).End(xlUp).Row
k = 0



For i = 8 To j

b:

If IsEmpty(Range("C" & i).Value) Then GoTo a:

    For l = 8 To i
    
d:
    
        If IsEmpty(Range("D" & l).Value) Then GoTo c:
            If Range("S" & l).Value = Range("S" & i).Value Then
            k = Range("D" & l).Value
            Range("D" & i).Value = k
         End If
    
c:
       
        
    Next l
   
If IsEmpty(Range("D" & i)) Then
    k = k + 1
End If

Range("D" & i).Value = k


a:



Next i
    
   
End Sub

So a couple of minor issues with it. Make sure you start in row 8. If your computer crashes, there is an easy fix for this, but your program will run much slower. I have a crappy computer so if it crashes with a lot of data just let me know and I'll modify it with making it wait 1 second every iteration, it will go much slower but won't crash your comp. So basically, if you have any questions on using it send me a private message or reply to this post again. But this works perfectly with your example data.

Also let me know if it works, and if so click on my star in the bottom left corner of my posts, and mark this thread as [SOLVED]

[Oredigger90]

Ok that's what I was afraid of lol. I think I will be able to do that, but it'll take me a little time to think the logic over in my head. I'll give it another crack though. I'll keep you posted.

[davegscott]

Sorry! You are the best! haha thanks so much

[davegscott]

Ok, it is so close to getting what I want and I am sorry its probably more my fault that its not working perfectly, THANKS so much its almost there. I have attached my results and if you filter, you can see its not a 1 number per value in S ratio. I think I made a mistake in telling you that the groups I want are in order, however, and that there are some in there that are out of order. Sadly I can't control this because I am not the sole owner of the file and was misinformed.

Basically, each value in column S rightfully is only associated with one number. However, the macro also (rightfully) gives e.g. the number 6 to the value MULTI BICOLOR when it pops up again in row 705 even though the last value was 10, which is great, I do want it to use an old number if its the same S value, but then it doesn't jump back to the next number of 11, it only goes to 7, so then there are multiple values in S associated with the number 7. This then happens again a few more times. Think you can fix it?

[Oredigger90]

No its fine, this is how programming works lol. A lot of trial and error. I won't have time today to get to it, but tomorrow I'll have plenty of time to work on it. I'm essentially trying to beef up my programming skills as I don't have anything to do at work until I leave for my Trip on Sunday. it should be an easy fix, I'll just create a new variable "p" that records the highest k value recorded, and then the "new" k will be k = p+1. If you understand what I'm saying you might be able to make a little If/Then around that portion by yourself, but regardless I'll finish it up tomorrow. But I get what you are saying on how they'll get out of numerical increasing order and such.

[davegscott]

Not a problem, that makes sense to me. Thanks so much for your help. If you are able to try to get to it in the morning, I am leaving for a wedding and will only be around til about 1230, but if not dont worry about it, the afternoon is fine. My vba skills are pretty basic and completely self taught so I appreciate what you are doing, its helping me learn a ton. I am also going to try to work on this myself this afternoon if I can to help my knowledge, but I've just been in a meeting all day. Thanks!!

[davegscott]

1 additional thing to note:

I'm going to be calling this macro from a BeforeSave event, and I want it to resort even if there is a value in column D. I think you might have it set to move to the next row if there is already a value in column D but I don't want to do that. e.g.

Baroque is 1
Iconic Quilting is 2

the user inserts a new row between these two with a new value: BICOLOR

Save file
Baroque is 1
Bicolor is 2
Iconic Quilting is 3

Hope that makes sense

[Oredigger90]

This should be the final version I believe. The MULTIBICOLOR counting issue is resolved. The Only Issue will be, all cells in column D must be blank before the sorter runs, hopefully that isn't an issue for you. I could just have the program clear Range("D8:D") before it runs the complete program. To write a different program without forcing column D to be blank would be a lot larger and time consuming to run than just clearing out column D before it is ran/saved. Here it is.

Sub groupsort()


Dim i As Long, j As Long, k As Long, l As Long, p As Long

j = Range("S" & Rows.Count).End(xlUp).Row
k = 0


For i = 8 To j

b:

If IsEmpty(Range("C" & i).Value) Then GoTo a:

    For l = 8 To i
    
d:
    
        If IsEmpty(Range("D" & l).Value) Then GoTo c:
            If Range("S" & l).Value = Range("S" & i).Value Then
            p = Range("D" & l).Value
            Range("D" & i).Value = p
        End If
    
c:
       
        
    Next l
   
If k > p Then GoTo a:

   
   
If IsEmpty(Range("D" & i)) Then
    k = k + 1
End If

Range("D" & i).Value = k


a:

p = k

Next i
    
   
End Sub

Edit: Also, It won't skip a row unless the value in column C is empty; if column D is empty, it just kicks it to another part of the program to increase the counter if there are no known matches for Column S so far.

[davegscott]

Works like a charm!! I was able to add in the clear column contents myself, which I did need it to do. Last little doozy of a request. In the attached I attempted to replicate the macro you created for other columns with slight variations. I can't figure out how to tweak it properly though.
Macro stylesort will work in a similar fashion but I need it to restart numbering for each group. So for group 1, the style sort order would be 1,2,3,4,5,etc for the different style numbers but for group 2, it should restart counting at 1, not continue up.
Macro colorsort works the same way as stylesort but would restart with every new style number. I think color is easiest because I dont really care whats in the color column, i just want every row for each style to increment up 1, because for each style, the colors should be unique.

Do you think this is feasible?? THANKS!!! I tried to get the macros started in the file.

[Oredigger90]

Yeah that is definitely feasible, I won't have a chance to work on it until Monday or so, as I am flying to Kentucky for a business trip on Sunday-Saturday. But I should have plenty of off time to get this done, it shouldn't take me more than an hour to get both of them.

For clarification,
- stylesort evaluates the data in column U, and if that changes while it remains the same value in column S, then increase that number by one. Then when there is a new value in column S, restart the counter back at 1
- colorsort evaluates the data in column W, and if that changes while it remains the same value in column U, then increase that number by one. Then when there is a new value in column U, restart the counter back at 1

Are those accurate assessments of what you want it to do? The colorsort is the most confusing to me of what you actually desire as an end result after the program is run. Also, are these going to be run after the data is filtered from column D in order, or will it just be part of the same program. Thanks

Edit: Also, why are some of the values bold in column W? Any reason for that?

[davegscott]

I may have over simplified that but I was going from memory. I'm going to look at the file first thing in the morning to confirm what I'm asking.

[davegscott]

Hi, great questions. Sorry for the late response. You have it pretty accurate. The macros will run in the following order:

1 a macro I created that populates column C
2 group sort
3 style sort
4 color sort

Because of the order, I had originally suggested that style sort order populates/changes numbers based on column D and color sort populates/changes numbers based on column E because the column will have populated prior but if it's easier to base it on the columns you mentioned then that's fine.

Not sure about the bold it's probably nothing but I can double check tomorrow.

Thanks!!

[davegscott]

The macros will run in the following order:

1 a macro I created that populates column C
2 group sort
3 style sort
4 color sort

Because of the order, I had originally suggested that style sort order populates/changes numbers based on column D and color sort populates/changes numbers based on column E because the column will have populated prior but if it's easier to base it on the columns you mentioned then that's fine.

When I stated the above, I think I was trying to oversimplify it. Basically, similar to the first macro we worked on, I only want it to populate for rows where column C is populated. Because they run in the order specified above, I wasnt sure if it was easier to have them all look to column C or just look at the column next to it. So stylesort could look to column D and Colorsort to column E, but if thats overcomplicating it, then they can all look at C.

For style sort, it will work very much the same as group sort, but with the new columns you mentioned. If its the same value in column U then it gets the same number and if its a new value, it gets a new number, but every time the group sort number increases, the count should restart to 1.

Color Sort order is similar but with a twist. I am not concerned with the actual value in the color column, e.g. everything with value "BLUE" should not all be the same number. The twist here is that, for each set of rows that have the same style number, i.e. same number populated in column E, or same value in column U, I want each row to have an increasing value in column F that restarts everytime column E/U changes. I have uploaded an example before/after to clarify. Its basically building a hierarchical numbering system in these 3 columns we are populating.

If you play around with the filters on the after tab in the attachment, you can get a feel for how it should work, I think. It may be a better explanation seeing it than trying to type it out. Let me know if you have questions.

[davegscott]

Have you had a chance to take a look? Thanks so much!

[davegscott]

Any chance you will be able to work on this today? Thanks so much for your help I really appreciate it.

[davegscott]

I was able to get it all to work. If you are interested in seeing how I updated the style/color sorts, here you go. Thanks for getting me most of the way. It was such a huge help.

Sub stylesort()


Dim i As Long, j As Long, k As Long, l As Long, p As Long, q As Long

           j = Range("U" & Rows.Count).End(xlUp).Row
            k = 0
            Range("E8:E" & j).ClearContents


            For i = 8 To j

b:

            If IsEmpty(Range("D" & i).Value) Then GoTo a:

                For l = 8 To i

d:
                If l = i Then GoTo c:
                 If IsEmpty(Range("E" & l).Value) Then GoTo c:
                        q = i - l
                        If Range("U" & l).Value = Range("U" & i).Value Then
                     p = Range("E" & l).Value
                     Range("E" & i).Value = p
                        End If
                        If Range("U" & l).Value <> Range("U" & i).Value Then
                        If Range("D" & l).Value = Range("D" & i).Value Then
                        p = Range("E" & l).Value
                        Range("E" & i).Value = p + 1
                        End If
                        End If
                        
                    
                    
c:


             Next l

            If k > p Then GoTo a:


            
            If IsEmpty(Range("E" & i)) Then
            If Range("D" & i).Value > Range("D" & (i - q)) Then
            k = 0
            End If
             k = k + 1
            Range("E" & i).Value = k
            End If
            

a:

            p = k

            Next i
    
   
End Sub
Sub colorsort()


Dim i As Long, j As Long, k As Long, l As Long, p As Long, q As Long

           j = Range("W" & Rows.Count).End(xlUp).Row
            k = 0
            Range("F8:F" & j).ClearContents


            For i = 8 To j

b:

            If IsEmpty(Range("E" & i).Value) Then GoTo a:

                For l = 8 To i

d:
                 If l = i Then GoTo c:
                 If IsEmpty(Range("F" & l).Value) Then GoTo c:
                     q = i - l
                        If Range("U" & l).Value = Range("U" & i).Value Then
                     p = Range("F" & l).Value
                     Range("F" & i).Value = p + 1
                 End If
                   
c:

             Next l


            If IsEmpty(Range("F" & i)) Then
            If Range("E" & i).Value <> Range("E" & (i - q)) Then
            k = 0
            End If
            If Range("E" & i).Value = Range("E" & (i - q)) Then
            If Range("D" & i).Value <> Range("D" & (i - q)) Then
            k = 0
            End If
            End If
             k = k + 1
            End If
            If IsEmpty(Range("F" & i)) Then
            Range("F" & i).Value = k
            End If
a:

            
            Next i
    
   
End Sub