Re:vba findnext 속도 개선

[Lago]

아래 코드를 사용해 보세요

Sub Lago()
     Dim X As New Collection
     Dim n As Long, k As Long, c As Long
     Dim i As Long, j As Long, s As Long
     Dim wsF As Worksheet
     Dim ws As Worksheet
     Dim AryF, Ary, xVar(), Tx
     Dim ss As String, str As String
     Dim t1, t2
    
     On Error Resume Next
     t1 = Timer
     Set ws = Sheet3
     Set wsF = Sheet7
     With Application
          .Calculate
          .Calculation = xlCalculationManual
     End With
     With wsF
          AryF = .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row).Resize(, 8)
          For i = 1 To UBound(AryF, 1)
               ss = AryF(i, 1)
               k = 0
               k = X.Item(CStr(ss))
               If k = 0 Then
                    n = n + 1
                    ReDim Preserve xVar(1 To 1, 1 To n)
                    X.Add n, CStr(ss)
                    xVar(1, n) = i
               Else
                    xVar(1, k) = xVar(1, k) & "," & i
               End If
          Next
     End With
     With ws
          .Range("S5:S" & .Cells(Rows.Count, 1).End(xlUp).Row + 30).Resize(, 18).ClearContents
          c = 35
          Ary = .Range("A5:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Resize(, c)
          n = UBound(Ary, 1)
          For i = 1 To n
               ss = Ary(i, 1)
               k = 0
               k = X.Item(CStr(ss))
               If k > 0 Then
                    str = xVar(1, k)
                    Tx = Split(str, ",")
                    For j = LBound(Tx) To UBound(Tx)
                         s = Tx(j)
                         Ary(i, 19 + j) = AryF(s, 2)
                         Ary(i, 27 + j) = AryF(s, 3)
                    Next
               End If
          Next
          .Range("A5").Resize(n, c).Value = Ary
          Erase Ary
          Erase AryF
          Erase xVar
     End With
     Set X = Nothing
     With Application
          .Calculation = xlCalculationAutomatic
     End With
     Set ws = Nothing
     Set wsF = Nothing
     t2 = Timer
     MsgBox Format(t2 - t1, "0.00") & " 초 소요"
End Sub

복사본 inventory_차감_PO_입고_차감_model_rev13_질문_.xlsm

[탁구짱]

Oh my god....0.5초만에 끝나네요...완전 놀람 입니다. 제가 나름데로 사용한 배열은 완전 못쓰는 코드네요 ^^
이렇게 차이가 많이 나네요..감탄 감탄
VBA 공부할만 하네요 좀더 열심히 해야 겠습니다.

Find와 findnext 는 사용하지 않으신거군요, 내일 Lago님 코드 잘 보고 다시 공부해야 겠습니다. ^^
정말 감사 합니다. 왜 "배신" 인지 알겠습니다 ^^


요즘 VBA 공부 한다고...태어나서 요즘 처럼 머리 많이 사용하기는 처음이네요...^^
코딩 재미 없었는데, 요즘 점점 재미가 붙는거 같아요.

아 혹시 이런것도 sql 로 완성할수가 있나요? inventory 차감, PO 차감 이런것도 SQL 로 가능 한가요?

[Lago]

Findnext 보다는 배열&컬렉션 조합이 더 빠르며 특히 데이터가 많을때 확연한 속도차를 보입니다.

[Lago]

SQL로 가능한지 여부는 DB 구조를 보기전에는 말씀드리기가 어렵네요..!!
짧은 경험상 불가능은 거의 못봤습니다.
암튼... DB담당자에게 우선 현재하고 있는 로직을 설명하고 (결과1 -> 결과 2 -> 결과3 -> 최종결과 이런순으로)
DB담당자에게 최종결과를 볼수 있는 VIEW를 만들어 달라고 하시고
그 VIEW를 다운로드 하시면 됩니다.
저도 실력이 미천한지라... 쿼리는 DB담당자에게 문의 해 보세요--이방법이 젤 좋을 듯 하네요...!!

[탁구짱]

아 감사 합니다. findnext 쓸때마다 속도가 느려서..궁금했는데, 배열 & collection 조합을 Lago님 코드 보면서 완전히 공부 해야 겠네요
네.. SQL 관련해서는, 저희 IT 후배들에게 한번 문의해 봐야 겠네요 ^^ 저도 그냥 SQL은 자르고,자르고 조금 JOIN 해서만 가져오는 수준이라서 ^^ 정말 다시 한번 감사 합니다.

아... 배열 잡을때 예를들면, AryF = .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row).Resize(, 35)-> 35 만큼 이렇게 크게 확장해서 잡아도 속도나 성능에는 영향 없는거죠 ?

[Lago]

배열의 열 개수의 크기가 중요한 것이 아니고
얼마나 많은 데이터를 사용하는것이 속도에 영향을 주는 인자입니다.
타라서 35개의 컬럼을 사용했다고 느리지는 않습니다. (행부분은 거의 대부분 사용되므로 행이 많아지면 느리지만 열부분은 사용하는 인자가 적어 차이가 없을것입니다. )

다만 배열은 메모리(RAM)에 만들어 지므로 RAM이 작으면 문제가 발생하겠죠!!!
음... 예를 들어 2백만행*80컬럼 정도의 배열을 만든다면 사용하는 PC의 Ram사양에 따라서
메모리 부족(일명 뻑)현상이 발생할겁니다. 물론 이것도 PC 사양이 좋아 16G 램을 사용한다면 커버가 될듯 하네요

[탁구짱]

아 감사 합니다. Lago 님 코드 흉내 내서 많이 배열 사용 하고 있는데, 예를들면 key값인 item이 A열에 있고
열 값을 35만큼 resize 할때마다 조금 궁금 했었거든요 ^^

[탁구짱]

며칠간 틈틈이 짜주신 코드 이해 한다고 머리 터지는줄 알았네요..어제 새벽 2시 까지 생각하고 또 생각하고..
오늘 아침에 또 보다 보니까..이제 이해가 좀 되네요 ^^ 응용까지 하려면 한참 걸릴듯 합니다 ! ^^

[Lago]

이게 원래 알면 쉽고.. 모르면 엄청 어려워요..!! 지금 확실하게 잡아 놓으셔야 합니다.
물론 쿼리때문에 점점 사라지기는 하지만 배열이해해 놓으면 쿼리도 쉽게 접근할 수 있어요!!
꼭 좋은 결과 얻으시길.........!!!