Solved

Excel Macro is behaving odd.

Posted on 2011-09-18
11
319 Views
Last Modified: 2012-05-12
Hi Experts;

I have a macro reading from txt file and sorting tables by Realtion Manager . It works fine for most RMs except RM No. 171,172,174  data get mixed.
attached both files.
Master-Filtering.xlsm
CBG-Aug--11.txt
0
Comment
Question by:obad62
  • 6
  • 5
11 Comments
 
LVL 13

Expert Comment

by:khairil
ID: 36556934
Hi,

I do not really understand what is RM here, but from the pattern of your data, I think you have missed some code for at the /Brnch/Brn No.

First bug
Here is your bug in the portion code under class module clsOfficers, Import function:

        ElseIf TheLine Like "* ###### [A-Z0-9]*" Then
            If TheLine Like "[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] *" Then
                BranchName = Left(TheLine, 4)
                Set Branch = Nothing
                Set Branch = Officer.Branches(BranchName)
                If Branch Is Nothing Then Set Branch = Officer.Branches.Add(BranchName)
            End If

Open in new window


You can see here, the line "If TheLine Like "[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] *" Then" (I will call buggy line after this) will create new branch and assign it to the new officer.

However, for Officer "171 Nawaf Al-Senan ECBG Ext 8", all branch are not in the acceptable format need by buggy line above, the format MUST begin with text in capital letter NOT number like branch data for Nawaf below:
302818 KHUSEIM CO
302996 N.AMERICAN CULT
304089 BALHAMR HOLDING

The code went OK for next data because it begin with needed format:
JUBL     362900 NAMA CO.

To fix it, the branch that exist soon after new officer must begin with upper letter case.

Second bug
Data for "186 Syed Ahmed Ziauddin" are not property in place.

You have to notice all other officer's name end with number or code, such:
187 Ayman Al-Kaf HINT 8404
151 Nawar Al-Khonaizi ECBG 26
148 TURKI AL-HUSSAINI - WCBG

Your code take the last word after spliting using space. Unfortunately for "Syed Ahmed Ziauddin", the last word is his name not the number or code. So you have to put a number or code to his name.
 
0
 

Author Comment

by:obad62
ID: 36558509
Hello Khairil

I appreciate your profissional comments, pls mind that I did not created this macro, and am not super in coding.
Onlight of your comments issues comes from the txt.
Is there any hope to fix those issues. if yes ; please can you update the macro.

Thank you very much
0
 
LVL 13

Expert Comment

by:khairil
ID: 36558649
The easiest way is to change the data so that every data come after name must begin with uppercase text like "JUBL     362900 NAMA CO".

If you like to change the code then replace this code:
            If TheLine Like "[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] *" Then
                BranchName = Left(TheLine, 4)
                Set Branch = Nothing
                Set Branch = Officer.Branches(BranchName)
                If Branch Is Nothing Then Set Branch = Officer.Branches.Add(BranchName)
            End If

Open in new window


with this:

 
           If TheLine Like "[A-Z][A-Z0-9][A-Z0-9][A-Z0-9] *" Then
                BranchName = Left(TheLine, 4)
                Set Branch = Nothing
                Set Branch = Officer.Branches(BranchName)
                If Branch Is Nothing Then Set Branch = Officer.Branches.Add(BranchName)
            Else
                TheLine = Trim$(TheLine)
                If TheLine Like "###### *" Then
                    BranchName = Left(TheLine, 6)
                    Set Branch = Nothing
                    Set Branch = Officer.Branches(BranchName)
                    If Branch Is Nothing Then Set Branch = Officer.Branches.Add(BranchName)
                End If
            End If

Open in new window


Be aware however, use at your own risk. I not knowing what your program do, this change might broken your business process.

For the second bug, just simple fix, make sure officer's name end with some code.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Expert Comment

by:khairil
ID: 36564396
if you wish to have blank branch name instead of 6 numbered code then replace ALL code on line 7, 8 and 9 above with code below:

                
If trim$(TheLine) Like "###### *" Then
     BranchName = Left(TheLine, 4)

Open in new window

0
 

Author Comment

by:obad62
ID: 36565199
Hi Khairi,

Big thank you. it works great.

but as you see there is defined cloumn for Ccy currency but it is not picking up.

your above solution is accepted to me, if you like to open a new for the Ccy i will.

thank you
0
 
LVL 13

Expert Comment

by:khairil
ID: 36566921
hi, i away from my pc.

now i on ipad, cannot do much thing. i will have a look after get back my pc. aaah... i wish for windows8 tablet... with ipad hardware.
0
 

Author Comment

by:obad62
ID: 36570719
windows8 tablet ?? I can imagine how many times you need to restart your tablet caused of hang up :) ..... pls I don't know why windows is consuming rams after a while while osx or ubuntu not. I had very bad experience with a stupid i-mate .


:)

0
 
LVL 13

Expert Comment

by:khairil
ID: 36571728
hah... just may be thing get better with M$, anyway if it is running Windows, then most likely I can help from anywhere. IPad is so much convenience and handy when travel. The battery is good too, my almost 3kg Dell Persicion is no match in weight and portability, let alone the power hungry graphic card.

I will back with your problem afterwards, just coming back from travel.
0
 
LVL 13

Accepted Solution

by:
khairil earned 500 total points
ID: 36572495
Hi,

I have to thru you excel macro once more, it seems that the macro are NOT doing anything to extract the currency info.

You should treat me some drinks for this :), just kidding.

So here it is, this might not be excellent solution but you are welcome to improve it:

Insert this code:

CustCurrency = ReturnCurrency(TheLine)

Open in new window


AFTER:

EconomicSubSector = Mid(EconomicSubSector, 2, Len(EconomicSubSector) - 2) 'get rid of surrounding parens

Open in new window


in your code.

Then add this function to clsOfficers:

Private Function ReturnCurrency(sInput As String) As String
    Dim iCount As Integer
    Dim sCleanUp As String
    Dim sElement() As String
    Dim iNumberCount As Integer
    Dim sReturn As String
    
    sCleanUp = Replace(sInput, "  ", "^")
    
    iCount = InStr(1, sCleanUp, "^^")
    
    Do While iCount > 0
        sCleanUp = Replace(sCleanUp, "^^", "^")
        iCount = InStr(1, sCleanUp, "^^")
    Loop
    
    sElement() = Split(sCleanUp, "^")
    iNumberCount = 0
    
    For iCount = 0 To UBound(sElement()) - 1
        If iNumberCount = 2 Then
            sReturn = Trim$(sElement(iCount))
            Exit For
        End If
    
        If IsNumeric(Trim$(sElement(iCount))) Then
            iNumberCount = iNumberCount + 1
        Else
            iNumberCount = 0
        End If
    Next iCount
   
    ReturnCurrency = sReturn
End Function

Open in new window


I cannot find suitable delimeter except double spaces, hopefully it suite you.

Anyway, I think you problem solve now and wish you to close it.

Good luck then.
0
 

Author Comment

by:obad62
ID: 36572798
Khairi.... thank you
0
 

Author Closing Comment

by:obad62
ID: 36572804
Great job
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
This is about my first experience with programming Arduino.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question