Posts by MrkFrrl

    Re: Ftp File Download Via Vba

    I'm not sure if this is quite what you want. If on a LAN, you could try to FileCopy it. You also might try, from the Menu, Data - Import External Data, and then Web Query. Another way might simply be to put in a hyperlink directly to the file you need to FTP within Excel and then have the program follow the link, setting it to time-out if nothing is retrieved after a specified time.

    Re: Breaking Links

    Check to see that some of the worksheets aren't protected. If so, turn off the protection first. Also, on the Menu, go to Format - Sheet and see if there is anything to Unhide, which may require turning off the protection. Also, make sure the file isn't read-only. This can be set internally within Excel, but can also be changed by right-clicking on the file's icon itself from properties as an additional way.

    Re: Cut And Paste A Row Based On Data In Column A

    Try using a loop, then going down one row at a time. You may have to use more than just the date function--perhaps a test--as sometimes things that you wouldn't consider being a date are regarded as such.

    ' go down one cell
    ActiveCell.Offset(1, 0).Range("A1").Select
    ' check to see if it's a date
    If IsDate(ActiveCell.Value) Then
    ' if a date concatenate your string with value
    End If
    Loop until activecell.value = ""

    Quote from [email protected]

    I want to move the rows that begin with date and cut and paste them at the end of the row that begins with a number.

    Re: Set Print Area Based On A Condition

    Instead of using [COLOR="Red"]<> ""[/COLOR] in the macro, try using [COLOR="red"]> 0[/COLOR] and see if that works.

    To avoid the message when printing, you might try inserting this in the code:

    ' first line of the macro:
    application.displayalerts = false
    ' last line of code:
    application.displayalerts = true

    Re: Vba Increase Columns In Subtotal

    So, you already tried this?

    Dim x as String ' in module header (or maybe it needs to be Dim x as Variant )
    x = "5, 6, 7, 8" ' at top of macro
    =array(x) ' within the context of your array macro

    And that did NOT work?


    Re: 38 Hour Week And 7.6 Hour Days

    Right click in the cell, select Format Cells from the list. Select the Tab named Number. On the left, select Custom. In the top box, put down your format as dd:hh:mm. That should work. Sometimes, in calculations, you may need to put the format-types in brackets to account for differences. So repeat this process again if that doesn't work with that instead: [dd]:hh:mm . Let me know if that works for you.

    Re: Command Button Link To Open Sheet(s)

    Turn on your macro recorder. Select a different sheet other than the one you're looking at. Stop your macro recorder. Now, from the Menu, go to View -> Toolbars -> Forms. Select the one with the button. Move your mouse where you want the button to appear. Left-click and hold down till you get the button to the desired size. Release. When you do, it will ask you which macro you'd like to assign it to. Assign it to the one you just created (Macro1, probably). When this is done, the button should still be highlighted. Go and change the text now to whatever you want. You're done! Repeat as needed. To view what you've recorded in the macro editor, simultaneously press ALT-F11.

    I'm suggesting in the above that you use Form buttons, as they're easier to work with. You can use Command Buttons if you'd prefer. If invoking a Private macro, that may be the way to go.

    Re: To It Empty With =if

    I don't understand your question. Please rephrase it.

    If you want the syntax of the IF-formula, click in the cell in which you'd like it to appear, concurrently press the SHIFT and F3 keys, bringing up the Insert Function box, then search for the function IF.

    Re: Vba Increase Columns In Subtotal

    I don't generally mess around with subtotals too much. Instead of using the various columns listed, can you use Columns("E:H") in the macro?

    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(Columns("E:H")) _
            , Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    If that works (and I don't know if it will for certain), all you'd have to do is then determine where is the first column, which you should know since you bring in the data, and where is the last column. Then, instead of - Columns("E:H") - you'd use use variables in their place, such as Columns(x & ":" & y)

    So, providing you know the first column (which should be A), you could then use something like this to determine the last column:

    Another idea might be to use the -


    and go backwards from there.

    You could also use a loop and generate the (5, 6, 7, 8) string, adding a number, comma, space for each entry found in the array if necessary.

    Re: Hyperlink Within Workbook Then Autozoom

    Copy your formulas with your macro recorder, if you need them to be variables at times. Then, while recording another macro, do a copy/paste-special/values to make your hyperlinks static. This would then be used immediately before the follow-hyperlinks macro is invoked.


    Re: Speeding Update Process Of A Database

    If you can avoid arrays and the sumproduct, by all means, do so.

    If you can't, try inserting code to copy/paste-special/value immediately after the sumproduct formula is added to replace it within your loop (if that will still work for you); and then see if that doesn't speed things up a little, as at least the spreadsheet won't have to recalculate all those values constantly. Will be curious to see.


    Quote from Upside

    I have read somewhere that both arrays and SUMPRODUCT are slow to calculate

    Re: Set Print Area Based On A Condition

    Hi Jeff,

    I tried it, and it worked for me when I populated different areas of the A column.

    Are you sure that the cells of your TQUOTE worksheet are completely empty--no spaces, 0s, or hidden formulas? You can do a quick check (though a space might not show up) with a message box:

    msgbox Worksheets("TQUOTE").Range("A254").Value

    You can also go into the VBE (ALT-F11), highlight the values, select Debug from the Menu, and Quick Watch to find out the values that the variable takes. Then, with your cursor inside the macro, hit the key F8 repeatedly to see the effect on the variable(s) by advancing one line at a time.

    I don't think it should matter about the repeating rows.

    The only change I'd recommend is perhaps moving the print-statement, since you probably don't want it to send a print-job if the print-area is set to empty.


    Quote from jandrew

    Hi Mrk and thank you for your response. I tried editing your code to adjust my print area based on specified cells being empty. For some reason the print area remains at A18 : E286 even when all specified cells are empty. This is how i modified your code. Also does it matter that I have the page set up to have several rows repeat at the top of each page?

    Re: Vba - Context Sensitive Auto-complete Gone Missing...

    Have someone save a good blank workbook as an Excel Template (.xlt) file named Book.xlt . Save that file and overwrite the existing file in the folder Xlstart (use Search to find it, if you have problems), and see if that doesn't help. I couldn't upload a file I saved as this for one reason or another.

    Try a reboot again too.

    You might try a repair from the Control Panel too. Find Excel (or MS Office), click on View Info, and there "should" be a Repair option there too.

    If that doesn't work, you may have to change the registry entries. But I'd only try that after everything else is exhausted, as it could cause more problems.

    If absolutely necessary, /regserver is the command line switch to make Excel re-register itself.

    Re: Vba - Context Sensitive Auto-complete Gone Missing...

    I thought Auto List Members simply showed VBA available for the code you're writing, so that if you type "ThisWorkbook." (without quotes) in the VBE, it will give you a list of choices available that you can choose (e.g., "Save").

    Quote from filo65

    in vbe go to tools->options->Editor and check auto List Members

    Re: Vba - Context Sensitive Auto-complete Gone Missing...

    ... If that doesn't work, try copying out all your code to Notebook as a text-file, save your workbook, then paste it back into your workbook and save-again. Excel will sometimes retain information related to errors and act in strange ways, despite having perfectly good code that's worked before. I've seen it do it with regular index-formulas too for no reason, not just VBA.

    Quote from JA1

    I'm trying that.

    Re: Vba - Context Sensitive Auto-complete Gone Missing...

    By default, Excel VBA should be case-sensitive unless you use Option Compare Text .

    Go to the Excel Help menu, then Detect and Repair , and see if that clears it up.

    If that doesn't work, try copying out all your code to Notebook as a text-file, save your workbook, then paste it back into your workbook and save-again.