[µo°Ý] ÅܼƳ]©w

Scott090

½Ð§â°}¦C¤¤ªº¹ïÀ³¦ì¸m­È³]µ¹ÅܼơAVBA¦p¦ó°µ¡AÁÂÁÂ

1. Option Base 1
   
2. Option Explicit
   
3. 
   
4. Sub Test()
   
5. 
   
6.     '©w¸qÅܼÆ
   
7.     Dim Qref%, kWref%, COPref%, Qev_ref%, Qcd_ref%, Tchws%, Tchwr%, Tcws%, Tcwr%
   
8.     Dim a0%, a1%, a2%, a3%, a4%, a5%
   
9.     Dim ar
   
10.    
    
11.     'ÅܼƦWºÙ°}¦C,   »¡©ú: ¦WºÙ¦b°}¦C¤¤ªº¦ì¸m¬O·|Åܪº
    
12.     ar = Array("Qref", "kWref", "COPref", "Qev_ref", "Qcd_ref", "Tchws", "Tchwr", "Tcws", "Tcwr", _
    
13.                 "a0", "a1", "a2", "a3", "a4", "a5")
    
14.    
    
15.     '§âÅܼƦWºÙ¦b°}¦C¤¤ªº¦ì¸m³]µ¹ÅܼÆ
    
16.    
    
17.     '(¦p¦ó¼gVBA¥i¥H±o¨ì´Á±æµ²ªG)'?
    
18.     'Qref = 1
    
19.     'kWref = 2
    
20.     'COPref = 3
    
21.     'Qev_ref = 4
    
22.     'Qcd_ref = 5
    
23.    
    
24.     '   .
    
25.     '   .
    
26.     '   .
    
27.     'a5 = 15
    
28.    
    
29.    
    
30. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 1# Scott090
³o¼Ë?
Qref = Application.Match("Qref", ar, 0)
kWref = Application.Match("kWref", ar, 0)

Scott090

