empty cell

[Axel]

Just curious
If I have a code that eksample
TextBox1.Text = ""
and
ActiveCell = TexBox1.Text
When I then run a code later to find the empty cells, the cell is not
empty (Looks like it empty).
This code wont find the cell
For Each c In ActiveSheet.Range("something")
If Not IsEmpty(c) Then Something
Next c

I had to write a ClearContens in the code to make above code find the
empty cell
Isn't "" the same as empty?

Thanks

Aksel

 

[Don Guillett]

if len(application.trim(c))>0
or <1

 

[Bob Umlas]

="" is NOT the same as Empty. Even if you entered ="" & then copy/Paste
special values, you have a null text string, but that's not empty! Your code
would be better off with
If Textbox1.Text = "" then Activecell.Clearcontents else
activecell.value=Textbox1.Text

 

[Axel]

Thank you for explain the difference

Aksel

 

[Dave Peterson]

I don't think I've seen setting a range.value = "" not be the same as clearing
the cell.

You sure that the textbox didn't have a space character in it?

When I did this:

With ActiveSheet
With .Range("a1")
.Value = ""
Debug.Print "Quotes: " & IsEmpty(.Value)
.ClearContents
Debug.Print "Clearcontents: " & IsEmpty(.Value)
End With
end with

I got back:

Quotes: True
Clearcontents: True

I do agree with Bob's note about using a formula that evaluates to ="" and then
converting to values does cause the cell to not be empty.

But that doesn't seem to fit your case.

 

[Axel]

I dont need a solution to this. just for info:

First I have a combobox who I can choose the range

Private Sub ComboBox1_Change()
Dim iCtr As Integer
Firstline:
On Error GoTo Errorline
iCtl = ComboBox1.Value
TextBox1.Text = Range("B" & CStr(3 + ComboBox1.Value))
GoTo lastline
Errorline:
MsgBox "kun tall mellom 1 og 159 er gyldig i Radnummer"
lastline:
End Sub


I can also use a button in the same userform to find the first empty
cell in same range

Private Sub CommandButton3_Click()

ActiveSheet.Unprotect Password:="driller"
Application.ScreenUpdating = False

'Dim rngActive As Excel.Range
Dim c As Range
Set c = ActiveCell

For Each c In ActiveSheet.Range("B4:B159")
If Not IsEmpty(c) Then GoTo line2 Else GoTo line1
line2:
Next c

'lastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select
line1:
ComboBox1.Value = c.Offset(0, -1).Value

ActiveSheet.Protect Password:="", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


And I have a button for sending the 2 textboxes values in to the range.

Private Sub CommandButton1_Click()
If ComboBox1.Value > 159 Then GoTo line1 Else GoTo line2
line1:
MsgBox "kun tall mellom 1 og 159 er gyldig i Radnummer"
GoTo lastline
line2:
ActiveSheet.Unprotect Password:="driller"
Application.ScreenUpdating = False
Dim iCtr As Integer
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.TextBox1
Range("C" & CStr(iCtr + 3)) = Me.TextBox2
lastline:
ActiveSheet.Protect Password:="", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Unload InsertItems
End Sub

so if I write nothing in the textbox, the selected cells will be empty.
But that din't work
So next time I use the commandbutton 3 macro, it didn't find the cell. I
had to put in:
If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents. in
the commandbutton 1 macro.

Aksel

 

[Dave Peterson]

I don't think that seeing the code will help.

Range("B" & CStr(iCtr + 3)) = Me.TextBox1

Maybe something is in that textbox that you don't see--either a space character
or some other whitespace.