Sorting a column based on user input

Question

Please refer to the code below. I´m trying to sort the content of my sheet based on the cell input on the Range("AD2"). The problem sits on the line key1:=rng_sort which I believe is being called in a wrong way.

What I want to do is change the column that is sorted based on the condition that is chosen on the cell Range("AD2"). On that cell i´ve a data validation list as seen below:

If Range("AD2") = "Event"i want to sort the column I. If it equals "Vo" i want to sort the column J.

Any Idea what i might be doing wrong?

Thank you.

Sub sortEvent()

Sheets(1).Activate ' activate sheet(1)

Dim n As Integer
Dim j As Integer
Dim i As Integer
Dim h As Integer
Dim Lastrow As Integer
Dim ng_sort As Range
        
        Lastrow = Cells(Rows.Count, "I").End(xlUp).Row ' guarda o indice da ultima linha com conteudo da coluna I. Mesmo havendo vazios identifca a ultima linha
        Length = Range(Range("I10"), Range("I" & Lastrow)).Rows.Count ' dimensão da coluna O ate a ultima celula com conteudo começando na O6
        
For firstrow = 1 To Length ' loop na coluna O
            If Range("C2").Offset(firstrow, 0).Interior.Color = RGB(68, 114, 196) Then
                Exit For
            End If
Next firstrow

If Range("AD2") = "Event" Then
        rng_sort = Range("I1")
    Else
        rng_sort = Range("J1")
End If
            
n = 0
j = 1
        
        For i = 1 To Length ' loop na coluna I
            If Range("C2").Offset(firstrow + i, 0).Value <> "" Then
                n = n + 1
            Else
                Range(Range("C2").Offset(firstrow + j, 0), Range("C2").Offset(firstrow + j + n - 1, 0)).EntireRow.sort key1:=rng_sort, order1:=xlAscending, Header:=xlNo
                j = j + n + 1
                n = 0
            End If
        Next i
End Sub

Please edit your question to show a representative example of your data (with sensitive information removed), along with the type of contents that might be entered into AD2. And also an explanation of what you mean by working great except ... would be useful in helping us help you. Take a look at the Help pages for How to create a Minimal, Complete, and Verifiable example — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 12, 2020 at 10:22
Hi thank you for the advise. I changed the question to make it more clear. Thanks — user2292821
– user2292821, Commented Jul 12, 2020 at 10:29

Ron Rosenfeld · Accepted Answer · 2020-07-12 10:52:57Z

1

You do not have Option Explicit set at the beginning of your module, else you would have seen your problem.

Go to Tools/Options and set the Option to always require variable declaration. I don't know why MS doesn't make that the default.

You have

Dim ng_sort As Range

but in your code, you show

rng_sort = Range("I1")

Since rng_sort was never declared, it will be used as a variant and contain the contents of Range("I1"), not the range object.

Had you declared Option Explicit, this would have been flagged as a typo; and without the Set keyword, you would have received a runtime error 91, and been able to figure this out for yourself.

Try:

If Range("AD2") = "Event" Then
        set rng_sort = Range("I1")
    Else
        set rng_sort = Range("J1")
End If

I have not tested your code, as you did not provide any data example, but you definitely need to Set a range object.

edited Jul 12, 2020 at 10:52

answered Jul 12, 2020 at 10:46

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting a column based on user input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related