CODEWITHSUNDEEP
excelvba
bwifler
bwifler

Reputation: 11

Is there a way to not have a blank cell considered 0 in VBA?

New to vba and trying to do something very basic. I have a column full of numbers that also contains blank cells. When I try to do this If statement, Excel considers the blank cells as the value 0. I only want the cells with numbers to have the "Alerted Transaction" value, not the blank cells. Any help is appreciated. Thanks. 

If Columns("J").Value > 0 Then Columns("J").Value = "Alerted Transaction"

Upvotes: 1

Views: 1513

Answers (3)

I_bad_at_code
I_bad_at_code

Reputation: 64

You can check to see if a cell is empty by using the IsEmpty function. I don't really mess around with Excel so I don't know how you would check a range of cells.

Upvotes: 0

B.Gao
B.Gao

Reputation: 165

For Each c In Worksheets("Sheet1").Range("J1:J10").Cells 
    If Not IsEmpty(c.Value) Then c.Value = "Alert" 
Next

As @BigBen pointed out, you probably don't want to IsEmpty the whole column, this code checks Row 1 to Row 10 in Column J. You may adopt the code to your scenario accordingly.

See doc on IsEmpty and doc on For loop

Upvotes: 1

SmileyFtW
SmileyFtW

Reputation: 336

Define the range you want to check, loop each cell in the range and check for empty cells.

dim myWS As Worksheet
set myWS = ThisWorkbook.Worksheets("myWorksheet")

With myWS
   Dim loopRange As Range
   set loopRange = .Range(.Cells(2,10),.Cells(.UsedRange.Rows.Count,10))
End with
dim currCell As Range
for each currCell in loopRange
   if currCell.Text<>vbNullString Then
      currCell.Offset(0,3).Value = "Alerted Transaction"
   end if
next

There are better ways to get the last populated cell in a column, but that is for another day :)

Upvotes: 1

Related Questions