Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 31st, 2004, 01:28 PM
Registered User
 
Join Date: Dec 2004
Location: , , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sebdanger
Default HELP! PLEASE referencing named cells

Gooday everyone!!

on named cells is there any way to make them relative referencing
instead of absolute, becasue otherwise my sorting system does not work
becasue it is getting values from another sheet through named cells and
it sorts it all wrong.


Cheers in advance for any one clever enough who can help me:)
Reply With Quote
  #2 (permalink)  
Old January 3rd, 2005, 08:34 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good question!

You would need to use cell-named-range.offset method to cover the target sort range.

I would need to have your worksheet to show you how to achieve this.

Thanks

Reply With Quote
  #3 (permalink)  
Old January 3rd, 2005, 02:36 PM
Registered User
 
Join Date: Dec 2004
Location: , , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sebdanger
Default

I can send you my worksheet if you give me your email, it's school course work that I am doing, if you could help me I would be very grateful
Reply With Quote
  #4 (permalink)  
Old January 3rd, 2005, 03:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Don't know if this may help you, but if you name a cell like this "MyCellName" it will be global and may be referenced from another sheet.

If instead you name it "Sheet1!MyCellName" it will only be referenced from Sheet1, unless you use the Sheet1!MyCellName from another worksheet.
Reply With Quote
  #5 (permalink)  
Old January 4th, 2005, 07:59 AM
Registered User
 
Join Date: Dec 2004
Location: , , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sebdanger
Default

Unfortunately that didn't seem to help it still sorts wrong because I am coping some data from one other sheet and also adding some new data in, and when I sort it because my worksheet Is split up by different columns and there are 100 rows of data, it sorts the first columns but does not sort the last ones as the first ones are info from a different sheet and the last ones are new info I add in, the naming cells links that cells value directly to which row it is on which is not helpful.


Cheers for trying to help anyway.
If there’s any one else who could help it would be much appreciated, cheers folks!!!
Reply With Quote
  #6 (permalink)  
Old January 11th, 2005, 06:08 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Pls send the file (with a refreshing note as to what to be done) on the following address:
amjad.s.mahmood@gsk.com

Thanks & regards
Amjad Mahmood

Reply With Quote
  #7 (permalink)  
Old January 13th, 2005, 12:40 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Seb, I have fixed your macro problem.
I made changes in your module2 macro call (named as : Sorting)
I have checked it it's now correctly working.

Please check at your end, and let me know.

By the way I liked your work in the file which you have sent, you are wonderful in Excel.


Sub sorting(range1 As String)
    there1 = True
    Application.ScreenUpdating = False
    Dim SrtRange As String
    SrtRange = "A5:I" & Range("a4").End(xlDown).Row ' make sure that A4 & A5 must not be empty
    Range("a5").Select
    For a = 1 To 10
        With Selection.Interior
            .ColorIndex = 37
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
        ActiveCell.Offset(0, 1).Select
        seb = ActiveCell.Column
    Next
    Range(ref).Select
    With Selection.Interior
        .ColorIndex = 34
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    If ascending = True Then
        Range(SrtRange).Sort Key1:=Range(ref), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Else
        Range(SrtRange).Sort Key1:=Range(ref), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
    oldref = ref
    oldrange = range1
    there1 = False
End Sub


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
named anchors stekker XSLT 1 November 16th, 2006 05:03 AM
Referencing Workbook Named Ranges in VBA RollingWoodFarm Excel VBA 2 October 4th, 2006 01:54 PM
Named arguments Bob Bedell C# 2 March 19th, 2006 09:25 PM
Named Pipes bpe_xmind Visual C++ 0 February 5th, 2006 08:21 AM
Help for named template wu4 XSLT 2 October 18th, 2005 10:09 AM



All times are GMT -4. The time now is 01:05 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.