My code need to go on a diet
On 10/01/2012 8:26 AM, GS wrote:
Vacuum Sealed was thinking very hard :
On 6/01/2012 10:33 PM, GS wrote:
Try...
Sub GetCount()
' Assumes this code runs on ActiveSheet
Dim Tenders As Range
Dim v As Variant
Const sSearchSource As String =
"G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC, AD"
Const lSourceRow& = 12
Set Tenders = Sheets("TMS DATA").Range("N6:N200")
For Each v In Split(sSearchSource, ",")
Range(v & lSourceRow + 1) =
Application.WorksheetFunction.CountIf(Tenders, Range(v & lSourceRow))
Next 'v
End Sub
If you want to use cell formula instead of VBA:
Select G13
Open Define Name dialog
In the name box type
'<sheetname'!LastCell
..where you need to replace <sheetname with the actual sheet name.
(Make sure you wrap the sheetname in apostrophes if it has any
characters other than letters, numbers, or the underscore)
In the RefersTo box type
=g12
..and press the Enter key
Select G13:AD13
Type =countif('TMS DATA'!N6:N200,lastcell)
Keyboard Ctrl+Enter to put the formula in all cells at once
HTH
Hi Garry
appreciate the reply
This code halts on "Source" with an error ( Expected: As Or = )
I actually went with Don's code on this occasion.
Thx heaps though, I always look forward to your contributions.
Regards
Mick
Not sure why you're getting the error because I tested the code with
sample data before posted and got the results you wanted without error.
Maybe the copy/paste broke the lines because there is no single word
"Source" in my code.
Actually Garry
Looking at the code again, you are right, it does appear I did clean up
the paste incorrectly.
I will have another flirt with it tomorrow when I superglue my ass to
the work chair...lol...
Thx again
Mick.
|