Fomat change

[gregork]

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

 

[Tom Ogilvy]

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.

 

[gregork]

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]

Private Sub CommandButton1_Click()
With Worksheets("Blend Sheet")
.Range("B7:B23,G7:G23").ClearContents

 

[gregork]

Thanks Tom. All sorted now.

Cheers
Greg

Private Sub CommandButton1_Click()
With Worksheets("Blend Sheet")
.Range("B7:B23,G7:G23").ClearContents