| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fomat change
I use the following code to input data on to a worksheet with a user form.
Everything used to work fine until I changed the cell format of the range B7:B23 to "text" format. Now every time I enter data using my user form the new data entry goes on top of the last entry instead of moving down a row. Any suggestions? Dim LastRow As Object Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) If Me.ComboBox1.Value < "" And Not IsNull(Me.ComboBox1) And Me.TextBox2.Value < "" And Not IsNull(Me.TextBox2) Then LastRow.Offset(2, 0).Value = ComboBox1.Text LastRow.Offset(2, 5).Value = TextBox2.Text |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fomat change
Regardless of the formatting, this line:
Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) should always find the last filled cell. If it worked before, it shouldn't have (if the cells were actually empty) Set LastRow = Worksheets("Blend Sheet").Range("B24").End(xlUp).offset(1,0) would be what you want. -- Regards, Tom Ogilvy "gregork" wrote in message ... I use the following code to input data on to a worksheet with a user form. Everything used to work fine until I changed the cell format of the range B7:B23 to "text" format. Now every time I enter data using my user form the new data entry goes on top of the last entry instead of moving down a row. Any suggestions? Dim LastRow As Object Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) If Me.ComboBox1.Value < "" And Not IsNull(Me.ComboBox1) And Me.TextBox2.Value < "" And Not IsNull(Me.TextBox2) Then LastRow.Offset(2, 0).Value = ComboBox1.Text LastRow.Offset(2, 5).Value = TextBox2.Text |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fomat change
Thanks for the help Tom. I think I know whats going on...I have a button
that clears the fields in the range that I am entering data to. Now that I have changed the format to "text" I think it is causing a problem. Here is the code: Private Sub CommandButton1_Click() With Worksheets("Blend Sheet") .Range("B7:B23,G7:G23").Value = "" I think the "" is the problem because the cells are not empty. How can I change the code so it clears the cells. Many Thanks Greg "Tom Ogilvy" wrote in message ... Regardless of the formatting, this line: Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) should always find the last filled cell. If it worked before, it shouldn't have (if the cells were actually empty) Set LastRow = Worksheets("Blend Sheet").Range("B24").End(xlUp).offset(1,0) would be what you want. -- Regards, Tom Ogilvy "gregork" wrote in message ... I use the following code to input data on to a worksheet with a user form. Everything used to work fine until I changed the cell format of the range B7:B23 to "text" format. Now every time I enter data using my user form the new data entry goes on top of the last entry instead of moving down a row. Any suggestions? Dim LastRow As Object Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) If Me.ComboBox1.Value < "" And Not IsNull(Me.ComboBox1) And Me.TextBox2.Value < "" And Not IsNull(Me.TextBox2) Then LastRow.Offset(2, 0).Value = ComboBox1.Text LastRow.Offset(2, 5).Value = TextBox2.Text |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fomat change
Private Sub CommandButton1_Click()
With Worksheets("Blend Sheet") .Range("B7:B23,G7:G23").ClearContents -- Regards, Tom Ogilvy "gregork" wrote in message ... Thanks for the help Tom. I think I know whats going on...I have a button that clears the fields in the range that I am entering data to. Now that I have changed the format to "text" I think it is causing a problem. Here is the code: Private Sub CommandButton1_Click() With Worksheets("Blend Sheet") .Range("B7:B23,G7:G23").Value = "" I think the "" is the problem because the cells are not empty. How can I change the code so it clears the cells. Many Thanks Greg "Tom Ogilvy" wrote in message ... Regardless of the formatting, this line: Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) should always find the last filled cell. If it worked before, it shouldn't have (if the cells were actually empty) Set LastRow = Worksheets("Blend Sheet").Range("B24").End(xlUp).offset(1,0) would be what you want. -- Regards, Tom Ogilvy "gregork" wrote in message ... I use the following code to input data on to a worksheet with a user form. Everything used to work fine until I changed the cell format of the range B7:B23 to "text" format. Now every time I enter data using my user form the new data entry goes on top of the last entry instead of moving down a row. Any suggestions? Dim LastRow As Object Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) If Me.ComboBox1.Value < "" And Not IsNull(Me.ComboBox1) And Me.TextBox2.Value < "" And Not IsNull(Me.TextBox2) Then LastRow.Offset(2, 0).Value = ComboBox1.Text LastRow.Offset(2, 5).Value = TextBox2.Text |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fomat change
Thanks Tom. All sorted now.
Cheers Greg "Tom Ogilvy" wrote in message ... Private Sub CommandButton1_Click() With Worksheets("Blend Sheet") .Range("B7:B23,G7:G23").ClearContents -- Regards, Tom Ogilvy "gregork" wrote in message ... Thanks for the help Tom. I think I know whats going on...I have a button that clears the fields in the range that I am entering data to. Now that I have changed the format to "text" I think it is causing a problem. Here is the code: Private Sub CommandButton1_Click() With Worksheets("Blend Sheet") .Range("B7:B23,G7:G23").Value = "" I think the "" is the problem because the cells are not empty. How can I change the code so it clears the cells. Many Thanks Greg "Tom Ogilvy" wrote in message ... Regardless of the formatting, this line: Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) should always find the last filled cell. If it worked before, it shouldn't have (if the cells were actually empty) Set LastRow = Worksheets("Blend Sheet").Range("B24").End(xlUp).offset(1,0) would be what you want. -- Regards, Tom Ogilvy "gregork" wrote in message ... I use the following code to input data on to a worksheet with a user form. Everything used to work fine until I changed the cell format of the range B7:B23 to "text" format. Now every time I enter data using my user form the new data entry goes on top of the last entry instead of moving down a row. Any suggestions? Dim LastRow As Object Set LastRow = Worksheets("Blend Sheet").Range("B23").End(xlUp) If Me.ComboBox1.Value < "" And Not IsNull(Me.ComboBox1) And Me.TextBox2.Value < "" And Not IsNull(Me.TextBox2) Then LastRow.Offset(2, 0).Value = ComboBox1.Text LastRow.Offset(2, 5).Value = TextBox2.Text |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional fomat series | Excel Discussion (Misc queries) | |||
| Fomat Cell | Excel Discussion (Misc queries) | |||
| I wish to fomat numbers in Excel | Excel Worksheet Functions | |||
| how do I fomat cell for latitude | Excel Discussion (Misc queries) | |||
| Time Fomat | Excel Worksheet Functions | |||
Linear Mode
