CODEWITHSUNDEEP
functionvbaexcelexcel-2007
Analytic Lunatic
Analytic Lunatic

Reputation: 3944

VBA: Excel range always coming up as Empty?

I have 2 fields in an Excel Workbook:

1. MED - Range("J13:K13")

Formula: =IF(D5=1,"X","")

2. NON-MED - Range("L13:M13")

Formula: =IF(D5=2,"X","")

When using the below code, the fields are always coming up as NOT empty, thus saving a value of '1':

    '1. Med
    If Not IsEmpty(range("J13:K13").value) Then
        Worksheets("FeedSamples").range("AK" & newRow).value = 1
    Else
        Worksheets("FeedSamples").range("AK" & newRow).value = 0
    End If
    '2. Non-Med
    If Not IsEmpty(range("L13:M13").value) Then
        Worksheets("FeedSamples").range("AL" & newRow).value = 1
    Else
        Worksheets("FeedSamples").range("AL" & newRow).value = 0
    End If

I tried using the following in my IF statement, but the count is always '1':

    '1. Med
    If WorksheetFunction.CountA(range("J13:K13")) = 0 Then

Anyone have any ideas? If the field has an 'X' for a value (or length count of 1) then I need to save '1' in my other worksheet. If the field is empty (or length count of 0) then I need to save '0' in my other worksheet.

I am at a lost for why my check for IsEmpty() is not functioning correctly.

Upvotes: 1

Views: 2012

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I don't think you can use IsEmpty on a range of cells. I can replicate this behavior, whenever I pass a range of 2+ cells, the IsEmpty function returns False.

Try using If Application.WorksheetFunction.CountA(Range("J13:K13")) = 0

This will check for the presence of any values in the range, and return a value of 0 only when all cells contain no values (the range is empty), otherwise it will return a non-zero value, indicating the range is not empty.

Revision

The presence of formula in the range, even formula that return a null string ("") value will be counted by the COUNTA function.

You may be able to use the COUNTIF function:

If Application.WorksheetFunction.CountIF(Range("J13:K13"),"") = Range("J13:K13").Cells.Count

This will avoid counting formulas that return a null string.

This statement evaluates True if and only if all cells are blank/null string values.

Upvotes: 5

Related Questions