count the number of cells if condition satisfy

Hello Everyone,

currently I am developing a code to count the number of cells if value is same as what i am looking for.

EX; cell values are as follows

TGN
a1 3001
a2 3001
a3 3001
a4 3001
a5 3001
a6 2000
a7 2000
a8 2000
a9 2000
a10 2000

I want to count the number of cells where TGN = 3001 and number of data changes with time to time as per the time of generating the report.

finally i want to count the number of cell which contains TGN = 3001 and assign final call position to a to a variable.

I did something like bellow but it didn't work.

Dim tgn As Range
Set tgn = Worksheets("sheet1").Range("a2")


Dim newCell As Range '<== variable used for final cell position
Set newCell = Worksheets("sheet1").Range("a2")


Dim tgn1 As Integer
tgn1 = 3001

Dim a As Integer '<== variable used for final count
a = 0


Do While (tgn.Value = tgn1)

tgn = Range("newCell").End(xlDown)
newCell = Range("newCell").Offset(1, 0)
a = a + 1

Loop

please help me to develop code for this.

thanks

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Hi,

here is the full code which i wrote to generate two line graphs using Column B, C and D which are stored in the range variable x, y, Z.

but the length of those variables changes with the time i get the report ( graph should only be generated when the column A = 3001, for others it is a different graph which i have not included yet)

work sheet is attached herewith

Please Login or Register  to view this content.

Thanks