[µo°Ý] Excel VBA¥H©w¸q¹LªºWorkSheet·f°tExcel¤@¯ë¨ç¼Æ VBA, Workbook, Worksheet, Excel

peohan

¤p§Ì¥¿¶}©lExcel VBAªº¼g§@¹Lµ{

¸ÕµÛ±Nsht1«Å§i¦¨Worksheet (Dim sht1 as Worksheet)
¦A¥HÅܼÆSheet1¥h»PExcel¨ç¼Æ°µ·f°t

¥Ø«e¤wª¾¥i¥H§Q¥ÎRange ("A1").Value ¤Î Range ("A1").Formula¥h¿é¤J¤½¦¡
¦ý¦ü¥G³£¨S¿ìªk¥HSheet1¥hª½±µ°µ¹B¥Î

¨Ò¦p¡Gsht1.Name = "¤u§@ªí1"
  ·í­nªí¥Ü =countif(¤u§@ªí1!C:C,A1)®É
  ¬O§_¯à°÷Âà´«¦¨ ±N¤u§@ªí1ª½±µ¥Îsh1¥h°µ¥N´À¡A²¤ÆVBAªº°T®§

  ²{¦b§Úªº¶i«×¥u·|§â¥Î¦r¦êªº¤è¦¡¥hªí¥Ü = "=countif(" & sh1.Name & "!C:C,A1)"
  ´Á±æ¥i¥H²¤Æ¦¨ = "=countif(sh1!C:C,A1)" ³oÃþ¤ñ¸û©öÁAªº¤½¦¡

¥t¥~·í¦³¸ó¬¡­¶Ã¯®É¡A¬O§_¤]¥i¥HÂǥѫŧiWorkbook
  Dim wb1 as Workbook
  ¥h±Nwb1.sh1¥h¹B¥Î¦bExcel¤½¦¡¤¤¡I¡H

peo.han

¥Ø«e¥Î¨ì¨â­Ó¼gªk
²Ä¤@­Ó¬O¥ÎVBAªºApplication.Function¡A¦ý®Ä²v¸û®t
Dim x1Row As Integer
For x1Row = 11 To sh1.Range("D65536").End(xlUp).Row
    Cells(x1Row, 5).Value = Application.CountIf(sh1.Range("C:C"), Cells(x1Row, 4))
Next x1Row

²Ä¤G­Ó«h¬O¥Î¦r¦êªº¤è¦¡¡A¼gªk¸û½ÆÂø¡A¦ý®Ä²v°ª¤F10­¿¥H¤W
»Ý¯d¤U '[Workbook]Worksheet!'ªº ' [ ] ! 'ªº²Å¸¹¤~¤£·|¥X¿ù
Cells( 11, 5).Formula = "=IF(D11="""","""",COUNTIF('[" & wb1.Name & "]" & sh1.Name & "'!$CC,D11))"
Range("E11").AutoFill Destination:=Range("E11:E" & [E65536].End(1).Row)

GBKEE

¦^´_ 2# peo.han
¸Õ¸Õ¬Ý
¬O³o¼Ë¶Ü?

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Sh1 As Worksheet, Rd As String, x1Row As Integer
   
4.     Set Sh1 = Sheet1
   
5.     With Sheet2
   
6.         Rd = .Range("C:C").Address(, , , 1, 1)
   
7.     End With
   
8.     With Sh1
   
9.         x1Row = .Range("D65536").End(xlUp).Row
   
10.         With .Range("e11:e" & x1Row)
    
11.             .Cells = "=IF(D11="""","""",COUNTIF(" & Rd & ",D11))"
    
12.             '.Value = .Value '¤½¦¡Âର¼Æ­È
    
13.             .Select
    
14.         End With
    
15.     End With
    
16. End Sub

½Æ»s¥N½X