Posts by JimFuller1

    Re: Counting Cells Within Vba


    I'm not that good at VBA but, from looking at the code, I'd suspect the .double part because it looks like VBA didn't recognize it (or perhaps it would have capitalized it ".Double"). Using worksheet functions in VBA almost always comes down to getting the syntax right. Another trouble area is merged cells. You might try the same statement on cells that aren't merged and see what happens.


    Re: Microsoft Cdo For Nts 2.1 Is Missing


    I don't think I understand what you mean by "the spreadsheet does not compile without it". Is it possible you've encountered an error while writing VBA code or is it a formula in the spreadsheet that gives you the error? Can you post a small example so we can see better what you mean?


    Re: Re-trigger Execution Of User Defined Function (udf)


    The short answer is no. The UDF is not volatile. You may get a more in depth answer but this question keeps coming up periodically and the answer always turns out to be no.

    Your best bet may be to post a question about how to avoid the need for color counting in the first place. That's usually an easy thing to avoid.


    Re: Macro To Match Date Isn't Working


    The underlying format for the date cell is "General". That tells me that there is a high probability that Excel is treating the "Date" like it's text because of the " (double quotes). I would experiment with changing the formatting of the cells that hold "Dates" and make the format one of Excel's default date formats to see if you can get rid of the errors. I also wouldn't give up before I discovered what changed that caused the program to blow up like it did. The error handling is a good idea but, it doesn't tell you what caused the problem so it can be avoided altogether.

    Hope that helps,

    Re: Macro To Match Date Isn't Working


    It's probably just me but the thing in question seems to be the contents and the formatting of the cell 50 columns over. If you get the answer from the people willing to guess what's in that cell or the array of cells, whatever, then good. If you want to help those that want to help you, post the sheet.


    Re: Macro To Match Date Isn't Working



    The macro runs without error but never proceeds because it doesn't think the dates ever match when infact they do.

    I think I'm gonna need some proof of that. Can you post the file?


    Re: Check If Underlined Before Calculating


    I probably shouldn't say this at this late date so, I hope you take it with a grain of salt. It seems that the problem is in how you are gathering the information you need. For example, if you were to put a "1" next to the date to indicate it's a firm date, the problem gets super simple. Doesn't it?


    Re: Copy Rows Matching 2 Criteria


    I can't quite tell exactly what you're trying to do but, have you tried to use a Pivot Table report? Try that and if you still need something else, maybe you can post a small example?


    Re: Select Entire Row With A Named Variable


    Personally I belong to the School of No Selection when using VBA. Sometimes that prevents the very problem you are solving. Can you share what you plan to do to the row once selected (hypthetically) and let's see if it's easier to do without selecting?


    Re: Insert Skipped Record

    Quote from lyambor

    So we can enter a planning period for each teacher in excel and thwen upload the data to a different system

    How are you planning to gather the information so it can be entered into Excel? Perhaps you are thinking that the spreadsheet you want to sort and insert records can be the data gathering device? If so, I'd advise against it because that would be unnecessary. To gather the information, just send a blank sheet with the data columns clearly labeled to gather the information you need and incorporate it later into the different system.

    Re: Insert Skipped Record

    I sorted your sample file without any problems. Perhaps you are not picking the correct sort column. Would the lack of column headers be confusing you?

    Re: Insert Skipped Record

    A VBA solution will not be very difficult as long as there is preparation made.

    For example:
    Can you fully explain WHY you need a new row when there is no data?
    Can you fully predict WHEN a new row will need to be added?
    Can you describe this logic in easy to understand terms?
    Is your data ALWAYS laid out in the same way?

    These are the kinds of questions I usually see before a VBA solution works. When one or more of the questions is difficult to answer, the solution is usually more complicated.

    Take heart, VBA is not that difficult to read and understand I've found. It's more difficult to construct from scratch, but just reading it is pretty easy.

    Good luck,

    Re: Insert Skipped Record


    Without VBA, the short answer is no. Sorting is very easy though. Just select the data (include all columns and rows) and do [Data][Sort...] from the Excel menu using the concatenated column for the sort sequence. That takes care of getting the records into the right order. Once the records are in the right order, is it still tedious enough to insert rows? If yes, you will need a VBA solution.


    Re: Code Interferes With Others In Worksheet Change


    Make sure you can reduce all that code to the following format and you should be able to figure out the problem.





    Beware of conditions in Thing Number 1 that exit the sub.


    Re: Currency Format Treating Zero As Neutral

    Sure, just put what you want the format to be in the "third" position in the formatting description.

    +$#,##0.00;-$#,##0.00; _(* "$0.00"_)

    +$#,##0.00;-$#,##0.00; _(* "Zilch"_)
    Will display the word Zilch when the value is zero.