Blank Values - Xtreme Visual Basic Talk

10-12-2004, 03:43 AM

rudekid

Regular

Join Date: Sep 2003

Posts: 54

Blank Values

Scratching my head on this one:

I have a column for which each cell contains an Excel formulae that returns a number OR blank when calculated. Depending on whether this number is greater or smaller than an earlier variable the user has set at the beginning of the procedure using an input box - Max - defines how my subroutine treats each row that's rather crucial to the whole point of my program.

The functions aren't important, what is important is getting my code to recognise blanks. My input box type has been set to 1, i.e. the user has to enter a number. My If statement looks like this:

If book1.Cells(RowNum, 31).Value > Max Then
book1.Range(book1.Cells(RowNum, 32), book1.Cells(RowNum, 32)).Value = "Deleted"

However: it treats a blank cell as > Max and flags it "deleted". I've tried copying and pasting the column as XLvalues, (i.e. deleting the function and leaving only the numbers or a blank) but it still treats each apparently blank cell as greater than Max.

Can anybody help? thx

10-12-2004, 03:50 AM

planetwise planetwise is offline

Regular

Join Date: Jun 2004

Location: Groningen, Netherlands

Posts: 75

Try a msgbox to show you what value is read?

__________________
Don't declare my string in public, it isn't integer.

10-12-2004, 03:53 AM

Wheels1978 Wheels1978 is offline

Junior Contributor

Join Date: Apr 2004

Location: 's-Gravenpolder, NL

Posts: 389

Try

Code:

If book1.cells(RowNum,31).value<>"" then
 If book1.Cells(RowNum, 31).Value > Max Then
    book1.Range(book1.Cells(RowNum, 32), book1.Cells(RowNum, 32)).Value = "Deleted"
 End If
End If

Maybe you should also refer to your range using

Code:

Workbooks(book1).Worksheets("YourSheetName").Cells(RowNum,31)
'or
ActiveWorkbook.Worksheets("YourSheetName").Cells(RowNum,31)

I assume that book1 is a new book. book1 should be book1.xls if it is already saved.

Just in case you get errors on your references.

__________________
"Accept what you can not change and change what you can not accept" - Me
"Normal is the average of all oddities" - Me
"Don't re-invent the wheel!" - My dad
Please enclose your code in [vb] and [/vb] tags