¦^´_ 2# stillfish00


    ÁÂÁ¤j¤jªº¸Ñ´b¡Aµª®×¥¿¬O©Ò­nªº¡A«D±`·P®¦

¦A½Ð±Ð¡A
°²¦p¨Æ¥ý¨SÅܼƳ]©w¡A¬O§_¯à±q¦WºÙ°}¦C¤¤¤ÀÂ÷¥X ¨Ï¦¨¦U­ÓÅܼƨýá¤_¦b°}¦C¤¤ªº¦ì¸m­È?

stillfish00

¦^´_ 3# Scott090
¨S¿ìªk§a , °£«D¥þ¥Îcode¼gcode?
¬O»¡§A³o¼ËÅܼƪº¦WºÙ©ñ¦bArray¦³Ô£¦n³B¶Ü¡A©_©Çªº¥Îªk¡C

Scott090

¦^´_ 4# stillfish00

«Dµ²ºc¤Æ©Ê¸ê®ÆªíÄæ¦ì¦h¡A°µ­pºâªºµ{¦¡©ö¥¢¥h¥iŪ©Ê¦Ó²V¶Ã¡F
¨Ò¦p:
       AR(i,j) ªº j ¥NªíÄæ¦ì¡AÄæ¦ì¦h¤F ¡Aj ¥u¬O¤@­Ó¼Æ¦r·§©À¡A¨S¦³¦Wµü·N¸q¡F·íµM¡A¼Æ¦r¥Î¨Ó°µ°j°é«Ü­«­n
       Cells(i,"B").offset(,j) ¤@¼Ëªº·N«ä

°²¦p¯à¥ÎÄæ¦ì¦WºÙ·íÅÜ¼Æ j¡A «h¬O¡G
     AR(i,Äæ¦ì¦WºÙ)
     cells(i,"B").offset(,Äæ¦ì¦WºÙ)

¥H¤W½Ð±Ð ¤j¤j  ¦³¨S¦³¨ä¥Lªº¸Ñªk

ÁÂÁÂ

ML089

¦^´_ 1# Scott090

1. Option Base 1
   
2. Option Explicit
   
3. 
   
4. Sub Test1()
   
5. 
   
6. '©w¸qÅܼÆ
   
7.     Dim Qref%, kWref%, COPref%, Qev_ref%, Qcd_ref%, Tchws%, Tchwr%, Tcws%, Tcwr%
   
8.     Dim a0%, a1%, a2%, a3%, a4%, a5%
   
9.     Dim ar, x
   
10.     Dim D As Object
    
11.     Set D = CreateObject("Scripting.Dictionary")
    
12. 
    
13.     'ÅܼƦWºÙ°}¦C,   »¡©ú: ¦WºÙ¦b°}¦C¤¤ªº¦ì¸m¬O·|Åܪº
    
14.     ar = Array("Qref", "kWref", "COPref", "Qev_ref", "Qcd_ref", "Tchws", "Tchwr", "Tcws", "Tcwr") ', "a0", "a1", "a2", "a3", "a4", "a5")
    
15. 
    
16.     '§âÅܼƦWºÙ¦b°}¦C¤¤ªº¦ì¸m³]µ¹ÅܼÆ
    
17. 
    
18.     '(¦p¦ó¼gVBA¥i¥H±o¨ì´Á±æµ²ªG)'?
    
19.     'Qref = 1
    
20.     'kWref = 2
    
21.     'COPref = 3
    
22.     'Qev_ref = 4
    
23.     'Qcd_ref = 5
    
24. 
    
25.     '   .
    
26.     '   .
    
27.     '   .
    
28.     'a5 = 15
    
29.     For Each x In ar
    
30.         D(x) = Application.Match(x, ar, 0)
    
31.     Next
    
32. 
    
33.     [E1:F1].Resize(D.Count) = Application.Transpose(Array(D.keys, D.items))
    
34.    
    
35.     For Each x In D.keys
    
36.         Debug.Print x, D(x)
    
37.     Next
    
38. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 5# Scott090
³B²z¸ê®ÆÄæ¦ì¼ÐÃD¡A¦pªG»Ý­n §Ú¤]¬O±ÄÃþ¦üMLª©¥Dªº°µªk
«Ø¥ß¤@­Ó¦r¨å¨Ó¹ïÀ³ : ¦WºÙ -> Ä渹
³o¼Ë§A´N¯à¹³³o¼Ë¥h¨Ï¥Î : Cells(i,"B").offset(,D("Qref"))
¤£¥Î«Å§i Qref ÅܼÆ

·íµM¡A³q±`¸ê®ÆÄæ¦ì³£¬O©T©w¦ì¸m®É¡Aª½±µ¥ÎConst©w±`¼Æ´N¦n¡C

Scott090

¦^´_ 6# ML089


    ÁÂÁ¤j¤jªº¦^À³
©êºp¡A§Ú¨S§â°ÝÃD´y­z²M·¡

°ÝÃDªº¥Ø¼Ð¬O­n ½á­È µ¹ ÅܼơA¤£¥u¬O¦r¦ê¦b°}¦C¤¤ªº¦ì¸m
¨Ò¦p¡G
          Qref =1¡A¦]¬°¦r¦ê "Qref" ¦b °}¦Cªº¦ì¸m ¬O 1
          Qev_ref = 4, ¦]¬°¦r¦ê "Qev_ref" ¦b°}¦Cªº¦ì¸m¬O 4

Scott090

¦^´_ 7# stillfish00


¤§©Ò¥Hµo·Q­n¥H°ÊºAªº¤è¦¡³B²z¡A
    ´N¬O¦]¬°Äæ¦ì«Ü¦h¥B¦ì¸m·|ÅÜ¡Fª½±µ¥Î Const ·|©Ô±o«Üªø¡A¸ê®Æ¦ì¸mÅܰʮɤ]¤£©ö§ó§ï

´£Ä³¥Î ML ª©¥Dªº¦r¨åªk D("Qref") À³¥i¦æ

´ú¸Õ«á¦A³ø§i

ÁÂÁÂ

GBKEE

¦^´_ 9# Scott090
¸Õ¸Õ¬Ý

1. Option Base 1
   
2. Option Explicit
   
3. Sub Test()
   
4.     '©w¸qÅܼÆ
   
5.     '  Dim Qref%, kWref%, COPref%, Qev_ref%, Qcd_ref%, Tchws%, Tchwr%, Tcws%, Tcwr%
   
6.     '  Dim a0%, a1%, a2%, a3%, a4%, a5%
   
7.     Dim ar, i As Integer
   
8.    
   
9.     'ÅܼƦWºÙ°}¦C,   »¡©ú: ¦WºÙ¦b°}¦C¤¤ªº¦ì¸m¬O·|Åܪº
   
10.     ar = Array("Qref", "kWref", "COPref", "Qev_ref", "Qcd_ref", "Tchws", "Tchwr", "Tcws", "Tcwr", _
    
11.                 "_a0", "_a1", "_a2", "_a3", "_a4", "_a5")  '
    
12.     For i = 1 To UBound(ar)
    
13.         ActiveSheet.Names.Add ar(i), i     '¦b§@¥Î¤¤¤u§@ªí¤W©w¸q¦WºÙ,µ¹­È
    
14.     Next
    
15.     MsgBox Cells(i, "B").Offset(, [Qref]).Address
    
16. End Sub

½Æ»s¥N½X