Posts by MisterT

  • Solver with Multiple Target Cells

    Is it possible to run a solver on multiple target cells, i.e. to solve cells A1:A10 to all equal 100 by changing all of B1:B10?


    To make matters worse, this is a [deliberately] circular model...


    Any suggestions gratefully received.


    Unfortunately, I can't attach the file, so this will have to be an abstract question.

  • Create Add-In with Command Bar drop-down

    Hi,


    I have a worksheet of macros and UDFs I use fairly often, and want to convert this to an excel add-in for ease of use by myself and others.


    I am just unsure how to create the drop-down menu from the command bar, which will display the macro names and clicking on them will give the required functionality.


    I have used userforms before, but require something different, I believe.


    Very interested to hear from others who've done something similar.


    Thanks in advance,


    Mike

  • Select Multiple Active Sheets (not by name)

    Hi,


    I am trying to create a clean generic macro to copy selected sheets to a new workbook and then paste those sheets as values.


    I have done this for the active sheet, but cannot seem to find the terminology to copy all of the selected worksheets (grouped) without using the worksheet names (making it workbook specific).


    Any suggestions very gratefully received



    Thanks in advance,


    Mike

  • Grouping Rows -> Crashes Excel

    Hi,


    I have a workbook that crashes each time I try and group rows together.


    It appears that there is not enough space to the left of the A column to allow the grouping indicators to show. I have toggled between showing them or not, using Ctrl-F8 and there is only the slightest difference.


    If anybody has had this before, and knows the solution, I would be very grateful to hear from them.


    Regards,


    Mike

  • [Solved] VBA : Formatting Column Widths

    I'm sorry to have left it for so long, but it is not quite perfect -
    The code will find the max column width for the whole column, not just that of the cells selected. Is it possible to select a range (A1:G1) and pull the max for that?


    This will be of great use in resizing tables.


    Thanks very much,


    Mike

  • [Solved] VBA : Formatting Column Widths

    Hi,


    I would like to be able to autosize excel columns based on the max width required for a range of cells, rather than autosize them all.
    i.e. all columns will be the same width - that of the cell with the most characters.


    I have had some success in doing this in one worksheet, but would like to know a more general solution, which could be applied whenever I select a row or range of cells in a row.


    My simple solution:
    1) Find the maximum length of characters in the range ={max(len(A1:A99))}
    2) set column width based on the above
    3) Copy column width (paste special) based on the above.



    If anybody has done this before (I can't believe they haven't) or knows of a simple vba solution, I'd love to hear from them.


    Thanks in advance.


    Mike

  • [Solved] VBA: footnotes and endnotes

    Hi,


    I would love to know a quick way to put footnotes (and numbered references) into cells in excel, in the same way that one can in word.


    The quickest way I can do it is:


    Select cell > F2 > write (#) and highlight it
    (where # is an increasing number for the footnote)


    Then: Ctrl_1 (to format) > Alt+e > Enter


    Then go to bottom of page, and type (#) and then footnote, and then format it to be pretty small.


    Surely stacks of you guys are having to do this every day, and have worked out a quick way? If so, I would love to hear it..


    Thanks,


    Mike

  • VBA: stripping text from PING output

    Hi Andy,


    Many thanks - I am enormously grateful. That is a very neat work around.


    I was attempting the same thing in VBA and not getting very far...


    You have also generated meaningful binaires for ping response, as I have just realised that "request timed out" responses can be eliminated by increasing the timeout settings, so will be taking an average over several long pings.


    BTW - I used Fping to ping multiple hosts, which is availabe at http://www.kwakkelflap.com/fping.html and 21,000 domains took under three hours.


    --Thanks again,


    Mike:wink2:

  • VBA: stripping text from PING output

    Hi again.


    Could anybody possibly point me in the right direction for stripping unwanted text strings from the following output:


    Reply[1] from 212.23.2.197: bytes=32 time=16 ms TTL=117
    Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
    Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
    Reply[1] from 62.173.67.99: bytes=32 time=31 ms TTL=50
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    66.246.48.216: request timed out
    66.246.48.216: request timed out
    66.246.48.216: request timed out
    203.98.189.18: request timed out
    203.98.189.18: request timed out
    Reply[1] from 203.98.189.18: bytes=32 time=687 ms TTL=46


    In an ideal world, I'd like to be able to do the following:
    Create a few new variables (columns) which include:


    --A binary variable for ping response. i.e. 1 if ping reply, 0 otherwise.


    --IP address


    --time


    --TTL


    Additionally, the pings are carried out many times for the same URL, as there are duplicates in the list. Is it possible to, say, take an average of the pings for each URL, and report the same one average PING for all identical URLs?


    I realise that this is a big ask, as some of these things are probably not possible. The alternative is I go back to the old find and replace function, and autofilter...


    Thanks in advance,


    Mike


    File Format:


    URL -- PING_Output
    URL1 ping1
    URL2 ping2
    URL2 ping2 (different result)
    URL3 ping3

  • [Solved] Exporting: excel to comma delimited text file

    < EDIT..&gt; That really is fast! Thankyou very much for all your help. Solved:o </EDIT...&gt;





    Okay,


    The trusty Microsoft Close-then-Reopen routine worked, and macro status is now medium.



    However, Alt-F8 brings up the macro, and clicking "run" gives me the hourglass for all of two seconds, then nothing.


    If it has actually created the file (which would make it the fastest excel function ever), then where is it?


    If not, any ideas?


    Here is the modified (slightly) code:


    ---------------------------------------
    Sub TextFileWrite()
    Dim myrng As Range
    Set myrng = Range("B2:B21020") 'CHANGE THIS RANGE TO MEET YOUR SPECS

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fs, f, ts, s
    Dim cellv As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CreateTextFile "C:\urlfile.csv" 'You can change the output name to anything you want
    Set f = fs.GetFile("C:\urlfile.csv")
    Set ts = f.OpenAsTextStream(ForWriting, TristateFalse)

    For Each cell In myrng
    cellv = cell.Value
    ts.Write (cellv & Chr(44))
    Next cell

    ts.Close

    End Sub
    ----------------------------------


    Cheers,


    Mike:o

  • [Solved] Exporting: excel to comma delimited text file

    Gah!


    Now I really am showing my ignorance...


    Your instructions were so clear, I have carried them out to the letter. Unfortunately, macros have been disabled for security purposes.


    I am logged into Office XP as administrator, and in excel have gone to tools &gt; macros &gt; security and reduced it to low (for the time being)


    When I then goto tools &gt; macros and try to run the macro, it still says:


    "macros in this workbook are disabled because the security level is high, and the macro has not been dgitally signed or verified safe..."


    Thanks for all this hand holding, I really appreciate all the help I get from this forum.

  • [Solved] Exporting: excel to comma delimited text file

    That is really good of you Ralph. Thanks.



    Right before I give you the gold star, could you just <ahem&gt; remind me what to do with the macro?


    If I goto Tools &gt; Macros &gt; Record New Macro


    and then copy and paste the (corrected) code, will that just fire up automatically?


    Apologies for my ignorance, I had no idea excel was quite so versatile. I am actually doing my analysis in Stata - a command line package, but find excel is good (and getting better) for manipulation.


    Thanks again.:yes: