Posts by Imbuzi

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Greek Characters Dont Display Properly


    Font and cell size is the same. I have managed to fix the problem by amending my regional settings to Greek. it seems Ive found one of the quirks in dealing with different languages and alphabets in Excel.


    Thanks for the suggestions

    Hi All,
    I cant seem to get an excel file that I have received from a Greek collegue to display Greek Characters properly.


    Some of the lines in the file with Greek appear to be correct and have not been turned into gibberish.


    In addition the cell properties of both the correctly displayed cell and the gibberish cell are exactly the same and in the cell format sample the gibberish is displayed as Greek Characters.


    Ive tried formatting the corrupted cells with the uncorrupted cells format but that doesnt seem to have any effect.


    TIA


    Graeme

    Re: Trim A String In Vba


    The completed string is required to be only 14 characters the users tend to only had spaces on the end of either the "corpAcct" or "Subdiv", thats why I was looking to use trim which if my understanding should do the trick.

    Hope someone can help on this one.


    I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working any ideas


    TIA


    Graeme


    Re: Will Tool Pack function travel and work with data file (.xls)?


    You could include this in the ThisWorkbook object which would ensure that the addin is always present


    Re: Cell summing, multi conditions


    Your best bet would be to utilise a SUMIF Formula.


    e.g. =SUMIF(A1:C10,"AddA",B1:C10)


    However as you are using more than 1 condition for the summing I suggest that you insert and additional column into the sheet which includes an if statement along the lines of
    =IF(C1>=0,"Add","")&trim(B1)

    Re: MB xls file size best treatment


    Try and not use to many sheets in the workbook as this can increase the size of the file.


    Also check that the end of the used ranges within the worksheets does not exceed the area where you have data/formulae.


    Only apply number formats to area that need it as this can also increase the size of the workbook.


    HtH

    Re: Amortisation using Actuarial method


    Quote from Fin Fang Foom

    I dont know this file could help you but it might get you started.



    Thanks for the file but unfortunately the amortisation schedule that you posted isnt the actuarial method and isnt accurate enough. Thanks anyway though.

    Well chaps its been a while since ive been on here and asked a question.


    Ive been asked to build a spreadsheet that is able to amortise a sum of money using the actuarial method.


    Only one problem is I havent got a clue where to start. is there any finance peeps that would be able to get me started?


    TIA


    Graeme

    Michael,


    One possible way you could get to switch between the languages is by using SendKeys. I havent used these for a while so you may need to get some help from some of the others but here goes...



    HTH


    Graeme


    PS if you want to test the code you have to run it you cannot use debug mode to test it.

    Workfrustration,


    Have you not thought of saving your files as an addin. this will allow your users to utilise the file and you could then password protect the module to prevent tampering


    HTH
    Graeme

    Or is this what you are after?


    Sferical,


    Is this the code that you are after:


    Code
    Sub GetFileNameExample()
        Dim fNameAndPath As Variant
        fNameAndPath = Application.GetOpenFilename _
                   (FileFilter:="Text Files (*.txt), *.txt", _
                    Title:="Select File To Be Opened")
        If fNameAndPath = False Then _
                          Exit Sub
        MsgBox fNameAndPath
    End Sub



    HTH
    Graeme

    Kenshin,


    I would use a combination of both Access and Excel by holding the data in Access tables and then consolidating the tables using queries and then linking to the queries from Excel using a get external data link.


    Depending on the amount of data you have i.e does the number of lines exceed th 65000 limit in Excel you may want to consider using SUMIF formulae in Excel.



    TIA


    Graeme