Using defined name as a reference
 

Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) = start
1, the rng(1, 48) = end 1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost all my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Using defined name as a reference
 

Patrick,

I think you mean

TextBox1.Text = Range("start 1").Text
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) = start
1, the rng(1, 48) = end 1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost all
my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Using defined name as a reference
 

I tried you idea and get the following error:

Method 'Range' of odject'_Global' failed

"Bob Phillips" wrote in message
...
Patrick,

I think you mean

TextBox1.Text = Range("start 1").Text
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) =
start
1, the rng(1, 48) = end 1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost all
my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Using defined name as a reference
 

I tried your idea and got the following error:

Method 'Range' of object'_Global' failed


"Bob Phillips" wrote in message
...
Patrick,

I think you mean

TextBox1.Text = Range("start 1").Text
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) =
start
1, the rng(1, 48) = end 1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost all
my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Using defined name as a reference
 

Patrick,

A couple of things

I should have used a sheet qualifier
and the names cannot include spaces

So we get

TextBox1.Text = ActiveSheet.Range("start1").Text


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
I tried you idea and get the following error:

Method 'Range' of odject'_Global' failed

"Bob Phillips" wrote in message
...
Patrick,

I think you mean

TextBox1.Text = Range("start 1").Text
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Simonds" wrote in message
...
Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) =
start
1, the rng(1, 48) = end 1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost
all
my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Using defined name as a reference
 

This does not seem to work. Part of the problem might be that I left out a
piece of information. Below is my original post, but what I failed to
mention is that start_1 is defined as a range of AV3 through AV36. Not
sure if that is important or not. But of course if I am on row 12 I want the
data in TextBox1 (TextBox1.Text) to be placed in the start_1 on row 12.


Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform. I am recreating my
worksheet and am using Defined Names for the cells. The rng(1, 47) =
start_1, the rng(1, 48) = end_1 ect. I am doing this because I had to add a
couple columns here and remove one there, which means that now almost all my
rng references are wrong.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text