gaurav.inani
New Member
- Joined
- Jul 17, 2010
- Messages
- 8
Hi,
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.
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.