VB Range referencing

Skebo · Mar 8th, 2002, 03:24 PM

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.

Barrie Davidson · Mar 8th, 2002, 03:26 PM

Quote:

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,

Skebo · Mar 8th, 2002, 03:34 PM

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

Barrie Davidson · Mar 8th, 2002, 03:43 PM

Quote:

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..

Skebo · Mar 8th, 2002, 03:48 PM

Just slap me next time.

Thanks that did it

Russell Hauf · Mar 8th, 2002, 03:52 PM

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

Skebo · Mar 8th, 2002, 03:59 PM

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.

Barrie Davidson · Mar 8th, 2002, 04:05 PM

Quote:

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,

Skebo · Mar 8th, 2002, 04:20 PM

Ummmm. That may have been bad

crashed Excel.

Skebo · Mar 8th, 2002, 04:41 PM

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

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

Mar 8th, 2002, 03:52 PM	#6
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 03:24 PM	#1
Skebo New Member Join Date: Feb 2002 Posts: 20	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.

Mar 8th, 2002, 03:34 PM	#3
Skebo New Member Join Date: Feb 2002 Posts: 20	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

Mar 8th, 2002, 03:48 PM	#5
Skebo New Member Join Date: Feb 2002 Posts: 20	Just slap me next time. Thanks that did it

Mar 8th, 2002, 03:59 PM	#7
Skebo New Member Join Date: Feb 2002 Posts: 20	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.

Mar 8th, 2002, 04:20 PM	#9
Skebo New Member Join Date: Feb 2002 Posts: 20	Ummmm. That may have been bad crashed Excel.

Mar 8th, 2002, 04:41 PM	#10
Skebo New Member Join Date: Feb 2002 Posts: 20	If Target.Column = Range("Skebo").Column Then Seems to work fine. Thanks for your replies Barrie and Russell.