Author Topic: Search Complexity  (Read 3883 times)

0 Members and 1 Guest are viewing this topic.

orcan

  • Guest
Search Complexity
« on: February 15, 2015, 08:59:17 AM »
Hi

i am trying to gather information among different excel files.

I have noticed that excel vba is always having hard times on searching two similar words.

Those words are "ZK0704706800" and "ZK0704707800" as you can see only 1 number is different from each other.

In many cases excel vba treats those words as the same.

That should not be a hard task for VBA do you have any idea about this?

Thanks in advance.

RICVBA

  • Newt
  • Posts: 62
Re: Search Complexity
« Reply #1 on: February 15, 2015, 10:06:27 AM »
use "Find" method of "Range" object.
I made a simple test placing those two "words" in different cells (both alone and among other characters) and always succeeded in recognizing them
for example
Code: [Select]
Dim c As Range
Set c = Range("b2:b100").Find("ZK0704706800", LookIn:=xlValues)
should it not work for you, please give more details about your case environment

orcan

  • Guest
Re: Search Complexity
« Reply #2 on: February 15, 2015, 10:30:50 AM »
Thx for the reply mate

Below the code i use

"Option Explicit

Sub AylikSiparisDene()

Dim a1, a2, a8, a20 As Variant
Dim a5, a9, a19, a23, a21, a22, a24 As Integer
Dim Rng As Range

Application.ScreenUpdating = False

Range(Sheets(2).Cells(3, 9), Sheets(2).Cells(32, 20)).ClearContents

a1 = ActiveWorkbook.Name
Sheets(1).Activate
a2 = Sheets(1).Cells(1, 2)
Workbooks.Open a2
a8 = ActiveWorkbook.Name
a5 = 32
a9 = 41

'*******************Aylık bazda model siparisi hesaplanması
  For a19 = 3 To a5 'program excelinde model ismi iceren sutunun son dolu satır numarası
  Workbooks(a1).Activate
  Sheets(2).Activate
  a20 = Sheets(2).Cells(a19, 1) 'aranacak model ismi
  a23 = 0 'aylık siparis adedi sıfırlandı
    For a21 = 2 To a9 'forecast te model isminin oldugu satırlar
      Workbooks(a8).Activate 'forecast excelini aktive et
      Sheets(1).Activate
      If Sheets(1).Cells(a21, 1) = a20 Then
        For a22 = 4 To 15
        a23 = 0
        Workbooks(a8).Activate 'forecast excelini aktive et
        Sheets(1).Activate
        a23 = Sheets(1).Cells(a21, a22) 'model basına her ayın siparis adedi
        Workbooks(a1).Activate
        Sheets(2).Activate
        Set Rng = Range("h:h").Find(what:=a20, lookat:=xlWhole, LookIn:=xlValues)
        a24 = Rng.Row
        Sheets(2).Cells(a24, a22 + 5) = Sheets(2).Cells(a24, a22 + 5) + a23
        Next a22
      Else: End If
    Next a21
  Next a19
'************************************************************

  Workbooks(a1).Activate
  Sheets(2).Activate

End Sub

as you can see i was using the range-find method but it is still not working

i tried to delete last 2 digits of one of them and now it works.

so the difference between those 2 words is much more than before

But this not an exact solution :/




RICVBA

  • Newt
  • Posts: 62
Re: Search Complexity
« Reply #3 on: February 16, 2015, 04:40:15 AM »
I tried and went through your code but it worked for me using two trial workbooks I made up from reading your code
I could help you more if you'd post your workbooks to see what's really going on

besides that you may want to consider the following hints to considerably speed up your code:
- avoid all those "activate" methods
- take the "Find" block out of both "For a22 = 4 To 15" and "For a21 = 2 To a9 " loops, since it only depends on "a19" outermost loop variable

for what above I adapted your code as follow
Code: [Select]
Option Explicit

Sub AylikSiparisDene()

Dim a20 As Variant
Dim a5, a9, a19, a23, a21, a22, a24 As Integer
Dim Rng As Range

Dim Wb01Sh01 As Worksheet, Wb01Sh02 As Worksheet, Wb02Sh01 As Worksheet

Application.ScreenUpdating = False

Set Wb01Sh01 = ActiveWorkbook.Sheets(1) 'sheet from where to read "forecast excelini" workbook name
Set Wb01Sh02 = ActiveWorkbook.Sheets(2) 'working sheet in main workbook
Wb01Sh02.Range(Cells(3, 9), Cells(32, 20)).ClearContents

Workbooks.Open Wb01Sh01.Cells(1, 2)
Set Wb02Sh01 = ActiveWorkbook.Sheets(1) 'forecast excelini

a5 = 32
a9 = 41

'*******************Aylik bazda model siparisi hesaplanmasi
For a19 = 3 To a5 'program excelinde model ismi iceren sutunun son dolu satir numarasi
    With Wb01Sh02
        a20 = .Cells(a19, 1) 'aranacak model ismi
        Set Rng = .Range("h:h").Find(what:=a20, lookat:=xlWhole, LookIn:=xlValues)
    End With
    a24 = Rng.Row
    a23 = 0 'aylik siparis adedi sifirlandi
    For a21 = 2 To a9 'forecast te model isminin oldugu satirlar
        With Wb02Sh01
            If .Cells(a21, 1) = a20 Then
                For a22 = 4 To 15
                    a23 = .Cells(a21, a22) 'model basina her ayin siparis adedi
                    Wb01Sh02.Cells(a24, a22 + 5) = Wb01Sh02.Cells(a24, a22 + 5) + a23
                Next a22
            Else: End If
        End With
    Next a21
Next a19
'************************************************************

Wb01Sh02.Activate
Application.ScreenUpdating = True

End Sub
where I made use of worksheets variable to both avoid "activate" method calls and make the code more readable (of course you can change sheets variable names as per your needs.

bye

orcan

  • Guest
Re: Search Complexity
« Reply #4 on: February 18, 2015, 02:27:28 PM »
wow ric

i was thinking about changing this activate routine and you came with this solutions.

That was a lucky day for me :)

and also thx for the .find suggestion.

Whenever i am able i will send other sheets

cheers.