Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create defined name using variable file name in reference | Excel Worksheet Functions | |||
Need to use the reference in a defined name in adifference colum | Excel Discussion (Misc queries) | |||
Updating cells which reference a defined name | Excel Discussion (Misc queries) | |||
VBA for changing a defined name reference | Excel Programming | |||
User-defined function creating circular reference | Excel Programming |