Solved

Excel Macro is behaving odd.

Posted on 2011-09-18
11
314 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now