CODEWITHSUNDEEP
excelvba
khyati dedhia
khyati dedhia

Reputation: 81

Function if not working not getting desire value

I want to update Cell "D6" based on Range ("N11:N35")

    If WorksheetFunction.CountA(Range("N11:N35")) = 0 Then
        ThisWorkbook.Sheets("Check List").Range("D8") = "Complete "
    Else
        ThisWorkbook.Sheets("Check List").Range("D8") = "In Process"
    End If

Upvotes: 0

Views: 34

Answers (4)

freeflow
freeflow

Reputation: 4355

I would write you target code using a select case and also embed the test in a function.

Option Explicit

Public Function GetRangeStatus(ByVal ipRange As Range) As String
    
    Dim myBlankCount As Long
    myBlankCount = WorksheetFunction.CountA(ipRange)
    
    Select Case myBlankCount
    
        Case Is = 0
        
            GetRangeStatus = "Completed"
        
        Case Is = ipRange.Rows.Count
        
            GetRangeStatus = vbNullString
            
        Case Else
        
            GetRangeStatus = "In Process"
            
    End Select
    
End Function

So you can now say

ThisWorkbook.Sheets("Check List").Range("D8")=GetRangeStatus("Range("N11:N35"))

Upvotes: 1

Viktor West
Viktor West

Reputation: 574

does it work for you?

Private Sub Test()
    Dim iCountCells As Long
    iCountCells = Range("N11:N35").Cells.Count
    With ThisWorkbook.Sheets("Check List")
        If WorksheetFunction.CountA(Range("N11:N35")) = iCountCells Then
            .Range("D8") = "Not started "
            Exit Sub
        End If
        If WorksheetFunction.CountA(Range("N11:N35")) = 0 Then
            .Range("D8") = "Complete "
        Else
            .Range("D8") = "In Process"
        End If
    End With
End Sub

Upvotes: 2

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub testRangeEvaluate()
 Dim sh As Worksheet, rng As Range
 Set sh = ActiveSheet 'use here the sheet you need
                      'it may be ThisWorkbook.Sheets("Check List")
 Set rng = sh.Range("N11:N35")
  If WorksheetFunction.CountA(rng) = 0 Then
    sh.Range("D6").Value = ""
  ElseIf WorksheetFunction.CountA(rng) = rng.Cells.Count Then
    sh.Range("D6").Value = "Completed"
  ElseIf WorksheetFunction.CountA(rng) > 0 And _
        WorksheetFunction.CountA(rng) < rng.Cells.Count Then
    sh.Range("D6").Value = "In Process"
  End If
End Sub

The code will work even if you will extend the range in discussion...

Upvotes: 2

jamheadart
jamheadart

Reputation: 5313

Try this, I'm using CountBlank to check for blank cells.

If WorksheetFunction.CountBlank(Range("N11:N35")) = 0 Then
    ThisWorkbook.Sheets("Check List").Range("D8") = "Complete "
ElseIf WorksheetFunction.CountBlank(Range("N11:N35")) = 25 Then
    ThisWorkbook.Sheets("Check List").Range("D8") = ""
Else
    ThisWorkbook.Sheets("Check List").Range("D8") = "In Process"
End If

Also note your question says cell "D6" but your code is pointing to "D8"

Upvotes: 2

Related Questions