TheSwamp
Code Red => VB(A) => Topic started by: orcan 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.
-
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
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
-
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 :/
-
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
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
-
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.