Excel Macro is behaving odd.

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

Select allOpen 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.
 

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

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

Select allOpen 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

Select allOpen 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.

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)

Select allOpen in new window

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

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.

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 .


:)

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.

Khairi.... thank you

Great job