Getting a count of formula results only.
 

Hi Everybody,
I've googled for an hour and must not be coming up with a meaningfu
search phrase. Lots of stuff on counts of all kinds but not what I'
looking for. Please help.
I use the following bit of code in a lot of different Subroutines t
count the number of entries in a range.

Entries = Excel.WorksheetFunction.CountA(Wks2.Range("CABSDTO PS"))

But now I find the need to obtain the a count from a range tha
contains formulas, so my count value ends up being the entire range (
of cells in range) because the COUNTA function treats the formula i
the cell as something to count.
What I need is a count of the cells in this range where the formula ha
calculated a value other than 0. Thanks in advance for any help.

Here is my current Code

Private Sub ImportCAtoSTD_Click()
Dim Wks2 As Worksheet
Dim Wks3 As Worksheet
Dim CopyRow As Long
Dim Entries As Long
Dim i As Long
Dim Cnt As Integer
Dim Cnt2 As Long
Dim Cnt3 As Long
Dim Msg As Integer
Dim Response As Integer
Dim N As Double

Msg = MsgBox("Is the Cabinet Area Takeoff complete and" & (Chr(13))
_
"cabinets with tops are indicated as such by" & (Chr(13)) & _
"a dimension in the Cabinet Database?", vbYesNo + vbQuestion, "Impor
Room # & Top SF Cabinets")
If Msg = 6 Then
Application.ScreenUpdating = False

Set Wks2 = Worksheets("Cabinet Areas")
Set Wks3 = Worksheets("Slabs-Tops-Decks")
'Clear the worksheet prior to importing new information
Wks3.Range("STDImportRange").ClearContents
Cnt = 0
Cnt2 = 0
Cnt3 = 0
'Set CopyRow to numerical value of first row to start copy process
CopyRow = 3
Entries = Excel.WorksheetFunction.CountA(Wks2.Range("CABSDTO PS"))
Cnt2 = Wks3.Range("RoomSTD").Rows.Count
Cnt3 = Entries - Cnt2
For i = 3 To Entries + 1000
N = Wks2.Cells(i, 9).Value
If N < 0 Then Cnt = Cnt + 1
If Cnt Cnt2 Then
MsgBox "You are attempting to import more records than you hav
rows." _
& (Chr(13)) & "Please go to the bottom of the Entry Area an
use the blue button" & (Chr(13)) _
& "to add " & Cnt3 & " additional Rows. Then hit the Impor
button again.", vbOKOnly + vbCritical, "Not enough Rows"
If Response = 1 Or 2 Then
Wks3.Range("STDImportRange").ClearContents
Exit Sub
End If
ElseIf N < 0 And Cnt <= Cnt2 Then
With Wks3
.Unprotect ("geekk")
.Cells(CopyRow, 1).Value = Wks2.Cells(i, 1).Value 'Room #
.Cells(CopyRow, 7).Value = Wks2.Cells(i, 4).Value 'LF Cabs
.Cells(CopyRow, 8).Value = Wks2.Cells(i, 9).Value 'to
depth
.Cells(CopyRow, 13).Value = Wks2.Cells(i, 10).Value 'to
SF
.Cells(CopyRow, 14).Value = Wks2.Cells(i, 3).Value 'ca
type
.Protect ("geekk"), DrawingObjects:=True, Contents:=True
Scenarios:=True
End With
CopyRow = CopyRow + 1

End If
Next i

Wks3.Range("C3").Activate
Application.ScreenUpdating = True
End If
If Msg = 7 Then
Exit Sub

End If
End Su

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=47184

Getting a count of formula results only.
 

Entries = Excel.WorksheetFunction.CountIF(Wks2.Range("CABSDT OPS"),"<0")

--
Regards,
Tom Ogilvy

"Casey" wrote in
message ...

Hi Everybody,
I've googled for an hour and must not be coming up with a meaningful
search phrase. Lots of stuff on counts of all kinds but not what I'm
looking for. Please help.
I use the following bit of code in a lot of different Subroutines to
count the number of entries in a range.

Entries = Excel.WorksheetFunction.CountA(Wks2.Range("CABSDTO PS"))

But now I find the need to obtain the a count from a range that
contains formulas, so my count value ends up being the entire range (#
of cells in range) because the COUNTA function treats the formula in
the cell as something to count.
What I need is a count of the cells in this range where the formula has
calculated a value other than 0. Thanks in advance for any help.

Here is my current Code

Private Sub ImportCAtoSTD_Click()
Dim Wks2 As Worksheet
Dim Wks3 As Worksheet
Dim CopyRow As Long
Dim Entries As Long
Dim i As Long
Dim Cnt As Integer
Dim Cnt2 As Long
Dim Cnt3 As Long
Dim Msg As Integer
Dim Response As Integer
Dim N As Double

Msg = MsgBox("Is the Cabinet Area Takeoff complete and" & (Chr(13)) &
_
"cabinets with tops are indicated as such by" & (Chr(13)) & _
"a dimension in the Cabinet Database?", vbYesNo + vbQuestion, "Import
Room # & Top SF Cabinets")
If Msg = 6 Then
Application.ScreenUpdating = False

Set Wks2 = Worksheets("Cabinet Areas")
Set Wks3 = Worksheets("Slabs-Tops-Decks")
'Clear the worksheet prior to importing new information
Wks3.Range("STDImportRange").ClearContents
Cnt = 0
Cnt2 = 0
Cnt3 = 0
'Set CopyRow to numerical value of first row to start copy process
CopyRow = 3
Entries = Excel.WorksheetFunction.CountA(Wks2.Range("CABSDTO PS"))
Cnt2 = Wks3.Range("RoomSTD").Rows.Count
Cnt3 = Entries - Cnt2
For i = 3 To Entries + 1000
N = Wks2.Cells(i, 9).Value
If N < 0 Then Cnt = Cnt + 1
If Cnt Cnt2 Then
MsgBox "You are attempting to import more records than you have
rows." _
& (Chr(13)) & "Please go to the bottom of the Entry Area and
use the blue button" & (Chr(13)) _
& "to add " & Cnt3 & " additional Rows. Then hit the Import
button again.", vbOKOnly + vbCritical, "Not enough Rows"
If Response = 1 Or 2 Then
Wks3.Range("STDImportRange").ClearContents
Exit Sub
End If
ElseIf N < 0 And Cnt <= Cnt2 Then
With Wks3
Unprotect ("geekk")
Cells(CopyRow, 1).Value = Wks2.Cells(i, 1).Value 'Room #
Cells(CopyRow, 7).Value = Wks2.Cells(i, 4).Value 'LF Cabs
Cells(CopyRow, 8).Value = Wks2.Cells(i, 9).Value 'top
depth
Cells(CopyRow, 13).Value = Wks2.Cells(i, 10).Value 'top
SF
Cells(CopyRow, 14).Value = Wks2.Cells(i, 3).Value 'cab
type
Protect ("geekk"), DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
CopyRow = CopyRow + 1

End If
Next i

Wks3.Range("C3").Activate
Application.ScreenUpdating = True
End If
If Msg = 7 Then
Exit Sub

End If
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=471842

Getting a count of formula results only.
 

Tom,
Worked perfectly as all your help seems to. I keep hoping that this VB
stuff will click with me. Here I am using a worksheet function, famila
ground, and I overlook the obvious choices. I hesitate to pos
sometimes, cause my code looks like, I know sorta, what I'm doing, bu
it's just inelegant, cobbled together bits from here and there plus
lot of google searches. Thanks so much for the help Tom

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=47184