Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to count of non-Empty cells in Excel VBA?

Status
Not open for further replies.
reportingbuzz

reportingbuzz

MIS
Feb 9, 2007
46
0
0
US
Hi,

While looping through a column from 2 to 12, I want to see those cells which has some value, and do a count of those cells which has the value.

How can I do that?

Thanks.

Code: 
With wSht
    For iRow = 2 To 105

    'grab the value of the 'AiRow'
    'varPlan = .Cells(iRow, 1).Value
    varPlan = "Channel Manager"
    
        'Loop through the columns to get the components
        'Count the number of components and insert that many
        'rows for the Plan
        Count = 1
        For iColumn = 2 To 12
            varComponent = .Cells(iRow, iColumn)
            
            
            If Not IsEmpty(varComponent) Then
                MsgBox (Count & ": " & varComponent)
                'getPlanUI (varPlan)
                'getPlanComponentUI (varComponent)
            End If
        Count = Count + 1
        Next

[b]        'This returns the total number of columns
        'how to get the count of non-empty cells?[/b]
        MsgBox (Count)
        
    Exit For
    Next
    
    
End With

There are 3 cells with data. When I do the above, I see 3 alert boxes but the 3rd time, I see a wrong count of 4
1st alert - 1: PlanName
2nd alert - 2: PlanName
3rd alert - 4: PlanName

The code that shows the above alert boxes is:

Code: 
MsgBox (Count & ": " & varComponent)

Where is this 4 coming from? Any ideas?

2ndly, how can I can count of non-empty cells? My count here returns the total number of columns I am leaping through.

Thanks.
 
Sort by date Sort by votes
Count = 0
For iColumn = 2 To 12
varComponent = .Cells(iRow, iColumn)
If Not IsEmpty(varComponent) Then
Count = Count + 1
MsgBox (Count & ": " & varComponent)
'getPlanUI (varPlan)
'getPlanComponentUI (varComponent)
End If
Next
MsgBox (Count)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Count should start at ZERO, not one.

...
'Loop through the columns to get the components
'Count the number of components and insert that many
'rows for the Plan
Count = [red]0[/red]
For iColumn = 2 To 12
varComponent = .Cells(iRow, iColumn)
...

But why loop? Why not just use CountIf?
Code: 
MyCount = _
Application.WorksheetFunction.CountIf(Range("A1:A12"), "Channel Manager")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Upvote 0 Downvote
Thanks for the tip. Yes. Count was placed in the wrong position. And it should start from 0.

Based on this count, can we insert the 'count' number of rows into another sheet with the value 'varComponent'?

Thanks.
 
Upvote 0 Downvote
Count = 0
lngRow = 2
For iColumn = 2 To 12
varComponent = .Cells(iRow, iColumn)
If Not IsEmpty(varComponent) Then
Count = Count + 1
Worksheets("another sheet").Cells(lngRow, 1) = varComponent
lngRow = lngRow + 1
End If
Next
MsgBox Count & " row(s) added to 'another sheet'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I call the function within this code:

Code: 
If Not IsEmpty(varComponent) Then
                Count = Count + 1
                MsgBox (Count & ": " & varComponent)
                getPlanUI (varPlan)
                getPlanComponentUI (varComponent)
            End If

This is the function that adds the component into the other sheet starting at B2 to the count of components that exist.

Code: 
Sub getPlanComponentUI(varComponent)

Dim iRows As Long
Dim wSheet As Worksheet

Set wSheet = Worksheets("PlanComponentUIs")

With wSheet
    For iRows = 2 To 30
        If .Cells(iRows, 2).Value = varComponent Then
            'grab the value of the 'AiRows'
            varPlanComponenetUI = .Cells(iRows, 1).Value
            
            'insert into the Plan_PlanComponent_Link sheet at B2.
            Worksheets("Plan_PlanComponent_Link").Range("B2").Value = varPlanComponenetUI
        End If
    Next
End With

End Sub
 
Upvote 0 Downvote
Hi,

Since I want to simultaneously insert two columns of a same row at a time, I added two insert lines within the code. Instead of assigning a variable to the value of the cell, I assigned it to a function. Function returns the value to insert into the cell whether it is for Column1(A) or Column2(B). But I get an error -

Expected Function or variable

My code is:

Code: 
Set wSht = Worksheets("Plan_Mapping")

