Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a row within a range that is defined in a sum function | Excel Discussion (Misc queries) | |||
Input Boxes 4 Inserting A Range In A Formula | Excel Discussion (Misc queries) | |||
Inserting a range of cells | Excel Worksheet Functions | |||
Inserting selected range into Word document | Excel Programming | |||
excel 97 : automatically inserting a specific value for a range? | Excel Programming |