Displaying a VBA result in a different worksheet

S

Simon Clark

Hi...
I am working with a spreadsheet that has a series of column which all
need to be counted.

I have managed to count the array of data and display it in any given
cell, but what i cannot do, is display it in a cell within another
worksheet.

Below is the code i am using... please advise...
-----------------------------------------------------
Sub Countif_InVBA()
Dim x As Integer
Dim removetxt As Integer
removetxt = WorksheetFunction.CountIf(ActiveSheet.Range("H1:H15"),
"Removal")
x = WorksheetFunction.CountIf(ActiveSheet.Range("H1:H15"), "New
Order")
Range("H17") = x
Range("H18") = removetxt

End Sub
-----------------------------------------------------

Below is a sample of the data i am using..
-----------------------------------------------------
Other
New Order with Reference
Removal
Removal
Order Change
New Order
New Order with Reference
Other
New Order
New Order
New Order
Removal
Other
New Order
Other
-----------------------------------------------------

So, with the data, i want a cell on a different worksheet to display
that there are '6' New Orders.

Any help would be great...

Cheers...

Simon
 
F

Frank Kabel

Hi
try something like the following:

Sub Countif_InVBA()
Dim source_wks as worksheet
Dim target_wks as worksheet
Dim x As Integer
Dim removetxt As Integer
set source_wks = activesheet
set target_wks = worksheets("Other_sheet")
removetxt = WorksheetFunction.CountIf(source_wks.Range
("H1:H15"),
"Removal")
x = WorksheetFunction.CountIf(source_wks.Range
("H1:H15"), "New
Order")
target_wks.Range("H17") = x
target_wks.Range("H18") = removetxt

End Sub
 
Top