Value not appearing to VB

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
I'm using the code below to test for a non-blank entry in the cell named "Amount" but VB is allowing the test to pass with no entry in the field. This is working properly on other fields (except one) that are text. What have I forgotten?

If Len(Amount) = 0 Then
MsgBox "Amount must not be blank", vbExclamation
Cells.Range("Amount").Select
GoTo Lastline
End If

The other one that isn't working is a formula that currently resolves to False but it is passing the test also. Any help appreciated.

If LO = False Then
MsgBox "Problem with Loan Officer field", vbExclamation
Cells.Range("LO").Select
GoTo Lastline
End If
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
If Len(Range("Amount").Value) = 0 Then

This still fails the blank test.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
are you sure the cell is blank? Note that cells with formula generated 'blanks' are not blank - see the example:
Book5
ABCD
1
2
310LenBlank
4 0FALSE
5
6
Sheet1



That said, the len() check should have worked
Paddy
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345

ADVERTISEMENT

It's as blank as the expression on my face right now. No formula but the cell does have data validation to allow Decimal between .01 and 200000 and the cell is named. These wouldn't be causing the problem, would they?

Earlier in my code are

Dim Amount As String

and

Amount = Workbooks("Sight Draft.xls").Sheets("SDA").Range("Amount")
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345

ADVERTISEMENT

MsgBox Range("Amount").Value
shows nothing.

MsgBox Len(Range("Amount").Value)
shows 0.

I also have a watch window running and the expected amount or blank is shown, depending on what I enter in the cell.
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
I agree, it should. Just to be sure, I 'd out my line and pasted yours in but it didn't change a thing. Baffling!
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
Anyone have an idea why the last part of my original post (False) isn't working either?
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top