How can fill down the function in excel vba

[Sukhdev Singh]

Hi all,
I am using countif formula in excel vba, but not able to implement desire cell i.e D2: D201 in report sheet and data is comming from data sheet.

waiting for your kind reply.

thanks,
Rgds,
Sukhdev

 

[Hui]

I think you want:

Sub CopyDeatils()
Dim lr1 As Integer, lr2 As Integer


Sheet2.Activate
lr2 = Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Activate
lr1 = Range("A" & Rows.Count).End(xlUp).Row

Range("A:F").CurrentRegion.Select

Sheet3.Activate
Range("G2").Formula = "=CountIf(" + Sheet2.Name + "!A2:A" + CStr(lr2) + "," + Sheet3.Name + "!D2)"
Range("G2").AutoFill Destination:=Range("G2:G" + CStr(lr1))


Range("A:F").CurrentRegion.Copy

'Range("G2").Formula = Application.WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet3.Range("D2").Copy)
'Sheet3.Range("G2:G201").PasteSpecial

'Sheet3.Range("G2:G201").Formula = Application.WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet3.Range("D2").Value)

'drag
'Application.Worksheets("Report").Range("D2").Copy Worksheets("Report").Range("D2:D201")

'vlookup
'range("H2").Formula=application.worksheetfunction.vlookup(range("A:B"),range("D5")

Range("A1").PasteSpecial Paste:=xlPasteFormulas

Sheet3.Columns.AutoFit

'Sheet3.Clear

End Sub

see attached file:

 

[Sukhdev Singh]

Thank you for helping me Mr. Hui. exactly I want the same.

thanks & Rgds,
Sukhdev