[µo°Ý] VBA¥[Á`°ÝÃD

sandra_wang

¸ê®Æ±q²Ä¤EÄæ¶}©l,¤@ª½¨ì²ÄnÄæ¡A¦pªG²Ä¤@¦Cªº¼ÐÃD(#_1, #_2...#_n)¤£¬OªÅ¥Õ¡A
´N§âCells(2,9)....Cells(2,n)Äæ¦ìªº¼Æ¦r¥[Á`¡A§â¥[Á`ªºµ²ªG¶ñ¨ìCells(2,2)
´N§âCells(3,9)....Cells(3,n)Äæ¦ìªº¼Æ¦r¥[Á`¡A§â¥[Á`ªºµ²ªG¶ñ¨ìCells(3,2)
´N§âCells(4,9)....Cells(4,n)Äæ¦ìªº¼Æ¦r¥[Á`¡A§â¥[Á`ªºµ²ªG¶ñ¨ìCells(4,2)
½Ð¤j®aÀ°§Ú¸Ñµª¤@¤U¦n¶Ü? ÁÂÁÂ!

GBKEE

¦^´_ 1# sandra_wang

1. Sub Ex()
   
2.     Dim Rng As Range, s1, s2, i
   
3.     With ActiveSheet
   
4.         Set Rng = .Range("i1", .Range("iv1").End(xlToLeft))
   
5.         s1 = Rng.Address(, , xlR1C1)
   
6.         For i = 2 To .Range("i" & Rows.Count).End(xlUp).Row
   
7.             s2 = Rng.Offset(i - 1).Address(, , xlR1C1)
   
8.             .Cells(i, "b").FormulaArray = "=SUM((" & s1 & "<>"""")*(" & s2 & "))"
   
9.         Next
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# sandra_wang

1. Sub nn()
   
2. For i = 2 To 4
   
3.   Set a = Cells(1, 9)
   
4.    Cells(i, 2) = Application.Sum(Range(a.Offset(i - 1), a.Offset(i - 1).End(xlToRight)))
   
5. Next
   
6. End Sub

½Æ»s¥N½X

FAlonso

1. Sub abc()
   
2. Dim mycolumn, i
   
3. mycolumn = Mid(Cells(2, 9).End(xlToRight).Address, 2, 1)
   
4. For i = 2 To 4
   
5. Cells(i, 2) = Application.Sum(Range("I" & i & ":" & mycolumn & i))
   
6. Next i
   
7. End Sub

½Æ»s¥N½X

­º¦¸¦b³o¸Ì´£¨Ñcode,¤£ª¾¼g±o¦p¦ó?

Hsieh

¦^´_ 4# FAlonso

³o·|¦³¤@­Ó¯ÊÂI
Äæ¦W­^¤å¬O2­Ó¦r®É´N¥X¿ù

FAlonso

1. Sub abc()
   
2. Dim mycolumn As String, mystring As String, myarray, i
   
3. myarray = Split(Cells(2, 9).End(xlToRight).Address, "$", -1)
   
4. mycolumn = myarray(1)
   
5. 
   
6. For i = 2 To 4
   
7. Cells(i, 2) = Application.Sum(Range("I" & i & ":" & mycolumn & i))
   
8. Next i
   
9. End Sub

½Æ»s¥N½X

¦hÁÂH¤j,§ï¨}¤F³o¤@ÂI,§Q¥Îsplit¤èªk®M¨úcolumn¦C

sandra_wang

·PÁ¼ӤW¦U¦ìªºÀ°¦£¡A°ÝÃD¤w¸g¸Ñ¨M¤F!

frankjenq

¦^´_ 2# GBKEE

½Ð°ÝGBKEE¤j®v
¤£¦n·N«ä¡I¤p§Ì¤£¤ÓÀ´³o°}¦C¤½¦¡"=SUM((" & s1 & "<>"""")*(" & s2 & "))"ªº¥Î·N¡A¥i¥H©ú¥Õªº¸ÑÄÀ¤@¤U¶Ü¡H
ÁÂÁÂ!

GBKEE

¦^´_ 8# frankjenq
¸`¿ý vbaªº»¡©ú   FormulaArray  ¸Ó¤½¦¡¥²¶·¨Ï¥Î R1C1 °Ñ·Ó¼Ë¦¡¡A¦ÓµLªk¨Ï¥Î A1 °Ñ·Ó¼Ë¦¡
©Ò¥H
s1 = Rng.Address(, , xlR1C1)
s2 = Rng.Offset(i - 1).Address(, , xlR1C1)
¶Ç¦^R1C1 °Ñ·Ó¼Ë¦¡  ±a¤J¤½¦¡¤¤ "=SUM((" & s1 & "<>"""")*(" & s2 & "))"

frankjenq

¦^´_ 9# GBKEE


Sorry!!¥i¯à¬O§Úªí¹Fªº¤£¦n¡I

Ex:[I2]={SUM(($I$1:$O$1<>"")*($I$3:$O$3))}
§ÚªººÃ°Ý¬O·Q°Ý¬°¦ó­n¥Î"(($I$1:$O$1<>"")*($I$3:$O$3)"ªº¤è¦¡¨Ó§@¹Bºâ¡A­è¤~¸Õ¤F¤@¤U¤~ª¾¹D·í¸Órowªºtitle¤º®e<>""®É¬°"TRUE"¬O¥i¥H®³¨Ó·í§@¼Æ­È1¨Ï¥Î¡A
§Y TRUE*1=1
    FALSE*1=0
¨Ì°}¦C¤½¦¡»¼¦^¨Ó§@¥[Á`¡÷³o©Û¯u¬O°ª§r!

¨ü±Ð¤F
Thanks!!