With wSht
    For iRow = 2 To 105

    'grab the value of the 'AiRow'
    'varPlan = .Cells(iRow, 1).Value
    varPlan = "Channel Manager"
        
        'Loop through the columns to get the components
        'Count the number of components and insert that many
        'rows for the Plan
        Count = 0
        lngRow = 2
        For iColumn = 2 To 12
            varComponent = .Cells(iRow, iColumn)
            
            If Not IsEmpty(varComponent) Then
                Count = Count + 1
                MsgBox (Count & ": " & varComponent)
                
                With Worksheets("Plan_PlanComponent_Link")
                    'Enter the values at the given row and column
                    For iRows = 2 To 4
                        [b]Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 1) = getPlanUI(varPlan)[/b]
                        [b]Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 2) = getPlanComponentUI(varComponent)[/b]
                        lngRow = lngRow + 1
                    Next
                
                End With
                
                'getPlanUI(varPlan)
                'getPlanComponentUI (varComponent)
            End If
        
        Next
        
        MsgBox (Count)
    Exit For
Next
MsgBox Count & " row(s) added to 'another sheet'"

End With

Should the function return some value in order to assign to the above statement?

getPlanUI(varPlan)

Code: 
Sub getPlanUI(varPlan)

Dim iRow As Long
Dim wSht As Worksheet

Set wSht = Worksheets("PlanUIs")

With wSht
    lngRow = 2
    For iRow = 2 To 105
        If .Cells(iRow, 2).Value = varPlan Then
            'MsgBox (varPlan & " found in B" & iRow & vbCrLf & "A" & iRow & " = " & .Cells(iRow, 1).Value)
            'grab the value of the 'AiRow'
            varPlanUI = .Cells(iRow, 1).Value
        End If
    Next
        
End With

End Sub

getPlanComponentUI(varComponent)

Code: 
Sub getPlanComponentUI(varComponent)

Dim iRows As Long
Dim wSheet As Worksheet

Set wSheet = Worksheets("PlanComponentUIs")

With wSheet
    For iRows = 2 To 30
        If .Cells(iRows, 2).Value = varComponent Then
            'grab the value of the 'AiRows'
            varPlanComponentUI = .Cells(iRows, 1).Value
        End If
    Next
End With

End Sub

Any ideas how to do simultaneous entry of data into the cells?

Thanks.
 
Upvote 0 Downvote
A starting point:
[!]Function[/!] getPlanUI(varPlan)
Dim iRow As Long
With Worksheets("PlanUIs")
For iRow = 2 To 105
If .Cells(iRow, 2).Value = varPlan Then
[!]getPlanUI [/!]= .Cells(iRow, 1).Value
[!]Exit For[/!]
End If
Next
End With
End [!]Function[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
That was good suggestion. It showed how to use a return value within a function.

However I see the following.

Code: 
Plan_UniqueIdentifier    PlanComponent_UniqueIdentifier
3004661	                 46387
3004661	                 46387
3004661	                 46387
3004661	                 46379
3004661	                 46379
3004661	                 46379
3004661	                 3004586
3004661	                 3004586
3004661	                 3004586

Instead it should just be only 3 rows of data. Plan (3004661) has 3 components as below.

Code: 
Plan_UniqueIdentifier    PlanComponent_UniqueIdentifier
3004661	                 46387
3004661	                 46379
3004661	                 3004586

My function that does the above is:

Code: 
Function getPlan()
    
Set wSht = Worksheets("Plan_Mapping")

With wSht
    For iRow = 2 To 105

    'grab the value of the 'AiRow'
    'varPlan = .Cells(iRow, 1).Value
    varPlan = "Channel Manager"
        
        'Loop through the columns to get the components
        'Count the number of components and insert that many
        'rows for the Plan
        Count = 0
        lngRow = 2
        For iColumn = 2 To 12
            varComponent = .Cells(iRow, iColumn)
            
            If Not IsEmpty(varComponent) Then
                Count = Count + 1
                MsgBox (Count & ": " & varComponent)
                
                With Worksheets("Plan_PlanComponent_Link")
                    'Enter the values at the given row and column
                    For iRows = 2 To 4
                        Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 1) = getPlanUI(varPlan)
                        Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 2) = getPlanComponentUI(varComponent)
                        lngRow = lngRow + 1
                    Next
                
                End With
            End If
        
        Next
        
        MsgBox (Count)
    Exit For
