ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¤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

¥Ø«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)

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD