Inserting value at end of range
 

Hey guys

Lets say I have Range A1:A100 and a textbox(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell in
the range. The next empty cell in the range is A26, so
therefor this is where I would like Textbox1.value to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and have
the vba code refer to that cell as the location for the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine

Inserting value at end of range
 

Try this

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Todd Huttenstine" wrote in message ...
Hey guys

Lets say I have Range A1:A100 and a textbox(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell in
the range. The next empty cell in the range is A26, so
therefor this is where I would like Textbox1.value to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and have
the vba code refer to that cell as the location for the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine

Inserting value at end of range
 

Todd

Sub find_last_plus_one()
Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Activate
End Sub

Will activate first blank cell in column A

Gord Dibben XL2002

On Fri, 28 Nov 2003 13:25:22 -0800, "Todd Huttenstine"
wrote:

Hey guys

Lets say I have Range A1:A100 and a textbox(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell in
the range. The next empty cell in the range is A26, so
therefor this is where I would like Textbox1.value to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and have
the vba code refer to that cell as the location for the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine

Inserting value at end of range
 

Ok thanx, one last question...

I also have 1 checkbox1. How do I get it to perform that
code if checkbox1 is checked? If checkbox1 is not
checked, dont do the code.

Thanx


-----Original Message-----
Try this

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Todd Huttenstine"
wrote in message news:030101c3b5f6$21ae7710
...
Hey guys

Lets say I have Range A1:A100 and a textbox(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell in
the range. The next empty cell in the range is A26, so
therefor this is where I would like Textbox1.value to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and have
the vba code refer to that cell as the location for the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine


.

Inserting value at end of range
 

Todd,

Use something like

If Me.CheckBox1.Value = True Then
Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Todd Huttenstine" wrote in
message ...
Ok thanx, one last question...

I also have 1 checkbox1. How do I get it to perform that
code if checkbox1 is checked? If checkbox1 is not
checked, dont do the code.

Thanx


-----Original Message-----
Try this

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Todd Huttenstine"
wrote in message news:030101c3b5f6$21ae7710
...
Hey guys

Lets say I have Range A1:A100 and a textbox(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell in
the range. The next empty cell in the range is A26, so
therefor this is where I would like Textbox1.value to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and have
the vba code refer to that cell as the location for the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine


.

Inserting value at end of range
 

Works perfect.

Thanx

-----Original Message-----
Todd,

Use something like

If Me.CheckBox1.Value = True Then
Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Todd Huttenstine"
wrote in
message ...
Ok thanx, one last question...

I also have 1 checkbox1. How do I get it to perform
that
code if checkbox1 is checked? If checkbox1 is not
checked, dont do the code.

Thanx


-----Original Message-----
Try this

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Todd Huttenstine"

wrote in message news:030101c3b5f6$21ae7710
...
Hey guys

Lets say I have Range A1:A100 and a textbox
(Textbox1).
Currently there are values in A1:A25. I need for for
TextBox1 value to be inserted in the next empty cell
in
the range. The next empty cell in the range is A26,
so
therefor this is where I would like Textbox1.value
to be
inserted.

How would write this?

I usualy just do a count formula+1 in any cell and
have
the vba code refer to that cell as the location for
the
next value to be inserted, but this can clutter up my
worksheet with variables.

Thanx

Todd Huttenstine


.



.

Inserting value at end of range
 

Ron de Bruin wrote

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value

Curious. What does the (2) after (xlUp) mean?

--
David

Inserting value at end of range
 

It's the equivalent of
Range("A" & Rows.Count).End(xlUp).offset(1,0)

(it comes down one row.)

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print that have some more variations for using ranges.
http://www.cpearson.com/excel/cells.htm

David Turner wrote:

Ron de Bruin wrote

Range("A" & Rows.Count).End(xlUp)(2) = TextBox1.Value

Curious. What does the (2) after (xlUp) mean?

--
David

--

Dave Peterson

Inserting value at end of range
 

That works just fine ... unless you have more data below the first blan
row, in which case you end up appending the new data at the very botto
instead of the relative bottom ... if you know what I mean.

I would suggest, instead ...

Dim myRange as Range

Range("A1").Select
Set myRange = ActiveCell.CurrentRegion
LastRow = myRange.Cells(myRange.Cells.Count).Row

the, insert the new data at LastRow +

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Inserting value at end of range
 

Dave Peterson wrote

It's the equivalent of
Range("A" & Rows.Count).End(xlUp).offset(1,0)

(it comes down one row.)

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print that have some more variations for using ranges.
http://www.cpearson.com/excel/cells.htm

Thanks.

--
David