View Full Version : Why is the sub or Function not defined for the following code
moiishchiu
05-19-2016, 01:28 AM
Is it the application.worksheet.function that's causing the issue ? :hi:
Sub YesAmountFile()
Dim TotalYesNum As Integer
Dim TotalYes As String
Dim counter2 As Long
Dim lengthoflist As Long
lengthoflist = Sheets("RawData").UsedRange.Rows.Count
For counter2 = 2 To lengthoflist
If Cells(counter2, 7) = "Yes" Then
TotalYesNum = Application.WorksheetFunction.CountIf(Cell("counter2: 7"), "Yes")
End If
Range("t10") = TotalYesNum
Next counter2
End Sub
Aflatoon
05-19-2016, 01:33 AM
No, this is the problem:
Cell("counter2: 7")
It should be:
Cells(counter2, 7)
as earlier in the code.
However, it doesn't really make any sense to use Countif on one cell - and you already know it contains the value you want.
moiishchiu
05-19-2016, 01:44 AM
No, this is the problem:
Cell("counter2: 7")
It should be:
Cells(counter2, 7)
as earlier in the code.
However, it doesn't really make any sense to use Countif on one cell - and you already know it contains the value you want.
Counter2 is the row, I defined counter2 as a loop where it will end once it goes to the last used row. lengthoflist = Sheets("RawData").UsedRange.Rows.
As I want it to continually loop and add up all the "yes" in that specific column which is (counter2, 7)
Aflatoon
05-19-2016, 03:24 AM
In that case all you really need is:
Sub YesAmountFile()
Range("t10") = Application.WorksheetFunction.CountIf(Sheets("RawData").Range("G:G"), "Yes")
End Sub
Paul_Hossler
05-19-2016, 06:12 AM
As I want it to continually loop and add up all the "yes" in that specific column which is (counter2, 7)
Aflatoon's technique is better, but in terms of the original logic ..
Sub YesAmountFile()
Dim TotalYesNum As Integer
Dim TotalYes As String
Dim counter2 As Long
Dim lengthoflist As Long
lengthoflist = Sheets("RawData").UsedRange.Rows.Count
For counter2 = 2 To lengthoflist
If Cells(counter2, 7) = "Yes" Then
TotalYesNum = TotalYesNum + 1 '<<----------------------------- Not CountIf()
End If
Range("t10") = TotalYesNum
Next counter2
End Sub
Aflatoon
05-19-2016, 07:52 AM
Small caveat: if you do that, you should also use:
Dim TotalYesNum As Long
rather than Integer. ;)
Always declare Row and Column counting Variables as longs
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.