VB Range referencing
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VB Range referencing

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then ...

    Is there a way to reference the range in some other way, like via its Excel name or by the value in the header row? It's a real pain to fix these macros everytime I move a column.

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 14:24, Skebo wrote:
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then ...

    Is there a way to reference the range in some other way, like via its Excel name or by the value in the header row? It's a real pain to fix these macros everytime I move a column.
    Sure,
    If Target.Value = "Skebo" Then ...

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I had already tried that. It returns a 'Run-time error: '13' type mismatch on the line If Target.Column = "Skebo" Then

    Skebo is in both the name box and row1

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 14:34, Skebo wrote:
    I had already tried that. It returns a 'Run-time error: '13' type mismatch on the line If Target.Column = "Skebo" Then

    Skebo is in both the name box and row1
    You've got the wrong argument there. You need
    If Target.Value = "Skebo" Then..




    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just slap me next time.

    Thanks that did it

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Target.Value refers the value in the CELL, not a range name. If you want to refer to a named range, you can do something like this:

    Code:
    If Not Intersect(Target, Range("skebo")) Is Nothing Then
        ' Target (at least part of it) is in your skebo column
    End If
    Hope this helps,

    Russell

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops, maybe I thanked you too soon

    I have:
    If Target.Value = "Skebo" Then ...

    Instead of:
    If Target.Column = 1 Then ...

    I have:
    Skebo in the name box and row1 for column 1(or A)

    The If Then evaluates as false unless I put Skebo in column A. Target.Value = 'whatever is placed in column A' which does not = "Skebo"

    My old code executed the Then code everytime I changed a value in column A.

  8. #8
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 14:59, Skebo wrote:
    Oops, maybe I thanked you too soon [img]/board/images/smiles/icon_wink.gif[/img]

    I have:
    If Target.Value = "Skebo" Then ...

    Instead of:
    If Target.Column = 1 Then ...

    I have:
    Skebo in the name box and row1 for column 1(or A)

    The If Then evaluates as false unless I put Skebo in column A. Target.Value = 'whatever is placed in column A' which does not = "Skebo"

    My old code executed the Then code everytime I changed a value in column A.
    Alright, let's try

    If Target.Column = Range("Skebo").Column Then...

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ummmm. That may have been bad crashed Excel.

  10. #10
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    If Target.Column = Range("Skebo").Column Then

    Seems to work fine. Thanks for your replies Barrie and Russell.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com