Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Runtime Error  and macro setting "use relative references"

Posted on 2012-08-22
8
Medium Priority
?
261 Views
Last Modified: 2012-09-12
Hi. I was recording a macro and using the "use relative references" setting. I tried doing a sort with this setting but get a runtime error which highlights this code
 .SetRange ActiveCell.Offset(-1, 0).Range("A1:G3846")

any ideas about this?
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Add Key:= _
        ActiveCell.Offset(0, 1).Range("A1:A3845"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("perStockTweets").Sort
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:G3846")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window

0
Comment
Question by:willie108
  • 4
  • 4
8 Comments
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 2000 total points
ID: 38323425
I think it's because of the reference to cell A1, which is row 1 and column 1 and have no columns or rows to the left or above it (that's what -1 means).

Can you post sample data?
0
 

Author Comment

by:willie108
ID: 38323441
Hi. If I record it without relative references and run it I don't get the error. Here it is
Sub Macro7()
'
' Macro7 Macro
'

'
    Sheets("perStockTweets").Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Add Key:=Range( _
        "B2:B3846"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("perStockTweets").Sort
        .SetRange Range("A1:G3846")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

0
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 2000 total points
ID: 38323579
I'm not really sure specifically why it wouldn't work in this example but there are known issues with recording macros.

What are you trying to accomplish? I might be able to guide you to code.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Accepted Solution

by:
willie108 earned 0 total points
ID: 38323832
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("perStockTweets").Sort.SortFields.Add Key:=Range( _
        "B2:B3926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("perStockTweets").Sort
        .SetRange Range("A1:G3926")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

Open in new window

Thanks. I am trying to get a pivot table macro like the one attached but I want it to be flexible. Right now it will only work with with rows down to row 3926 because that is the way I recorded it but I want it to work with any size row and column that appears in the sheet.
0
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 2000 total points
ID: 38324458
Based on the code you've recorded it looks like you're just sorting a range?

Below is a basic code for Pivot Tables. It is best to use a "dynamic named range" for Pivot Tables. Go to Data > Name Manager and put this in the field.
=OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))

Open in new window


Your table should automatically adjust rows and columns as they are added.

Sub CreatePivotTable()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PTRange As Range
    Dim wb As Workbook
    Dim ws As Worksheet
    
    
    Set wb = ThisWorkbook
    Set ws = Worksheets("Sheet1") 'Assuming your sheet name is Sheet1
    Set PTRange = ws.Range("A1").CurrentRegion
    
    'Create the cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=ws.Range("myRange"))

    'Add a new sheet for the pivot table
    Worksheets.Add

    'Create the pivot table
    Set PT = ActiveSheet.PivotTables.Add( _
    PivotCache:=PTCache, _
    TableDestination:=ActiveSheet.Range("A3")) 'Where you want the Pivot to be positioned on new sheet

    'Specify the fields
    With PT
    .PivotFields("X").Orientation = xlRowField 'Add a row field
    .PivotFields("Z").Orientation = xlColumnField 'Add a column field
    .PivotFields("Val1").Orientation = xlDataField 'Add a data field
    .PivotFields("Sum of X").Function = xlCount ' Change the calculation type
    
    'no field captions
    .DisplayFieldCaptions = False
    End With
    
End Sub

Open in new window

0
 

Author Comment

by:willie108
ID: 38324485
Sorry, I said pivot table in one of the messages above but I am trying to sort
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38324760
Try this workbook. Paste the data and corresponding headers then run the macro.
Sort.xlsm
0
 

Author Closing Comment

by:willie108
ID: 38390095
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

580 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