Posts by charlesa920

    I have a local area network with a couple of hundred computers which share one internet connection. Each user has a CPE (customer premise equipment) with an assigned (by me) IP. This CPE connets to the users equipment via CAT5. My responsibility is to provide internet. Sometimes users call and say their comupter cannot access the internet. I need a quick way to see if the problem lies with my network or if the problem is with the customer's computer.

    It seems to me that if I could open my spreedsheet with all the network connections and user data and simply ping their IP I would see if the problem is with my equipment or the users.

    Column A of my spreedsheet has the actual IP addresses: thru and thru but not all are currently being used. Each row has distinct user account information.

    I have created a shortcut, named it PING106.bat and listed the target as %windir%\system32\ping.exe which I can click on and it runs. Next I inserted a hyperlink in A:107 and it does work (it brings up a DOS screen and pings three times then closes the DOS screen)... But there must be a better way. I don't want to create hundreds of shortcuts and insert hyperlinks to specific cells one at a time.

    It would be nice if I could click on a cell which contains an IP and know if that particular IP is up and reachable on my LAN.

    Thanks for any ideas,

    BTW as a heads up. If this PINGING can be done the next step is to output something which indicates if the link is up or down. the next step is to conditionally formatt the cell green/red. and to automate the process to run every couple of minutes.... but that is the stuff for another thread...

    Re: Change Hex Number To Dec Number

    I had a similar problem of needing to convert hex2dec or dec2hex. application had to determine which coversion was needed on a case by case basis. It is part of an old inventory receiving program for Cell phone. Cell phones used to have HEX & DEC Electronic Serial Numbers. Now they are using IMEI so I don't believe this bit of code what you want but may give some insite...

    the HEXESN is always 8 digits
    the DECESN is always 11 digits
    the first 2 HEX digits equate to the first 3 digits in the DEC

    My cellular carrier supplies my monthly commission reports where column "S" indicates the month and column "T" indicates the year. The first few hundred rows will indicate the month like "NOV" and the reminding rows will have it like "11".

    The year will always be yyyy.

    I want to combine these into one column where it will read like "NOV, 2006" so it will match other months already entered.

    Each month has its own worksheet.

    I started to concanetate and fill down all the active rows.
    I found this snippit of code but cannot get it to work:

    ActiveCell.FormulaR1C1 = "=RC[-2]&"", ""&RC[-1]"
    ActiveCell.Range("U1:U" & LastMSRow).FillDown

    But I got to thinking,..Should I open an InPutBox? or something and have the user just type in what they want. This would solve the "NOV" and "11" issue. But I'm still left with xlFillDown? and I don't know how to get it to stop when it finds an empty row.

    Hope I'm not breaking forum rules by asking 2 questions?
    1) Should I use the concanetate & fill down or require user input?
    2) How do I get the auto fill down to work as needed?


    Thanks, Charles

    Re: Pre-fixing Numbered Rows With Cell Information


    Looks like you are doing a science fair project, hope you do well, take First Place, finish high in regionals, and get a scholarship! For the record, more PSI will make the ball bounce higher.

    I'm probaly wasting everyone's time as the suggestions above are viable, and more elegant than my techinque...

    I would use a hidden column, or a column on another sheet. Put numbers in the column, and ref to there. this will get the number. It doesn't even have to be a hidden sheet as you can set the print area before you print your data tables

    if your hidden column is "X" you could put this formula in row 2 and drag it down.

    And instead of typing the X2 in the formula bar you could just click on it.

    =$A$1&" "&X2

    Re: Formula

    What are you going to do with the results?
    Keep it as a perm record? Print it? Just look it up on demand?
    this could make a difference as to the approach...

    If all you want to do is look at it on demand, one record at a time...
    If you need to print it for each customer, each month...
    If you intend to create a perm record...but if you already have the perm record there would be no need to store the same information twice when you can always mine the data for your results.

    Re: Parse A Delimited String

    If the value in the cell is the result of a formula, that is you can see the results in the cell but you see a formula in the formula bar...

    You can do a copy and the paste it back in the same cells using "paste special" =values

    Now the formula has been replaced by its returned value.

    Re: Write This Formula

    I believe tomach is on the right track but has inadvertantly left out one set of brackets...

    The order of math opperations is * / before + -

    so =1000*A1+0.256 will multipy 1000*A1 and then add .256

    where 1000*(a1+.256) is what you are looking for


    I have two worksheets... "TRANSACTIONS" and "MANUAL ADJUSTMENTS"
    The two sheets have similar types of data but the columns are in different orders.

    Here is what I've got so far:

    I am happy to search for solutions (I've learned tons :) just reading other posts) but I don't seem to know what to search for...

    any help is very much appreciated!

    Re: Copy 2 Sheets And Close, variable Wbk Names

    WE got it working!:p I say "WE" because I could not have done it alone...Thank you so much!

    right after the dialogbox I inserted this line:
    NewWrkbkName = ActiveWorkbook.Name

    then after I copied the sheets I inserted this line:

    looks like this now:

    Re: Copy 2 Sheets And Close, variable Wbk Names

    Yes, thank you.

    This is going to work, it doesn't yet, but I'm sure it will.

    After it copies the sheets to:
    MyWrkbkName, MyWrkbkName is active and it does not close either

    I think if we could make NewWrkbkName active, we could close it (and not save changes)

    Could you suggest a line of code to activate the NewWrkbkName?

    Thanks to the help from this Forum and its members: I can now open a file and copy two sheets but need to close the second file...

    In wkb "C*commissions database.xls" I have this code which directs the user to open the current month's commissions. And with it open I can copy two sheets (always named the same). but now I need to close the current month's workbook, so I can continue working with the data in my permenant workbook.

    Thank you for taking the time to read this, hope you can give me a suggestion. Charles

    Re: Import Access into Excel has bad text format

    I had a similar problem with dates. The cells in Excell looked like dates but were not because when I chagned the format of the cells to a different date format, the cells not change to the new format.

    But if I select the cell and click in it, the format will change when the cell lost focus.

    I ran the code above and sure enough, the correct format showed up.
    Then just to be sure I pasted the same dates in 5 columns, formated them all different, ran the trimmer(), and they all changed to the correct format

    Re: Have User Browse For File To Copy Sheets

    Hey! Thanks!
    That was too easy.
    I did a search but instead of searching for "inputbox" this time I searched for "getopenfilename" and here is what I found...

    Sub getopenfilename()
    End Sub

    If you could see me now, you would see me smiling ear to ear.:p

    One of my wireless carriers provides commission data in Excel, each of their downloads has a specific name which refers a month’s commissions. I’ve thought about making a copy of each month’s Workbook when it is available with the name “current download.xls” and then work with it in a know directory.

    I’ve just recently discovered InputBox and am wondering if I can use it to ask the user to find (Browse for) the actual file they want, thus saving the step of creating and renaming a copy of the actual file.

    Since I like to work with the files as does another Director of my company, and the actual workings are handled on another management level I do not want to point to a specific directory.

    I’m thinking there is a way to copy the needed sheets without opening the file but I can save that for another question. First things first...thanks

    Here is some code I recorded then tweaked a little, sorry if it isn’t elegant…

    thanks for any suggestions