#### gaurav.inani

##### New Member

- Joined
- Jul 17, 2010

- Messages
- 8

I have created a new excel macro enabled workbook in Excel 2007.

I have entered the following in Sheet1

A1 = Name

A2 = Mike

A3 = Jullian

A4 = Peter

A5 = Rusell

and in Column B, as

B1 = Say

B2 = Hi

B3 = Hello

B4 = Hi

B5 = Hi

in my sheet2 i have

A1=Hi

A2=Hello

and then I have activated sheet2 and trying to enter formula through VBA

Sub test()

dim r as range

Sheet(ActiveSheet.Index - 1).Select

Set r = Columns("B:B")

Sheet(ActiveSheet.Index + 1).Select

Range("B1").Select

Activecell.Formula = "=Countif(r,A1)"

End sub

(This I am doing because the user does not know which sheet is activated and hence in Sheet2 the forumla have to return the value 3)

But it is generating error, can anyone help me on this topic.