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

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

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

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

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