Next
MsgBox Count & " row(s) added to 'another sheet'"

End With

End Function

Since for each plan, there could be multiple components, do I add anything to handle that within the function below:

Code: 
Function getPlanComponentUI(varComponent)

Dim iRows As Long
Dim wSheet As Worksheet

Set wSheet = Worksheets("PlanComponentUIs")

With wSheet
    For iRows = 2 To 30
        If .Cells(iRows, 2).Value = varComponent Then
            'grab the value of the 'AiRows'
            getPlanComponentUI = .Cells(iRows, 1).Value
        End If
    Next
End With

End Function
 
Upvote 0 Downvote
If Not IsEmpty(varComponent) Then
Count = Count + 1
With Worksheets("Plan_PlanComponent_Link")
'Enter the values at the given row and column
.Cells(lngRow, 1) = getPlanUI(varPlan)
.Cells(lngRow, 2) = getPlanComponentUI(varComponent)
lngRow = lngRow + 1
End With
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Never mind. I added another For Loop and moved the lngRow+1 to outside of both the for loops:

Code: 
If Not IsEmpty(varComponent) Then
                Count = Count + 1
                MsgBox (Count & ": " & varComponent)
                
                With Worksheets("Plan_PlanComponent_Link")
                    'Enter the values at the given row and column
                    For iRows = 2 To 4
                        Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 1) = getPlanUI(varPlan)
                        For i = 2 To 4
                            Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 2) = getPlanComponentUI(varComponent)
                        Next
                    Next
                    lngRow = lngRow + 1
                End With
            End If

Thanks for the suggestions.
 
Upvote 0 Downvote
Why writing 9 times the same value in the same cell ???

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
In the line:
Code: 
If Not IsEmpty(varComponent) Then
                Count = Count + 1
                'MsgBox (Count & ": " & varComponent)
                
                With Worksheets("Plan_PlanComponent_Link")
                    'Enter the values at the given row and column
                    [b]For iRows = 2 To 4[/b]
                        Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 1) = getPlanUI(varPlan)
                        [b]For i = 2 To 4[/b]
                            Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 2) = getPlanComponentUI(varComponent)
                        Next
                    Next
                    lngRow = lngRow + 1
                End With
            End If

In the bold lines above, I hardcoded the ending row number since I knew that this plan has 3 components. How to do away with this '4' and instead replace it with the total count of the components:

I replaced it with:

Code: 
With Worksheets("Plan_PlanComponent_Link")
                    'Enter the values at the given row and column
                    MsgBox ("Count before 1st For loop: " & Count)
                    For iRows = 2 To Count
                        Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 1) = getPlanUI(varPlan)
                        MsgBox ("Count before 2nd For loop: " & Count)
                        For i = 2 To Count
                            Worksheets("Plan_PlanComponent_Link").Cells(lngRow, 2) = getPlanComponentUI(varComponent)
                        Next
                    Next
                    MsgBox ("Count after the 2 For loop: " & Count)
                    lngRow = lngRow + 1
                End With

But when I do that, I get a blank row of data like follows followed by the other 2 rows:

Code: 
Plan_UniqueIdentifier	PlanComponent_UniqueIdentifier
	
3004661	                46379
3004661	                3004586

Instead of the following when I hardcoded the number 4:

Code: 
Plan_UniqueIdentifier    PlanComponent_UniqueIdentifier
3004661	                 46387
3004661	                 46379
3004661	                 3004586
 
Upvote 0 Downvote
Why writing 9 times the same value in the same cell ???
Click to expand...

Now I get the appropiate number of rows as the number of components by nesting it within 2 for loops. But any idea about doing away with the hard-coding as in the previous post?

Thanks.
 
Upvote 0 Downvote
Try to make sense with all the help you've already got instead of doing cargo cult programming.
Good luck.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
618
dhookom
dhookom
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
128
combo
combo
xwb
  • Locked
  • Helpful tip
Performing VBA operations in other languages
Replies
1
Views
109
strongm
strongm
Andrzejek
  • Question
Outlook VBA - Trust access
Replies
3
Views
222
hermanlaksko
hermanlaksko
Katto
  • Locked
  • Question
VBA permutation of arrays
2
Replies
22
Views
232
Katto
Katto

Part and Inventory Search

Sponsor

Back
Top