Posts by Phlu

    Re: Exit or Skip Loop

    For those curious, I did in fact find a way to accomplish what I was looking for. It's too late for me to implement the solution for this particular macro, but I found that you can skip through a For...Next loop using labels and GoTo. My objective was to skip down to "Next c" without exiting the For...Next loop altogether. An example follows:

    iLastRow and iFirstRow are integers defined earlier that represent row numbers, and rngOut is a user-specified cell that defines where the macro writes data.

    I'm pleased to say that this works quite well, and my only worry is that inserting a GoTo statement in the middle of an If statement might mean that the If statement never closes properly until the subroutine runs its course. This hasn't been a problem yet, though. Hope that helps anyone who wants to do something similar.

    Re: Dim Long Quirk

    Quote from turtle44

    Your dim statement in Demo1 is declaring zxc as long; qwe and asd are variant.

    Long variables are integers. I think you are confusing Long with Double.

    By golly, you're right! :eureka: Good call. Thanks for clearing that up.

    Hello Ozgrid!

    I've occasionally had trouble with Long variables mysteriously rounding off their values as if they were integers, and I've noticed that others have had this problem, too. Though still I do not know exactly why VBA does this, I believe that I've discovered the circumstances than cause this to come about. It all traces back to when you define the variables.


    Dim qwe, asd, zxc As Long


    Dim qwe As Long, asd As Long, zxc As Long


    Dim qwe As Long
    Dim asd As Long
    Dim zxc As Long

    What I've found is that when a Long variable is given the value of a cell, it will round that value to the nearest integer as long as that variable is adjacent to "As Long" when it was defined. That is, of all of the examples shown above, only "qwe" and "asd" from Demo1 will carry the full decimal value recorded. The rest will be integers.

    I attached a file with three demo macros that demonstrate what I mean. Does anyone know what's going on? Has anyone else noticed this?

    Re: Use Default Input Box

    Thanks for the info, Andy. I was hoping we'd have access to an inputbox that's a little more advanced/streamlined than the existing one, but I guess not.

    Quote from ByTheCringe2

    Did a good job, actually. We would have edited that title exactly as you have it now - well, I would, anyway...

    Thanks! Nothing personal... >.>

    PS--oh, and congrats on 10,000 posts, Andy!

    Re: Use Default Input Box

    Heh, that is true. Thanks for the post, norie. I do understand the difference between the method and the function "InputBox." I guess I'm wondering if there's a way to utilize the inputbox that Microsoft used for nearly all of its user input requests, or if what I'm looking for has to be accomplished through a custom userform (not sure on the vocab here).

    ...or if I should simply satisfy myself with what I already have. ;)

    Hello Ozgrid!

    You guys are always so helpful, so hopefully you can help me with this. I wanted to title this "Using Excel's standard input box" but the thread title police wouldn't allow it. Basically, I'm coding a macro that needs the user to specify a range, and I'd like to know if it is possible to use Excel's standard input box format, rather than VBA's standard inputbox.

    My reason for asking is that I'd like to use a format more aesthetic and more familiar to the user, rather than the bulky thing that VBA puts out. If you're not sure what I'm referring to by "Excel's standard format," run the Chart Wizard, go to Step 2, and click on the button that allows you to select a range. That's the inputbox style I'm referring to. Can I use it for my own macros without creating it from scratch?

    Here's my code as it stands using the VBA format.

    Set rngMagSusActual = Application.InputBox("Please confirm the Magsus output location. " & _
            "Click on the column header cell.", "User input needed", rngMagSusGuess.Address, 200, 200, , , 0 + 8)

    Thanks in advance!

    Re: Autofit Height For Merged Cells

    Sounds like you need to use autofit. Put in the appropriate cell address you want to autofit where CellNameHere is.


    Hope that helps

    Scratch that. I forgot that Autofit doesn't quite work with merged rows. Sorry!

    Re: Sorting Data Range With Formulas

    If you don't want the blank rows to be sorted to the top, what do you want Excel to do with them if they can't be deleted either? Are they simply blank cells waiting for data to be entered later?

    Re: Text Box Values On Several Worksheets

    This seemed to work for me:

    There's no need to declare "ws" as a worksheet. I believe that it's a generic XLVBA object, though I could be wrong on that note. Once I removed that line it recognized the ws.TextBox objects.

    However, it should be noted that this macro will still come up with error 438 if it encounters a worksheet that doesn't have three textboxes with the names TextBox1, TextBox2, and TextBox3. There's a workaround for this if that's a problem, but my error handling skills are rudimentary at best so you'll have more success browsing the forums for an answer.

    Re: Convert Serial Date Time To General Format

    Try this custom format instead:

    mm/dd/yy hh:mm:ss.0

    The extra ".0" at the end will allow the decimal values of the seconds to display. Note this custom format will cause one-digit month/day/hour values to have "0" placeholders (e.g., 1:00am May 4th will appear as "05/04/07 01:00:00.0"). If you don't want that then change it to:

    m/d/yy h:m:s.0

    Hope that helps.

    edit: add extra zeroes to the end of the custom formula if you want better precision than tenths.

    Re: Advance For Each Loop Early

    Ok, I accomplished one objective (skipping an entry if a more recent one exists) and the other objective (skipping through the loop) was ignored by bracketing the read/write process with an If statement. However, the end result is rather bulky and it seems like it can be done in a far more efficient manner. I was thinking that perhaps an AdvancedFilter could be used instead of a loop, but I can't figure out how to use that particular method for my application.

    Attached are reduced copies of the workbook and the course worksheet. I am still interested to know if there is a way to skip over a For...Next loop. Any help or advice would be greatly appreciated.

    Hello Ozgrid! I have a For Each...Next loop to read and write data from one workbook to another. Now, it's possible but unlikely that a certain "wellindex" (as defined by the Column A value) occurs more than once in the source spreadsheet, and only the most recent value should be used.

    And now to the actual question. Currently my macro runs a check to see if the current cell's wellindex has already been used, and if so, then it warns the user of potential double-entry. What I'd like to have it do is have it check whether the current cell's wellindex has duplicates further down (e.g., using a "findnext" method), and if so, then skip to the next cell in the loop. I know that I can exit a loop with "Exit For" but I don't know how to have it go straight to "Next c" without embedding everything in an If statement.

    Re: Format By Page Break

    Thanks! I followed that link, which led me to a whole set of excel helpfiles involving pagebreak methods and objects. Here's what I ended up with.

    "ID" is the leftmost header that rests in a frozen pane so that the header prints on every page, which is why I only need borders on the bottom of every page. Hope that helps anyone else attempting something similar.

    Thanks again!


    Hello Ozgrid!

    I've a macro that pulls data from another workbook and inserts rows throughout the current worksheet. As a result, the borders drawn on that table for aesthetics at page breaks no longer rest at the bottom of the page once the macro has run. The user has to then manually remove the old double-border and then format it in the appropriate location. This gets to be a hassle when the worksheet is 10+ pages and the rest of the process is already automated.

    So, my question is whether it's possible to detect page breaks using VBA, then have it format the row above that page break. I attached a partial sample of the workbook after the macro has been run. Thanks!

    Re: Define Varying Range

    It all makes sense now! That certainly explains a few of the error messages I've gotten while trying to manipulate Range objects in various functions: I kept saving them as strings. Many thanks to you all.

    Re: Reformat A Date String In Vba

    Quote from Sicarii

    this help at all?

    Selection.NumberFormat = "m/d/yy;@"

    That seems to change the date format of the selected cell, when what I was trying to change is the value of a string variable. Out of curiosity, though, what is the ";@" suffix there for?

    Re: Reformat A Date String In Vba

    That works perfectly! I changed it slightly to remove the "0" placeholder but it's pretty much the same thing. Thank you! One-liners are always preferable to blocks of code. :)

    sourcedate = Format(DateValue(sourcedate),"M/D/YYYY")

    Re: Define Varying Range

    Yes, actually. I'd assumed that the "," separator designated that it was a list, rather than the brackets of a range. How would it change if there was another range "RV3" that I also wanted to select in addition to RV1 and RV2 without including all cells in between?

    As a side note, why did you add "Set" to the code? I'm not clear on when it should/shouldn't be used.