VBA Excel SORT using CELLS(r,c) for key reference

Posted on 2011-05-06
Last Modified: 2012-05-11
I have an Excel macro that uses Cells(r,c) references extensively, because the layout of the spreadsheet is highly variable. At some point, I need to sort the data, and the columns to be used as sort keys are determined when the macro runs.

The following syntax (from a recorded macro) works fine: Key1:=Range("A2").
With this syntax, the sort appears to do nothing: Key1:=Range(Cells(2,1)).
For most other purposes, Range("A2") and Range(Cells(2,1)) are interchangeable.  But apparently not for SORT.
Ultimately, I need the sort key to be dynamic: Key1:=Range(Cells(2, iDocNum)), where iDocNum is a column number.
What am I missing?

Thanks, Dave
Question by:SkyGuyDave
    1 Comment
    LVL 24

    Accepted Solution

    Range(Cells(2,1)) is not valid syntax, but Cells(2,1) is.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now