Posts by MrkFrrl

    Re: Convert Macros To Html?

    I Googled it. It looks the name has been changed to Spreadsheet Converter. Looks pretty nice. It also says at the website:


    You can test SpreadsheetServer today with your own spreadsheets. If you send us your spreadsheet, we will create a private account just for you and upload your spreadsheets.

    Quote from GrahamB

    I looked into this a few years ago, there is a program called ExcelEverywhere

    Re: Editing External Workbook With Code?

    Quote from sleepeeg3

    Once I open a new workbook, how do I make it the active workbook to have a function make changes to it?

    Re: Expanding Code To Multiple Cells

    The problem is that you've got an absolute reference (symbolized by the $ sign). You can change the substitute part of the problem easy enough:
    =SUBSTITUTE(A2," ","_")

    You'd then drag the bottom-right corner of the cell with the formula down to populate as many rows as you'd like.

    Still, I don't know if the Indirect function will work together too well with the Substitute function.

    Re: Convert Macros To Html?

    If you simply want it converted to HTML, Excel will do that for you with a save-as. But, of course, once in HTML format on the Internet, you can't update the spreadsheet-based HTML with remote data supplied to it via the Internet (only unless supplied via your LAN).

    I can't recommend it (because I've never tried it), but this program was listed on OzGrid's website.

    Re: Selection by Date and Time

    I'm a little confused about what you're asking here.

    I understand that you want to look at the first three time values, and then compare them to each other to determine which values among the three are the closest--right?

    In the example you gave, the first two values are closer than the second and third values.

    But where do we go from there? Are we then comparing the second, third, and fourth values, as the first choice might be inclusive to itself?

    Or should we be comparing the third, fourth and fifth values, since we've already used the first and second data values?

    Still, perhaps we should be examining the fourth, fifth, and sixth values, since we've already done a comparison with the first three values?

    If we're simply comparing every three rows, you could put a formula in every three rows. Say Cell A1 is the label "TM", Cell B1 is "B", and Cell C1 is "A"--and then you'd have your data immediately beneath that (in Cells A2, B2, and C2), in Cell D2, you could put a formula like this:
    =IF(A4-A3>A3-A2,"T1 and T2","T2 and T3")

    That would then compare three values at once. But I'm really not sure where to go next?

    What if the first and third values were closest, what to do with the second value? Do we then compare it to the fourth and fifth values?

    Also, do you disregard the end-product if there are only two rows of data left?

    What about one row at the end?

    Does T1, T2, T3, etc. always refer to values down the list (i.e., the fourth row down is T4)?

    What if the time-values become the same, or would that never occur?

    Re: Hyperlink - No Program Registered

    Quote from Solidtop

    Any ideas?

    You could try using the Shell command to open up Adobe, then have the macro follow the hyperlink. That MIGHT work?

    The problem might be related to your network security?

    I was able to follow a hyperlink in Excel to a PDF document, which opened it automatically, without any warning or problems.

    Re: Stop "file In Use" Dialog Box Except For Owner Of File.

    Quote from jefsboys

    My Goal: To have other users open a workbook on the network without having to click button to open it as read only.

    Go to the directory where the file is stored. RIGHT-click on the file. Select Properties - Attributes - and then select Read Only. It should then open as Read-Only for everyone. You'll need to de-select that option when altering the file.

    Re: Fill Table From Other Worksheet

    I think you're asking a bit much.

    The problem I see is that you've got two different sheets where the data resides, and you want to bring part of that data together on one sheet. This is not impossible, but it is a major pain, as doing such often involves running into unforeseen obstacles.

    Added to this problem, it doesn't appear that you've even tried to figure any of this out on your own, though it appears you're working at a place with plenty of reference resources.

    I'd recommend merging the sheet with your calculated formulas with that of your data in one way or another. (If concerned about messing up your data, you could just do a copy-paste on another sheet, then add the formulas and dragging them down the column--all the while having your macro recorder on while you do it.)

    This would make it considerably easier for you to then pick out the lines that are marked as overdue, and copy them to another worksheet. Or you could probably even set-up a Sort where the "Overdue" column is separated from the rest.

    Quote from Hello

    I've got TWO new problems.

    Re: Vba Increase Columns In Subtotal

    I'm at a loss why it won't accept a variable in this field. I see you found another thread that went unanswered a few years ago too.

    And I really don't mess with Subtotals enough to see why it doesn't work.


    1.) Could it be because the data in question are not entered in an array format yet at the time the Subtotal is to be calculated?

    2.) If #1 is no, will the number of columns vary by much--that is, could you write several macros, each with a different number of column rows in the Subtotal macro? You'd then calculate the number of columns, and call whichever Subtotal macro would be appropriate.

    3.) If #2 is too difficult, is there another method that the data could be calculated without using Subtotals and Arrays (e.g., Sumproduct, etc.)?

    Don't know what else to tell you.

    You could always ask Dave Hawley, if he's not too busy. I think he knows everything there is to know about Excel.

    If that doesn't work, ask John Walkenbach, who also knows everything.

    Let me know what you come up with, as I'm curious.

    Re: Clear Contents, Copy And Sort

    Would something like this work?

    If you're doing a paste-special, all formulas that are equal to nothing when converted to the values would then show a zero (whether you have them displayed or not). Here, I'm looking at the activeworksheet.

    I change all cells with formulas to their values. I then do a find/replace (i.e., "0" with nothing).

    Re: Find Function In A Vba Userform

    I think Find is strictly a worksheet function, but I could be mistaken.

    Why not try it and see what happens--instead of Cells.Find, something like TextBox.Find etc. invoked from the UserForm?

    If it works, let me know, as I'd be curious to find out if it's not limited to that.

    You might also try returning the contents of the listbox to a sheet and then using the Find function.

    Re: Date Range

    Actually, after reading your post again, it looks like you might try the [COLOR="Red"]DateDiff[/COLOR] Function.

    Re: Date Range

    I'd recommend having a standard date input, then you can format the output however you'd like:

    Format(date_in_between_inclusive, "YYMMDD") = end_date - start_date

    Re: Macro That Automatically Creates Txt File

    Turn on your macro recorder. Create a new workbook. Write the information mentioned in it. Do a file -> save as -> text, writing the name scope in the box. (I think tab-delimited "might" work better than Unicode text, because I once used a similar method to create a batch file to ping some IPs and had problems with one or the other.)

    Re: Adding Date

    A better way to select the date would be from a UserForm. When you go to insert a UserForm and bring up the Toolbox, right-click on it; and you'll see various options: DTPicker (date/time picker), MonthView, and Calendar.

    Using these, you prevent errors from Users.

    If you really must use an InputBox, try this:

    Then, I guess all you'd really do is need to compare your one variable (MonthGiven) to that of the other in the same format.

    Dim DateCompare$
    DateCompare = Sheets("Sheet2").Range("A12").value
    ' If DateCompare is a Date then compare MonthGiven in the same format to DateCompare
    If IsDate(DateCompare) = True and Format(MonthGiven, "mm/dd/yyyy") > Format(DateCompare, "mm/dd/yyyy") then
    ' do stuff
    End if

    Re: Linking To An Unknown Filename

    This would change all the links for one reference with a different folder-name (with the initial folder-name referenced in Cell A1 of the active sheet, which you could then use a Data Validation drop-down box) to that of another (with the new one referenced in cell A2). The paths and file names would obviously need to be modified.

    Re: Clear Contents, Copy And Sort

    Something like this for your different areas:

    Sub ClearACellsContentsIfEqualToZero()
    ' looking at the value in Sheet 2, cell A9, and deleting contents
    ' if it is equal to 0 (i.e., formula with no value)
        If Sheets("Sheet2").Range("A9").Value = 0 Then _
    End Sub

    Re: Linking To An Unknown Filename

    Yes, there probably is a way to do this. You'd set the the path with a partial variable referencing the unknown directory.

    But I'm not sure if that's what you want to do.

    Let me ask you this: Are you trying to do this because you have formulas in this workbook, and you want them updated to reflect the changes from the other workbook being moved (or a new workbook with a similar set-up that is referenced), which is created in a different directory?

    If that's the case, from the Menu, you'd go to Edit - Links - and then update your links to the new workbook.

    That would be once a month, compared to constantly being asked what directory you want to access every time you open the workbook.

    Re: Comparing Text