Incorporate range within a cell to range formula in Excel VBA

Question

In cells A1 and A2, I have a concatenate formula that produces X1:X2 and Y1:Y2 respectively, where in both the letter and numerical values are dependent on certain formulas in the worksheet.

In VBA, I wish to sort out the describe ranges in A1 and A2 separately. I tried the following formula, but it didn't work:

Sub Sort()
    Dim myrange1 As String
    Dim myrange2 As String
    myrange1 = A1
    myrange2 = a2
    Range(myrange1).Select
    ActiveWorkbook.Worksheets("Sample generator").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sample generator").Sort.SortFields.Add Key:=Range( _
        "X2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sample generator").Sort
        .SetRange Range(myrange1)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range(myrange2).Select
    ActiveWorkbook.Worksheets("Sample generator").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sample generator").Sort.SortFields.Add Key:=Range( _
        "X4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sample generator").Sort
        .SetRange Range(myrange2)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks!

Go on then...don't keep us in suspense. How did the code not work? — Darren Bartrup-Cook, Mar 26 '18 at 10:03
I'm thinking..... `variable not defined`? Wrap `A1` & `A2` in square brackets, but then you'll only get the value of A1 and not a reference to it. Try changing `myRange1` to a `Range` rather than a `String` and `Set` it as it's an object... — Darren Bartrup-Cook, Mar 26 '18 at 10:08
hi Darren, I actually went back with the code I'm playing with, and I somehow dim'd myrange1 and 2 as range, not string. I as well tried brackets and no brackets, but somehow won't make it run. I probably should bother as well what error message is being displayed, so thanks for the tip. Anyway, I had my answer with Japeed's sample code. Tried playing a bit with it, and it somehow worked. Thanks! — J.Mapz, Mar 27 '18 at 00:45

score 1 · Answer 1 · answered Mar 26 '18 at 10:23

The way you have written it:

Sub Sort()
    Dim myrange1 As String
    Dim myrange2 As String
    myrange1 = A1
    myrange2 = a2

A1 and a2 are variables. And below in your code Range(myrange1).Select, they are probably intended to be strings, as far as you are mentioning here "ranges in A1 and A2 separately".

Thus, try something like this:

myrange1 = "A1"
myrange2 = "a2"

Then Range(myrange1).Select would run quite well.

In general, read these two to make your code a bit better:

And then try to refactor the whole code.

Thanks so much for the help! The references were as well helpful in adding knowledge to my basic VBA understanding and in reducing some code typing. Not sure why, but I still was not able to make it run properly. I tried playing around a bit with Jeeped's provided answer, and was able to get it right after a few tries. I truly appreciate though your references. Thanks! — J.Mapz, Mar 27 '18 at 00:40

score 0 · Accepted Answer · 2018-03-26T10:55:52.227

0

In A1 you have a formula that creates the string "X1:X2"; in A2 you have a formula that creates the string "Y1:Y2".

You want to sort X1:X2 in an ascending manner then sort the range Y1:Y2 in an ascending manner.

Option Explicit

Sub mySort()
    Dim myrange1 As String, myrange2 As String

    With ActiveWorkbook.Worksheets("Sample generator")

        myrange1 = .Cells(1, "A").Value2
        myrange2 = .Cells(2, "A").Value2

        With .Range(myrange1)
            .Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlNo
        End With

        With .Range(myrange2)
            .Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlNo
        End With

    End With

End Sub

edited Mar 26 '18 at 10:55

answered Mar 26 '18 at 10:41

Why **.Value2** ? – Gary's Student Mar 26 '18 at 10:47
1

Why not? It's infinitesimally faster and the strings contain neither currency or date information that would be gained from .Value. – Mar 26 '18 at 10:49
Thanks so much! I was able to make it work after playing after a few tries with your provided code. Not sure why, but I as well tried .value, but it doesn't sort the second set. With .value2, this works, so I sticked with it instead. I also learned some used text, such as .cells and .value2, though I can't understand how the .Cells(1) work since it only enumerates a single digit inside cells. Thanks! – J.Mapz Mar 27 '18 at 00:43

Incorporate range within a cell to range formula in Excel VBA

2 Answers2