Re: Counting Cells Within Vba
Dwarf,
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.
Jim
Re: Microsoft Cdo For Nts 2.1 Is Missing
Chris,
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?
Jim
Re: Re-trigger Execution Of User Defined Function (udf)
dwkocsis,
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.
Jim
Re: Macro To Match Date Isn't Working
GuyGadois,
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,
Jim
Re: Macro To Match Date Isn't Working
Just convert to values (copy/paste special/values). It won't change the formatting and we get to.... oh never mind, I give up. Good luck.
Re: Macro To Match Date Isn't Working
GuyGadois,
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.
Jim
Re: Macro To Match Date Isn't Working
GuyGadois,
You know you can just peel off the part of the sheet that has the date and send that little itty bit of the data so we can see...
Jim
Re: Macro To Match Date Isn't Working
GuyGadois,
QuoteThe 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?
Jim
Re: Check If Underlined Before Calculating
DeafnDumb,
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?
FWIW,
Jim
Re: Timeline With Automatic Label Updates - Fishbone Style
This one has no bad names that may cause problems either.
Re: Copy Rows Matching 2 Criteria
albatros81,
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?
Jim
Re: Select Entire Row With A Named Variable
coolhandphil,
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?
Jim
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?
Etc.
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,
Jim
Re: Insert Skipped Record
lyambor,
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.
Jim
Re: Auto Updating
cbanks,
Try opening the February worksheet(s) before you do the search and replace.
Jim
Re: Code Interferes With Others In Worksheet Change
ozastudent,
Make sure you can reduce all that code to the following format and you should be able to figure out the problem.
IF - ANYTHING IN COLUMN A CHANGES
DO THING NUMBER 1
DO THING NUMBER 2
ELSE - EXIT SUB
Beware of conditions in Thing Number 1 that exit the sub.
Jim
Re: Change Cell B, By Formula In Cell A
Macro's,
A little unclear which cell should do what.
B1: =If(A2="Green","yes","no")
Enter the formula into cell B1 to see the results based on what is in A2.
Jim
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.
Jim
