Counting the number of Samples in Excel

[dslt]

Hello Everybody,

I need help where I used the code [to count the number of samples within a unknown range] and it worked:

Const COL_RESULTS = 2 ' all results in this column
Const ROW_RESULTS = 13 ' reference row
Const ROW_SAMPLES = ROW_RESULTS + 1 ' how many wind samples found

Sub Count_Selection()
Dim cell As Variant
Dim count As Integer
For Each cell In Selection
If cell > 0 Then
count = ROW_SAMPLES
End If
End Sub

However the Selection Part has to be an unknown range, inwhich when I use this code whichever workbook I use it can be implemented. I don't know how to add this unknown range into this code.

Also the count = ROW_SAMPLES is a function where i go onto excel and type:
= count(range)
it gives me the answer
But I'm not allowed to do that I have to let it run normally and let the answer end up in cell "I17"

Any Help will be appreciated :] thankyou!

 

[baitmaster]

The term "Selection" is whatever range of cells is currently active, it looks like you are already passing this in

You are going to get problems with your constants, it appears you are using these incorrectly, and also incrementing them incorrectly in your code

As a guess, are you trying to do this?:

Sub Count_Selection()

Dim cl As Range, counter As Long
 
For Each cl In Selection
    If cl <> "" Then
        counter = counter + 1
    End If
Next cl
 
MsgBox counter
 
End Sub

If you do need to use the constants, tell me what you are trying to do with them

 

[dslt]

Heys baitmaster, thanks for replying

I am trying to let VBA count the number of samples from the column E3 onwards ( but not knowing how many values the range can go up to, so i need a code where it can take as much range till it reaches a blank)

Then putting the answer (the number of counts/samples) in the cell "B14"

I am given these constants to start me off that is all:
Const COL_RESULTS = 2 ' all results in this column
Const ROW_RESULTS = 13 ' reference row
Const ROW_SAMPLES = ROW_RESULTS + 1 ' how many wind samples found

However I can't use "msgbox" it just has to appear the number of counts once i click on the 'run' box.

 

[baitmaster]

OK, looks like we need to work out what range you want to look in, then use this in my sample code above. I used msgbox as a way of showing the results, we will replace this with another method instead

try this

Sub Count_Selection()

Dim cl As Range, rngToLookAt as range, counter As Long

' find last row and last column that contain data
Dim lastRow As long: lastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
Dim lastCol As long: lastCol = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

' create range from E3 to last row and last column
set rngToLookAt = range(range("E3"),cells(lastrow,lastcol))
 
' loop through and count values
For Each cl In rngToLookAt
    If cl <> "" Then
        counter = counter + 1
    End If
Next cl

' write results
Range("B14") = counter

End Sub

You may get errors if E3 is not within the range defined by [last row and last column]

 

[dslt]

Heys,

It worked but the samples was way to many that it counted, like does the code take into fact that the last row would be a blank and hence won''t count it? I got 110 samples when it was meant to be 32

thankyou

 

[baitmaster]

This code looks for the row that contains data, and the last column that contains data

It then draws a rectangle with E3 as the top left cell, and (lastRow, lastCol) as the bottom right. You should check this is the range you want analysed

Within this rectangle, it counts any cell that is not blank, and this might include cells that look blank but contain a "null" character. To test whether a cell is included in the count, we could add colour to it

For Each cl In rngToLookAt
    If cl <> "" Then
        counter = counter + 1
        [COLOR=red]cl.interior.color = 65535
[/COLOR]    End If
Next cl

 

[dslt]

Sorry about all these questions!

Last one hopefully this will help me with it all,

What i was trying to say and what I'm trying to find is that i need the number of samples from "E3" onwards I just wanted to count the number of cells in that column like E4, E5, E6,E7 etc...

I don't know how many cells would have a number in the E column so the last cell with a number will be the last cell counted since the VBA code will detect the blank cell before it.

I don't need the other cells around it

Do you get me ?

thanks

 

[baitmaster]

I think you only want to look at column E and not the whole worksheet, in which case we can adjust the range being looked at accordingly

set rngToLookAt = range(range("E3"),cells(lastrow,5))

Which for consistency could also be written

set rngToLookAt = range(cells(3,5),cells(lastrow,5))

If so, this line is no longer needed

Dim lastCol As long: lastCol = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

 

[dslt]

Heys BaitMaster,
Yes I only want Column E starting from E3 and ending at the last number in which I don't know. I want the code to be able to be tested if a large range is given in column E.

So I tried this, inputing the cell you asked me to and to delete the other but it still doesn't work, i keep getting the error message


Sub Count_Selection()
Dim cl As Range, rngToLookAt as range, counter As Long

' find last row and last column that contain data
Dim lastRow As long: lastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
set rngToLookAt = range(range("E3"),cells(lastrow,5))

' create range from E3 to last row and last column
set rngToLookAt = range(range("E3"),cells(lastrow,lastcol))

' loop through and count values
For Each cl In rngToLookAt
If cl <> "" Then
counter = counter + 1
End If
Next cl
' write results
Range("B14") = counter End Sub

 

[baitmaster]

Sorry, to clarify, the previous update was a change to an existing line of code, not a new line. I've adjusted some comments to show this too

I've also added "Option Explicit" outside of the subroutine. This forces you to declare variables, and would have highlighted to you that "lastCol" was no longer in use, which I am guessing is the cause of your error. If not, please let me know what problem you are getting

Option Explicit should always be used as best practice, you can set this automatically in future from VBA > Tools > Options [require variable declaration tick box]

Option Explicit
 
Sub Count_Selection() 
Dim cl As Range, rngToLookAt as range, counter As Long 

' find last row that contains data 
Dim lastRow As long: lastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row 

' create range from E3 to last row in column E
set rngToLookAt = range(range("E3"),cells(lastrow,5))

' loop through and count values 
For Each cl In rngToLookAt 
    If cl <> "" Then 
        counter = counter + 1 
    End If 
Next cl 

' write results 
Range("B14") = counter
End Sub