Counting the number of values in a dynamic range of next sheet

[nynamyna]

Hello everybody

I have a macro to count the number of values in a particular column of a sheet and to display it (sheet name is also fixed in this macro which should be changed) This is the forumula I have

=COUNTIF(Comparisons117!G2:G15238,"Hit")

The above formula counts the number of times the word "Hit" repeats in a specific range of cells in a sheet named Comparisons117.

But what I want is to count the number of times the word "Hit" is repeated in a particular coulum and the range should be dynamic. The sheet from which it counts should be the sheet next to the sheet where I am displaying this value

Thanks
Bharath <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig --><!-- edit note -->

 

[MrKowz]

Is there consistency as to where the formula is placed, and what column it looks at to count from sheet to sheet?

 

[nynamyna]

Yes the cell where I place the formula is consistent. The column In which I look for the values is also consistent. The only problem is to look at the sheet next to the sheet where i have this formula and the range is dynamic.

 

[MrKowz]

What cell is this entered on each sheet, and what column are you counting?

 

[nynamyna]

I enter it on cell J2 and I am counting column G in the next sheet. I will have different values in coulmn G. "Hit" "Miss" etc...First I will count the number of Hit and then in a seperate cell I will display the number of Miss etc...

I have only two sheets. In one sheet i will display the counts and in the next sheet I have the data which I should count.

 

[MrKowz]

Something like this what you're needing?

Sheets(1).Range("J2").Formula = "=COUNTIF('" & Sheets(2).Name & "'!G:G,""Hit"")"

Since you have only two sheets, I don't understand why you need a macro to do this.

 

[nynamyna]

I will always delete and replace the second sheet.....

 

[nynamyna]

Should I directly enter this in j2 ?

 

[MrKowz]

You'll have to do this from VBA (a macro).

As far as I'm aware, the only way to reference the "next sheet" is to enter the formula from VBA.

 

[nynamyna]

Thank you very much..that worked.....I have the following macro which works for the sheet that is open. I need to run this macro for the second sheet in my file when I click a button in first sheet..I have the button created but I am not able to run this from my first sheet.....can you please see this and tell me how to change it

Sub FormatSetup()
'
' FormatSetup Macro
'
'
Range("A1:AB1").Select
Selection.AutoFilter
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 9.57
Columns("K:K").ColumnWidth = 9.57
Columns("P:P").ColumnWidth = 9.43
Columns("R:R").ColumnWidth = 9.71
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Columns("Q:Q").ColumnWidth = 17
Columns("Q:Q").ColumnWidth = 27
Columns("S:S").ColumnWidth = 16.29
Columns("S:S").ColumnWidth = 18.57
Columns("U:U").ColumnWidth = 10.86
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
Columns("X:X").ColumnWidth = 10.71
Columns("Y:Y").ColumnWidth = 9.14
Columns("Z:Z").ColumnWidth = 10.14
Columns("AB:AB").ColumnWidth = 9.43
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=8, Criteria1:="1"
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=12, Criteria1:="1"
Range("A1:AB15238").Select
Range("A517").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=8, Criteria1:="0"
Range("A1:AB15238").Select
Range("A994").Activate
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 16764159
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("S994:S1172").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=8, Criteria1:="1"
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=12, Criteria1:="0"
Range("A1:AB15238").Select
Range("A1002").Activate
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1:AB1").Select
Range("AB1").Activate
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=8
ActiveSheet.Range("$A$1:$AB$15238").AutoFilter Field:=12
ActiveWindow.ScrollRow = 998
ActiveWindow.ScrollRow = 967
ActiveWindow.ScrollRow = 904
ActiveWindow.ScrollRow = 780
ActiveWindow.ScrollRow = 624
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 2
End Sub