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

[µo°Ý] excel §Q¥Îvba¤ñ¹ï¨â­Ó¬¡­¶Ã¯¸ê®Æ

[µo°Ý] excel §Q¥Îvba¤ñ¹ï¨â­Ó¬¡­¶Ã¯¸ê®Æ

¤u§@»Ý­n,·Q¾Çvba ,³Â·Ð°ª¤âÀ°¦£¸Ñµª¤@¤U
½Ð°Ý¤@¤U,§Ú ªºEXCEL¦³¨â­Ó¬¡­¶Ã¯, (Á`ªí) ( 123 )
Á`ªí:   a:©m¦W  b:¨­¥÷ÃÒ   c:¥Í¤é    d:¹q¸Ü
123:   a:¨­¥÷ÃÒ  b:¥Í¤é
¦p¦ó±N(123)ªº¨­¥÷ÃÒ»P(Á`ªí)¨­¥÷ÃÒ¤ñ¹ï,
¦pªG(123)ªº¨­¥÷ÃÒ»P(Á`ªí)¨­¥÷ÃÒ,¤@¼Ë,
«h±N(123)ªº¥Í¤é(Äæ¦ì),¶ñ¨ì(Á`ªí)¥Í¤é(Äæ¦ì).

(Á`ªí¬ù¦³ 3000µ§¸ê®Æ)
(123¬ù¦³ 500µ§¸ê®Æ)

§A¦n¡G
  ¤U¦C»yªk½Ð¸Õ¸Õ¬O§_¦X¾A
Sub aa()

Dim mDic As Object
Dim mWk1 As Workbook
Dim mSht1 As Worksheet
Dim mRng As Range
Dim E As Range

Set mDic = CreateObject("Scripting.Dictionary")
Set mWk1 = Workbooks("Á`ªí")
With mWk1
    Set mSht1 = Worksheets(1)
    With mSht1
        Set mRng = .Range("b2:b" & .[a65536].End(xlUp).Row)
    End With
   
    For Each E In mRng
        If mDic.Exists(E.Value) = False Then
            mDic(E.Value) = E.Offset(, 1).Value
        End If
    Next
End With

With Workbooks.Open(ThisWorkbook.Path & "\" & 123 & ".xls")

With Sheets(1)

    For Each E In .Range(.[A2], .[A2].End(xlDown))

        E.Offset(, 1) = mDic(E.Value)

    Next

End With

.Close

End With

End Sub

TOP

¦^´_ 2# dechiuan999
ª`·NWith¸Ìªº±Ô­z­n¥[  .   
''''''''''''''''''''''''''''''''''''
With
Set mWk1 = Workbooks("Á`ªí")
   With mWk1
     Set mSht1 = .Worksheets(1)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Workbooks.Open(ThisWorkbook.Path & "\" & 123 & ".xls")
With .Sheets(1)

TOP

ÁÂÁª©¥D¤j¤j¡C
¤p§Ì¦b»yªk¤W
·|¦A¦h¥[ª`·N¡C

TOP

ÁÙ¬O¤£¦æ,
¤£¦æ¥Î°j°é¼g VBA¶Ü,

TOP

§A¦n¡G
  ¤p§Ì²¨¥¢±NÁ`ªí¤Î123¤GªÌ
ÄA­Ë¤F¡C
½Ð¦A­«¸Õ¤@¦¸
Sub aa()

Dim mDic As Object
Dim mWk1 As Workbook
Dim mSht1 As Worksheet
Dim mRng As Range
Dim E As Range

Set mDic = CreateObject("Scripting.Dictionary")
Set mWk1 = Workbooks("123")
With mWk1
    Set mSht1 = .Worksheets(1)
    With mSht1
        Set mRng = .Range("a2:a" & .[a65536].End(xlUp).Row)
    End With
   
    For Each E In mRng
        If mDic.Exists(E.Value) = False Then
            mDic(E.Value) = E.Offset(, 1).Value
        End If
    Next
End With

With Workbooks.Open(ThisWorkbook.Path & "\" & "Á`ªí" & ".xls")

    With Sheets(1)

        For Each E In .Range(.[b2], .[b2].End(xlDown))

            E.Offset(, 1) = mDic(E.Value)

        Next

    End With

.Close

End With

End Sub

TOP

¤j¤j:
¥i¥H½Ð°Ý¤@¤U
With Workbooks.Open(ThisWorkbook.Path & "\" & "Á`ªí" & ".xls")
¬O¤°»ò·N«ä¶Ü??

TOP

§A¦n¡G
®M¥Î¦ÜWorkbooksª«¥ó®É¥Îopen¤èªk
¤]´N¬O¶}±Ò¤@­Ó¬¡­¶Ã¯
§A¥i¦bopenªº¦ì¸m«öf1§Y¥i
¬Ý¨ìopen¤èªkªº¨Ï¥Î»¡©ú¡C

TOP

¤j¤j,¤£¦n·N«ä
°õ¦æ«á¥X²{ °}¦C¯Á¤Þ¶W¥X½d³ò??

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD