Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Macro is behaving odd.

Posted on 2011-09-18
11
Medium Priority
?
331 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

971 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