Posts by Michal

    Re: Reversing POWER Function where the Power is unknown

    Thanks for your answer. Not being to great at math I thought I had 2 known. The result of the power formula and the number. All I am missing is the Power. Im obviously missing something ?
    X = Power(Number,Power) when the power is missing I still have X (the result) and the number.

    I have been researching how to reverse power function and I have found an example that does do the process successfully in order to find the NUMBER!

    =POWER(0.75,3.44) gives 0.37172

    to reverse it I used

    =0.37172^(1/3.44) gives 0.75  which is correct!

    When I tried something with my example in order to find the POWER I run into problems. Lack of skill and mathematical knowledge. In order to reverse the formula, TO FIND POWER, I tried many variations of the formulas above to no avail and my attempts would have mathematicians rolling around in laughter and tears.

    Could someone help me to find the 3.44 when that is the unknown in the power formula?

    Re: Run Macro That Resides In A Private Sheet Module

    I would imagine that all the sheets and well as the "this work book" spaces are private modules and all modules added are then public?

    What is the significance of the public and private modules, is there a reference that I can read up on?


    Re: Run Macro That Resides In A Private Sheet Module

    I am afraid that the reason Im here asking questions is that some of the fine points of programing escape me, I did what I did because it was what I know how to do and it works. I realise that private and public procedures/modules are supposed to mean something but Im afraid that their meaning escapes me, so if I did something that I wasent supposed to, it was only because I didnt know it was a nono.

    Thanks for the help anyway


    Re: Run A Macro That Resides On A Sheet From Else Where

    Hi ,

    the code to call the macro from my VBA code in a module

    Call MyMacro

    the code that is in the sheet

    sub MyMacro
    'my code
    end sub

    this works if the macro (sub) is in the same or even different module however does not when the macro (sub) is sitting in the sheet.

    By In the sheet i mean that in your vba edditor if you double click on a sheet you will get a place to put a code that resides in that sheet ???

    Is that right ?



    I have a sheet that has a macro on it, I would like to run that macro programaticaly but Im not able to call it using Call mymacro type of code. The code that calls the macro resides in a module and the macro is in a sheet. Obviously they are not talking, is there a way to run it remotely without moving the macro into the module?

    Thank you



    I have email adress a file name and a message.Each one is a variable

    emailAddress = "[email protected]"
    fileToAttach = "C:/hhdh.txt"
    Message = "Hello"

    I would like to include a button on my form to press and have a new email message open using the default client with the address already filled, the file attached and message displayed.

    Is this possible?



    I have a text box that a user enters numbers in 1 to 14

    each of these has an action asigned to them. Im having a problem with number 1 and 10,11,12,13,14. The text box takes the first number and runs away with it. How can I make sure that the numeber being entered is 1 and not 10?

    Im using TextBox1_Change sub.


    Re: Csv Date Format As General Or Date In Same Column


    Found a solution:

    I place the folowing code in the program, this converts ALL cels to Serial Date number from there its easy to get the date

        Selection.NumberFormat = "@"

    To extract the date back in normal format , in Cell A2 I would use this

    MyDate = Format(Cells(2, "A").Value, "Short Date")

    Hi Dave,

    Local:= True, is a seting that is NOT supported in Excel 2000, but is supported in versions after, to fix this exact problem, where date in CSV file when open programaticaly bypases the local date setings and is displayed in US format which results in the date being converted to text if the date does not make sence in US format. As I understand it.

    So my code works fine unless you run it in Office 2000 then it crashes and there is nothing that I can do about it other then resolve the problem using backward compatible code which is what Im trying to do.

    I have tried the Import Text Wizard and while that is a perfect solution I cant convert the macro that I recorded to accept the file destination and name as a string rather then being hard coded as it is when one records the macro. If you can point me in the right dirrection then that would solve this problem.

    Finaly Im starting to realise that you are very particular and exact in everything that you look at unfortunately I took a short cut to express the values in the cells

    14/9/2007 results in a cell being formated as General and stands for 14sep2007
    9/10/2007 results in a cell being formated as Date and SHOULD stand for 10sep-2007

    I hope that this will now make sence.



    I am using a CSV that has the date in Column A in the format of
    DD/MM/YYYY. When I open this CSV using a VBA comand the Format of the Cells in this col are then formated as either Date or General.
    The general format happends when the date cant be turned into US format and when it can then the date is turned US and displayd as such.

    14- Sep = 14/09/2007 as General
    10- Sep = 09/10/2007 as Date

    Im in Australia so the correct format is DD/MM/YYYY.

    Microsoft has issued a fix for this using Local:=True in the file open comand which is all good and well as it works unless you have office 2000 when it crashes.

    What would be the best way of fixing this, so that the Dates are all correct or the col is all the same format?

    Thank You

    Re: Delete Rows Based On Condition Of 1 Column

    Hallo P45cal,

    RESOLVED !!!!

    Thank you so much for your time, I have spent months on this problem but it was not until here that the real problem was diagnosed, and your solution is just perfect. The CSV file is a fickly thing with the commas and spaces and date problems that occurs when excel opens it and tries to deal with the content.

    I can easily place this into my program.

    All I have to do is then rename the original file and then name the newly created file as the original and the analysis program runs without any problems.

    Thank you for such an elegant and simple solution. It is fast as well. Perfect.


    Re: Delete Rows Based On Condition Of 1 Column


    I have tried the suggested method and this time the analyser program crashed all together. Normaly the problem is that the error is in the display of the results. By that I mean that results are not compiled under 1 criteria but all listed separatele so if the ocurance is say 4 then under normal operation there may be 5 instances of 4 and so the analysis is 4 5 times when the error ocurs the 4 is listed as a single instance 5 different times.

    Anyway, it may not be commas but may be spaces. I must also say that the files look to me like they are identical, in as much I cant tell the differance between them. Perhaps excel changes a column to text where a number is expected? How would I be abel to test for that ?

    Perhaps this is the answer because using the wordpad I can delete the lines without afecting the structure.

    A bit of a break through.

    I took the files that I uploaded here aand compared them in word using the non printing character view. What I found is that there are charecters missing in quite a lot of the data.

    For instance the second cell in the scv contains a number designating a day of the week. In the unaltered file it is shown as
    format Cell 1 Date, Cell 2 Day of Week and it looks like this

    17/09/2007, 2,

    in the altered file it is shown as


    in the non printing characters it is actually shown as ,..2, but the dots are not fulstops like here but rather in the middle of the line.

    I would think that that may be the problem the Analyser is expecting 3characters and instead its getting 1. This may cause the problems with the way it interprets data.

    Further more it seams that the spaces are only ditched from numbers any text still looks like it retains the extra spaces.

    The question then is how do I stop excel from ditching the spaces?

    Thank You for your help


    Re: Delete Rows Based On Condition Of 1 Column


    The file as originaly produced by the program (which is also has the analyser ) is called Test
    the file that I edited by deleting the second row of data is called test changed.

    The cell that I want to base the row delete on is AJ and the value = 5
    but I would like to be abel to later add other criteria in other cells.

    I should mention I opened the CSV in excel to delete the row and did this manualy, when I do this through my program the result is the same, from all indications the problem occurs when I open the file in excel.

    Thank you

    Re: Open Csv With Wordpad

    Hi X10A,

    I have edited the file manually in wordpad and the result is good. The file retains the formating. It may be possible that word can do the same I however have not got the programing skills to acomplish this by myself.


    Re: Delete Rows Based On Condition Of 1 Column


    The analyser program is a comercial software, not writen by me. I agree with you however that it is probably at fault but the fact that I can edit in wordpad and not compromise the file, means that it should be possible. Perhaps not in excel but using VBA and perhaps other methods of opening and editing the file.