[µo°Ý] ¦U¦ì¥ý¶i¤j®a¦n¡A¦³­Ó¤p°ÝÃD·Q°Ý

wxad0285

¦U¦ì¥ý¶i±z­Ì¦n¡G

¤p§Ì³Ìªñ¦Û¾Ç¤Fexcel¤¤ªºvba¨Ó¼g¤@¨Çµ{¦¡¡A³Ìªñ¹J¨ì´X­Ó°ÝÃD·Q½Ð±Ð
¥ýªþ¤W§Ú¼gªºµ{¦¡½X

Sub firstrecord()
    '±NÀx¦s®æA3½s¸¹¬°1'
    Worksheets("salary").Range("A3").Value = "1"
   
    '±NÀ³¤äª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ'
    Worksheets("salary").Range("B3").Value = UserForm1.Textbox1.Text
    Worksheets("salary").Range("C3").Value = UserForm1.TextBox2.Text
    Worksheets("salary").Range("D3").Value = UserForm1.TextBox3.Text
    Worksheets("salary").Range("E3").Value = UserForm1.TextBox4.Text
    Worksheets("salary").Range("F3").Value = UserForm1.TextBox5.Text
    Worksheets("salary").Range("G3").Value = UserForm1.TextBox6.Text
    Worksheets("salary").Range("H3").Value = UserForm1.TextBox7.Text
    Worksheets("salary").Range("I3").Value = UserForm1.TextBox8.Text
    Worksheets("salary").Range("J3").Value = UserForm1.TextBox9.Text
    Worksheets("salary").Range("K3").Value = UserForm1.TextBox10.Text
   
    '±NÀ³¦©ª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ'
    Worksheets("salary").Range("M3").Value = UserForm1.TextBox11.Text
    Worksheets("salary").Range("N3").Value = UserForm1.TextBox12.Text
    Worksheets("salary").Range("O3").Value = UserForm1.TextBox13.Text
    Worksheets("salary").Range("P3").Value = UserForm1.TextBox14.Text
    Worksheets("salary").Range("Q3").Value = UserForm1.TextBox15.Text
    Worksheets("salary").Range("R3").Value = UserForm1.TextBox16.Text
    Worksheets("salary").Range("S3").Value = UserForm1.TextBox17.Text
    Worksheets("salary").Range("T3").Value = UserForm1.TextBox18.Text
    Worksheets("salary").Range("U3").Value = UserForm1.TextBox19.Text
    Worksheets("salary").Range("V3").Value = UserForm1.TextBox20.Text
    Worksheets("salary").Range("W3").Value = UserForm1.TextBox21.Text
    Worksheets("salary").Range("X3").Value = UserForm1.TextBox22.Text
   
   
    '±NÀ³¤äª÷ÃB¬Û¥['
    Dim a, b, c, d, e, f, g, h, i, j, k
   
   
    a = Worksheets("salary").Range("D3").Value
    b = Worksheets("salary").Range("E3").Value
    c = Worksheets("salary").Range("F3").Value
    d = Worksheets("salary").Range("G3").Value
    e = Worksheets("salary").Range("H3").Value
    f = Worksheets("salary").Range("I3").Value
    g = Worksheets("salary").Range("J3").Value
    h = Worksheets("salary").Range("K3").Value
    i = b * c
    j = d * e
    k = f * g
   
   
    'Åã¥Ü¥XÀ³¤äª÷ÃB¤p­p'
    UserForm1.Label19 = a + i + j + k
    UserForm1.Label19 = Worksheets("salary").Range("L3").Value
   
    'Åã¥Ü¦U¶µ¥Ø¤p­p'
   
    UserForm1.Label35 = i
    UserForm1.Label34 = j
    UserForm1.Label13 = k
   
    '±NÀ³¦©ª÷ÃB¬Û¥['
    Dim aa, bb, cc, dd, ee, ff, gg, hh, ii As Integer
    aa = UserForm1.TextBox11.Text
    bb = UserForm1.TextBox12.Text
    cc = UserForm1.TextBox13.Text
    dd = UserForm1.TextBox14.Text
    ee = UserForm1.TextBox15.Text
    ff = UserForm1.TextBox22.Text
    gg = (UserForm1.TextBox16.Text) * (UserForm1.TextBox17.Text)
    hh = (UserForm1.TextBox18.Text) * (UserForm1.TextBox19.Text)
    ii = (UserForm1.TextBox20.Text) * (UserForm1.TextBox21.Text)
   
    'Åã¥ÜÀ³¦©ª÷ÃB¤p­p'
    Worksheets("salary").Range("Y3").Value = a1 + b1 + c1 + d1 + e1 + f1 + g1 + h1 + i1
    UserForm1.Label42 = Worksheets("salary").Range("Y3").Value
   
    'Åã¥Ü¹ê¤äª÷ÃB'
    UserForm1.Label16 = (UserForm1.Label19) - (UserForm1.Label42)
   
End Sub

§Úªº°ÝÃD¬O
1.¬°¤°»ò§Ú¥i¥Hª½±µ§Q¥Î¦Û¤v³]­pªºªí³æ¶ñ§¹¸ê®Æ¡A¶¶§Q±N©Ò¥´ªº¸ê®Æ¶ñ¤J¨ìexcel¤¤¡A¦ý¬O¦bvbÀô¹Ò¤¤¡AÀˬdµ{¦¡½X«oÁ`¬O¥X²{"¸ê®Æ«¬ºA¤£¥¿½T"?¬O¦]¬°§Ú«Å§iªºÅܼƦ³°ÝÃD¶Ü?
2.¦]¬°³o­Óºâ¬O§Ú¤gªk·Ò¿ûªº¤è¦¡¡A¨Ì·Ó§ÚªºÅÞ¿è¼g¥X¨Óªº¡A©Ò¥H·Pı¦³ÂIÂø¡A¤£ª¾¹D¬O§_¦³Â²¤Æªº¤è¦¡?

¥t¥~¦pªG¤å³¹±Æª©©Î¬Oµo°Ýªº³W«h¨S¦³¿í¦u¨ì¡AÁÙ¬O¬Ý¤£À´§Ú¼gªºT_T
ÁٷнЦU¦ì§iª¾¡A¨Ã¤£§[«ü±Ð
ÁÂÁÂ

GBKEE

¦^´_ 1# wxad0285
³o a1 , b1 , c1 , d1 , e1 , f1 , g1 , h1 , i1  ªº¼Æ­È¦b­þ¸Ì,
Àˬdµ{¦¡½X«oÁ`¬O¥X²{"¸ê®Æ«¬ºA¤£¥¿½T"? ­þ¤@¦æµ{¦¡½X, ªþÀɤW¨Ó¤~²M·¡¿ù»~¦b­þ¸Ì.

1. Worksheets("salary").Range("Y3").Value = a1 + b1 + c1 + d1 + e1 + f1 + g1 + h1 + i1

½Æ»s¥N½X

wxad0285

¦^´_ 2# GBKEE


    ©êºp¡A§Ú¶Kªº¨º­Ó¦³ÂI¿ù»~
    ªþ¥ó¤º®e¤w¸g§â§Úªºexcelªþ¤W¤F¡A¦Û¤v·d¨ì¦³ÂI©üÀY@_@
    ¦A³Â·Ð¦U¦ì¥ý¶i«üÂI¤@¤U°g¬z
    ÁÂÁÂ~

GBKEE

¦^´_ 3# wxad0285
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub firstrecord()
   
3.     Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer
   
4.     Dim a1 As Integer, b1 As Integer, c1 As Integer, d1 As Integer, e1 As Integer, f1 As Integer, g1 As Integer, h1 As Integer, i1 As Integer
   
5.     Dim Sh As Worksheet, xRow As Integer, i As Integer
   
6.     Set Sh = Sheets("salary")    '³o¸Ì³]¥ß¤u§@ªíÅܼƠ   '*** ¦p¤u§@ªí­n§ï¦WºÙ: ³oùاï¤@¦¸´N¥i¥H  *****
   
7.    xRow = Application.CountA(Sh.Range("a:a"))            '¦³¸ê®ÆÀx¦s®æªº­p¼Æ
   
8.    With Sh
   
9.          .Cells(xRow + 1, "A") = xRow - 1                '½s¸¹: ¬°¦³¸ê®ÆÀx¦s®æªº­p¼Æ-1
   
10.         For i = 1 To 12
    
11.             '±NÀ³¤äª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ' 1-10
    
12.             If i <= 10 Then .Cells(xRow + 1, "A").Offset(, i) = UserForm1.Controls("Textbox" & i)
    
13.             '±NÀ³¦©ª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ' 11-22
    
14.             .Cells(xRow + 1, "M").Offset(, i - 1) = UserForm1.Controls("Textbox" & i + 10)
    
15.         Next
    
16.     End With
    
17.     '±NÀ³¤äª÷ÃB¬Û¥['
    
18.     With UserForm1
    
19.        'Àˬdµ{¦¡½X«oÁ`¬O¥X²{"¸ê®Æ«¬ºA¤£¥¿½T"? ->'·í±±¨î¶µ ¬°ªÅ¥Õ®É ¤£¥i§@¥|«h¹Bºâ
    
20.         a = Val(.TextBox3)                    ' a ¤]¥i¥H = ¤u§@ªíªºÀx¦s®æ ,¤£¥²¥[ Val¨ç¼Æ
    
21.         b = Val(.TextBox4) * Val(.TextBox5)   'Àx¦s®æ¬°ªÅ¥Õ®É¦Û°ÊÂର 0 ¥i§@¥|«h¹Bºâ
    
22.         c = Val(.TextBox6) * Val(.TextBox7)
    
23.         d = Val(.TextBox8) * Val(.TextBox9)
    
24.         e = Val(.TextBox10)
    
25.     'Åã¥Ü¥XÀ³¤äª÷ÃB¤p­p'
    
26.         Sh.Range("L3").Value = a + b + c + d + e
    
27.         .Label19 = Sh.Range("L3")
    
28.     'Åã¥Ü¦U¶µ¥Ø¤p­p'
    
29.         .Label35 = b
    
30.         .Label34 = c
    
31.         .Label13 = d
    
32.     '±NÀ³¦©ª÷ÃB¬Û¥['
    
33.         a1 = Val(.TextBox11)
    
34.         b1 = Val(.TextBox12)
    
35.         c1 = Val(.TextBox13)
    
36.         d1 = Val(.TextBox14)
    
37.         e1 = Val(.TextBox15)
    
38.         f1 = Val(.TextBox22)
    
39.         g1 = Val(.TextBox16) * Val(.TextBox17)
    
40.         h1 = Val(.TextBox18) * Val(.TextBox19)
    
41.         i1 = Val(.TextBox20) * Val(.TextBox21)
    
42.     'Åã¥ÜÀ³¦©ª÷ÃB¤p­p'
    
43.         Sh.Range("Y3").Value = a1 + b1 + c1 + d1 + e1 + f1 + g1 + h1 + i1
    
44.         .Label42 = Sh.Range("Y3")
    
45.     'Åã¥Ü¹ê¤äª÷ÃB'
    
46.         .Label16 = .Label19 - .Label42
    
47.         'Sh.Range("Y3")-Sh.Range("L3")   Àx¦s®æ¬°ªÅ¥Õ®É¦Û°ÊÂର 0
    
48.        End With
    
49. End Sub

½Æ»s¥N½X

wxad0285

¦^´_ 4# GBKEE


   ·PÁª©¥Dªº¼ö¤ß¦^ÂСA¸Õ¹L¤§«á¨S¦³¥X²{¤§«eªº°ÝÃD¤F¡A

¦ý¬O¦³¨Ç»yªk§ÚÁÙ¤£¤Ó²M·¡­n¦b¤°»ò®É­Ô¥Î¡A¥H¤Î¥Lªº·N«ä¡A¥¿¦b¸É±j¸ê®Æ¤¤@_@

¥t¥~·Q°Ý¡A¦pªG§Ú­n¦bªí®æ¤¤¦V¤U·s¼W¸ê®Æ¡A¬O§_­n¦h¤@­Ó¤@­Ó¦V¤U°»´ú³Ì«áÀx¦s®æªºµ{¦¡

Ãþ¦ü¹³³o¼Ëªº
ActiveCell.End(xlDown).Select
        LastRec = ActiveCell.Row

ÁÙ¬O¥i¥H§Q¥Îfor...next...°j°é¤]¯à¸Ñ¨M?

¬O§_½Ð¦U¦ì¯àµ¹ÂI´£¥Ü´N¦n¡A³Ñ¤Uªº§Ú¦A¥h¸Õ¸Õ¬Ý

¥t¥~¶Kµ{¦¡½Xªº®É­Ô ­n¿ï­þ­Ó¿ï¶µ¤~¯à¦³¥ªÃ䪺½s¸¹?¹³ª©¥D¦^ÂЪº¨º¼Ë?¬O¥N½X¶Ü?

ÁÂÁ¦U¦ì

GBKEE

¦^´_ 5# wxad0285
  ¦h¬Ý¬Ý,·|¬Ý¥X¬ªùªº

wxad0285

¦^´_ 6# GBKEE


·PÁª©¥D¤jªº¦^ÂСA­«½Æ·Q¤F·Q¡A¤]¬d¤F¸ê®Æ¡A
¦³¨Ç¦a¤èÁ`ºâ¬O§Ë²M·¡¡A¦ýÁÙ¬O¦³´X­Ó°ÝÃD§Ú·Q¦A½Ð±Ð¤@¤U

1.¦³Ãö©ó°j°éªº³¡¤À
xRow = Application.CountA(Sh.Range("a:a"))            '¦³¸ê®ÆÀx¦s®æªº­p¼Æ
   With Sh
         .Cells(xRow + 1, "A") = xRow - 1
For i = 1 To 12
            '±NÀ³¤äª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ' 1-10
            If i <= 10 Then .Cells(xRow + 1, "A").Offset(, i) = UserForm1.Controls("Textbox" & i)
            '±NÀ³¦©ª÷ÃB¶ñ¤J¡A¨Ã¬Û¹ïÀ³excelªºÀx¦s®æ' 11-22
            .Cells(xRow + 1, "M").Offset(, i - 1) = UserForm1.Controls("Textbox" & i + 10)
  Next
    End With

§Ú¥i¥H±N¥¦¸ÑÄÀ¦¨
"·í i ¤p©óµ¥©ó10¡AÀx¦s®æ·|±q("A & xRow+1)¬°°_©lÂI¡A¦V¥k²¾°Ê i ®æ¡A¨Ã¥B±N userfome1¤¤²Ä i ­Ótextboxªº¤º®e¶ñ¤J "
¦ý¬O¤U¤@¥y±N³o¼ËÅÞ¿è®M¥Î¶i¥h´Nº¡©_©Ç?
©Ò¥H¤£ª¾¹D¬O¤£¬O§Úªº»{ª¾¦³¿ù»~¡A¬O§_¯à½Ð¦U¦ì¤j¤jÀ°§Ú¸ÑÄÀ¤@¤U¡AÁÂÁÂ

2.·í§Ú¦b­pºâ¤p­pªº®É­Ô¡A­ì¥»ªºµ{¦¡½X¦p¤U:
  Sh.Range("L3").Value = a + b + c + d + e
        .Label19 = Sh.Range("L3")

¦ý³o¼Ë¤£ºÞ¿é¤J´X­Ó¤Hªº¸ê®Æ¡A¤p­p¥u·|¥X²{¦bL3³o­Ó
¦]¦¹§Ú§@¤F¥H¤U­×§ï:
sh.Range("L" & xRow + 1) = a + b + c + d + e
        .Label19 = sh.Range("L" & xRow + 1)

³o¼Ë´N§â°ÝÃD¸Ñ¨M¤F¡A¦ý¬O§Ú¤£À´ªº¬O¡A¦b¼g.Cells()©M.Range()Àx¦s®æ¤è¦¡¬O§_¦³¤£¦P?
.Cell(xRow+1, "A")©M.Range("A" & xRow+1)³o¨âªÌ©Ò¥Nªíªº·N«ä¤]¤£¦P¶Ü?
¦]¬°·í§Ú§â«e­±¼g.CellÀx¦s®æªº¤è¦¡¡A¼g¤J.Range¤¤¡A¤]´N¬O.Range(xRow+1, "A")
·|¥X²{
"°õ¦æ¶¥¬q¿ù»~1004¡AÀ³¥Îµ{¦¡©Îª«¥ó©w¸qªº¿ù»~"
·N«ä´N¬O«ü§Ú¹ï.Rangeªº¼gªk¿ù»~¤F¶Ü?

®¦¡A¥H¤W¨â­Ó°ÝÃD¡AÁٽФj®a¤£§[«ü±Ð
¦pªG°Ýªº°ÝÃD¹L©ó²LÅã¡AÁÙ¬O¦U¦ì¤j¤j¨£½Ì

ÁÂÁÂ

GBKEE

¦^´_ 7# wxad0285
¤£¿ù,vba­n¶i¨B´N¬O­n¦p¦¹¬ã¨s

1. Range ¶°¦X
   
2. ¥Nªí¬Y¤@Àx¦s®æ¡B¬Y¤@Äæ¡B¬Y¤@¦C¡B¬Y¤@«ü©w½d³ò (¸Ó«ü©w½d³ò¥i¥]§t¤@­Ó©Î­Y¤z³sÄòÀx¦s®æ½d³ò) ©ÎªÌ¬Y¤@¥ßÅé½d³ò¡C

½Æ»s¥N½X

Cells(¦C¯Á¤Þ,Äæ¯Á¤Þ)  ->Cell(xRow+1, "A")   , ¦C¯Á¤Þ¥²¶·¬O¼Æ¦r,Äæ¯Á¤Þ¥i¥H¬O¼Æ¦r©Î¤å¦r

1. Option Explicit
   
2. Sub Ex()
   
3.   With Range("D10:F15")
   
4.     .Select
   
5.     MsgBox .Range("A" & .Rows.Count).Address
   
6.     MsgBox .Cells(.Rows.Count, 1).Address
   
7.     MsgBox .Range("C6").Address
   
8.     MsgBox .Cells(.Rows.Count, .Columns.Count).Address
   
9. End With
   
10. End Sub

½Æ»s¥N½X

wxad0285

¦^´_ 8# GBKEE

·PÁª©¥Dªº²Ó¤ß¦a¦^ÂÐ

¥»¨Ó­è­è­n°Ýªº°j°é°ÝÃD¡A·Q»¡¦A¬Ý¤@¦¸

µ²ªG´N©¿µM¬ÝÀ´¤F¡A¤£ªP§Ú¤§«e·Q¨ì¸£³U§Ö¿NµJ

Ãö©ó°j°é

¥u¬O§Ú«á­±ªº±Ô­z¨S¦³¤@°_¬Ý¡A¥H¬°¬O¤À¬qªº

¥t¥~«á­±À°§Ú¸É¥Rªº§Ú¤]°O¤U¨Ó¤F

¦A¦¸·PÁÂ

²{¦b¥¿¦b±qª©¤W¾Ç²ß§ó¦h¥Îªk

§Æ±æ¤§«á¦³°ÝÃD¡AÁٽЦU¦ì¤j¤jÀ°¤p§Ì¸Ñµª

ÁÂÁÂ~