Excel User Defined Formula

[eventhorizon]

So I'm trying to help a dude out at work and make a formula that will accomplish the following:

Compare a date to an array of dates on another sheet. If there is a match AND that date is today, I want it to return True. Else, it should return false.

I've tried the following two functions but neither one of them works...I get the same error with both. It claims the second parameter that is passed into the function is the wrong type and thus i get #VALUE! errors.

Function MatchDate(TPRrange As range, TPRDate As range) As Boolean
Application.Volatile (True)
Dim c
For Each c In ActiveWorkbook.range(TPRrange)
If c.Value = range(TPRDate) Then MatchDate = True
Exit For
Next
MatchDate = False
End Function


Function MatchDate2(TPRrange As range, TPRDate As Date) As Boolean
Application.Volatile (True)
Dim Index As Variant

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
Index = Application.Match(CLng(TPRDate), range(TPRrange), 0)

' Display the results.
If IsError(Index) Then
MatchDate2 = True
Else
MatchDate2 = False
End If
End Function

[bassplayinMacFiend]

Dude, you don't even need VBA for that. Just use the following:

= IF ( COUNTIF(check:range,today()) >0,true,false)

[bassplayinMacFiend]

Also, in your first code fragment, you'll always return false because you set MatchDate = True, then you Exit the For loop.

What's the first statement after the For loop? You guessed it, MatchDate = False. Therefore, you'll always get a False result from this function.

[Edit]
I could be wrong with what I said in this post. If the MatchDate = True is working as a Return statement (as in return MatchDate; which is the syntax in C/C++/Java), then the True value would be returned.

[bassplayinMacFiend]

Also, you might want to tighten up your variable declarations. The Variant variable type should only be used as a last resort as it is horrible for code readability.

[eventhorizon]

Thanks man. I hadn't used VBA in years and those two things were kinda just copied and slightly modified from other web sites. I'm still confused as to why it didn't like the second parameter passed to it but that doesn't matter now. The COUNTIF works great. Thanks again.

[bassplayinMacFiend]

In the first function, TPRDate is defined as a range. I'm not sure that's what you need. The second function doesn't really make sense to me out of context. The Index = assigned along with saying that a nonzero value is an error looks weird to me as well.

I'm glad the COUNTIF is working for you though. :) My Excel kung-fu is very strong. :)

[AWR]

bpmf,

While you're fielding Excel questions like Bruce Lee, I wonder if you could help me.

I can't seem to SUM time. I keep track of my runs and bikes and would like to have a running tally of my cumulative time. I can't seem to get 1:25:00 and 34:52 to add up to 1:59:52.

Any ideas? Cheers.

[bassplayinMacFiend]

You have to enter the 34:52 as 00:34:52 in order for Excel to recognize the entry as hh:mm:ss. Once you do this the SUM function will accurately total the times as 1:59:52.

If you just enter 34:52, Excel will take this as 34 hours and 52 minutes since 12/31/1899 12:00:00 AM (on a PC, I think Macs use a different start date but I could be wrong) which gives you a result of "1/1/1900 10:52:00 AM", and you don't want that.

[edit]
Macs start on 1/1/1904, so 34:52 translates to 1/2/1904 10:52AM.

[Banana]

Do you know of a way to program a certain column so that whenever I type in one cell for a month, only need to input the date of the month to get the date?

For example, I type in Month: Feburary then in that column, I only need to type 1, 1, 1, 3, 5, 7, 7, then it'd read as 2/1/06, 2/1/06, 2/1/06, 2/3/06, 2/5/06, 2/7/06, 2/7/06.

[bassplayinMacFiend]

You would need two columns in order to do what you ask without using a macro. In cell A1, enter 2/1/2006 (February 1st, 2006). Then, click Format -> custom, then enter "mmmm" (without quotes) as the custom format. Now all you see is the word February in cell A1.

Now in cells A2 - through A??, enter the day like you did in your list above (1,1,1,3,etc.,). Then in cell B2, use the following formula:

= DATE(2006,MONTH($A$1),A2)

This formula is in the format DATE(y,m,d). For your example, I hardcoded 2006, although you could take this from the date in cell A1 by modifying the above formula to read:

= DATE(YEAR($A$1),MONTH($A$1),A2)

Moving on, the MONTH($A$1) takes the month number from what you entered in cell A1. The dollar ($) signs create an absolute cell reference. Simply put, no matter where you copy/paste this formula to, the cell in the MONTH formula will always be A1.

The A2 reference in the above formula pulls the day from the cell where you entered your data (1,1,1,3,5,7,etc.,).

If you want a macro that you can run on a selected range of cells instead of having two columns (and probably hiding the column where you type in your data), let me know. I could whip one up if you really wanted one.

[edit]
Oooh, neat! You can press CTRL + ; to put today's date into the currently selected cell! Learn something new everyday. :)

[Banana]

Great, thanks!

I would prefer hardcode over macros; they're nothing but trouble in my experience. :)

Much obliged.

[bassplayinMacFiend]

Great, thanks!

I would prefer hardcode over macros; they're nothing but trouble in my experience. :)

Much obliged.

Having the formulas right on the spreadsheet makes it much easier to troubleshoot as well.

None of this "What the hell does activesheet.range(OMG).currentregion refer to at this point in code?" :)

[AWR]

You have to enter the 34:52 as 00:34:52 in order for Excel to recognize the entry as hh:mm:ss. Once you do this the SUM function will accurately total the times as 1:59:52.

If you just enter 34:52, Excel will take this as 34 hours and 52 minutes since 12/31/1899 12:00:00 AM (on a PC, I think Macs use a different start date but I could be wrong) which gives you a result of "1/1/1900 10:52:00 AM", and you don't want that.

Cheers mon ami.

[bassplayinMacFiend]

C'mon, keep 'em comin'. :)

[Banana]

Hmm, okay.

Let's see you get Excel to divide by zero. :devil:

[Mac+]

Hey bpMF, I love this thread. :)

I have one for you:

Date - No. of classes - Description - Income - Expenses - Balance

I have the calculations working perfectly, so this not - strictly speaking - a formula based problem.

If I type in "piano lesson" or "English" for example in the 'Description' column, the spreadsheet will calculate and input the correct amount (depending on the number in the 'No. of classes' column) in the 'Income' column automatically for me. No biggie. However, when I type in a description of an expense, I want the whole line to be formatted as red text. Obviously I set the 'Expense' column to red text, but I would also like the 'Date' and 'Description' cells in the same row to also appear as red, when they recognise that there is an amount in the corresponding 'Expense' column.

Oh, and for the record, this is one piece of software from MS that I absolutely love. I haven't seen how tables perform in iWork, but if they aren't up to scratch my copy of Excel for OS X will make the transition to my new Mac, working under Rosetta if it has to!

[bassplayinMacFiend]

What you're looking for is called "Conditional Formatting". This is used to make negative numbers show up as red, for example. What we'll do is set one row of data with the conditional format, then copy the cells down the spreadsheet.

First, highlight your row of data. Then on the menu go Format -> Conditional Formatting. In the window that pops up, change the drop down for Condition 1 from "Cell Value Is" to "Formula Is". In the formula field, type = C2 = "Expense" where C2 is the cell where you type the word Expense. This is case sensitive so remember this.

Once you've set the formula, click the Format... button, then set Font Color to Red and whatever font options you might want. Then click OK to exit the Font window, and click OK again to exit the Conditional Formatting window.

Now you can copy these cells down your spreadsheet and the conditional formatting will follow. If you already have values and you just want to apply the Conditional Formatting, you can use the Format Painter tool (paintbrush icon on the formatting toolbar) to add the Conditional Formatting to existing cells without disturbing any values or formulas.

[Edit]
I may have misread your post. Reading it again, it looks like you want everything to turn red once you've entered something in the Expense column. Just alter the formula in the Conditional Formatting window to read = C2 <> 0 . In this example, C2 would be the cell where you type the actual expense amount.

Then whenever you enter anything into the expense column, that row will turn red.

[bassplayinMacFiend]

Hmm, okay.

Let's see you get Excel to divide by zero. :devil:

You know, it's always bugged me that you can do 0/x = 0, but
x/0 = #DIV/0! Why is that?!? Who made up that cockamamie rule?!?

I'll tackle this once I prove the set of Real numbers is finite. :smokey:

[Mac+]

bpMF - Yeah, conditional formatting is what I'm trying to achieve - but for the whole row. I already had conditional formatting working for the 'Expense' column - I wrote that I had the 'Expense' column set to red, but it was conditionally formatted.

When you wrote highlight the whole row and apply a conditional format, I thought cool. I didn't know that you could apply a conditional format to a whole row. However, the cell value (in my case the 'Expense' cell - as long as it is >0) gets shifted for each cell.

For example, let's say:
Week - Date - No. of Classes - Description - Income - Expense - Balance are columns A to G respectively.

I want data from A to E appear as red text if the data in cell F ('Expense') is greater than 0 (or not empty ... whatever works). However, it works for the A cell, then for the B cell it is automatically extended to check with the G cell, C cell checks with H cell. Do you get what I mean? Can I get cells A through E to all reference against the F cell in one command? Or, do I have to apply that rule separately for all cells ... and copy/special paste the conditional formatting to all other cells in my spreadsheet?

Btw - thanks for alerting me to the fact that I could do conditional formatting on formulas. I previously only based it on cell values. :cool:

[Banana]

I'll tackle this once I prove the set of Real numbers is finite. :smokey:

Why, didn't you know?

It's easy- the set of Real number is less than the set of complex numbers, therefore it must be finite. :D

The end of month's coming up and I may decide to fix up the glitches I've been seeing in my monthly report once and for all when I work it. Will post when I have specific questions.

Again, thanks! :)

[AWR]

OK, I've got SUCH a softball, I am slightly embarrassed to ask (but as I'm in my very late 30s, I've put all of that behind me):

How do I set up a date column that has the day and the date and would allow me to drag it down autofill like.

For example: Friday, 24 March

[bassplayinMacFiend]

bpMF - Yeah, conditional formatting is what I'm trying to achieve - but for the whole row. I already had conditional formatting working for the 'Expense' column - I wrote that I had the 'Expense' column set to red, but it was conditionally formatted.

When you wrote highlight the whole row and apply a conditional format, I thought cool. I didn't know that you could apply a conditional format to a whole row. However, the cell value (in my case the 'Expense' cell - as long as it is >0) gets shifted for each cell.

For example, let's say:
Week - Date - No. of Classes - Description - Income - Expense - Balance are columns A to G respectively.

I want data from A to E appear as red text if the data in cell F ('Expense') is greater than 0 (or not empty ... whatever works). However, it works for the A cell, then for the B cell it is automatically extended to check with the G cell, C cell checks with H cell. Do you get what I mean? Can I get cells A through E to all reference against the F cell in one command? Or, do I have to apply that rule separately for all cells ... and copy/special paste the conditional formatting to all other cells in my spreadsheet?

Btw - thanks for alerting me to the fact that I could do conditional formatting on formulas. I previously only based it on cell values. :cool:

Mac+,

Your solution is simple. Reference my previous example, and change = C2 <> 0 to =$C2 <> 0 .

What this does is force column C to be absolute. No matter where you copy this formula on row 2, it'll always reference column C. BUT when you copy this formula to new rows, it'll reference the correct row because the 2 is not absolute (doesn't have a dollar ($) sign in front of it).

[bassplayinMacFiend]

OK, I've got SUCH a softball, I am slightly embarrassed to ask (but as I'm in my very late 30s, I've put all of that behind me):

How do I set up a date column that has the day and the date and would allow me to drag it down autofill like.

For example: Friday, 24 March

It depends on whether you want the date to increment or not. If you want the date to increment, type in 3/24/2006 (or 24/3/2006 for you non-USers), then click on the lower right hand corner and drag down. When you release the mouse button, the selected cells will be filled.

If you want all the same date, then you want to copy/paste.

If there's more to your question that I'm missing, let me know.

[AWR]

Thanks for the reply bpmf.

When I enter 24/3/2006 and click the righthand corner and drag down I get 24/3/2007, 24/3/2008, etc.

And part of my original query was that I wanted to have the day (Monday, Tuesday...) in there too if posssible (or do I need a seperate column.

[bassplayinMacFiend]

The day is just formatting. Select one of your date cells. On the Format menu click Cells... . On the window that opens, click date on the left side under Category. The right side should then show an option to display the full date including day. If for some reason this option is not available, then click Custom under Category and type dddd, mmmm, yyyy in the Type: field. That'll get you what you need.

When I drag the date down in Excel, it increments the day, not the year for me. If this remains a problem for you, then do this:

Let's say your first date is in cell A1. In cell A2, type = A1 + 1 . Then copy this formula down. If the formatting isn't correct, select cell A1, then click on the paintbrush icon in the toolbar (Format Painter) and "paint" the format on the cells below A1.

Then if you just want to retain the dates (lose the formula), highlight the cells with the = Ax + 1 then select Copy. Then select Paste Special... (either on the Edit menu or via right-click) and select the Values option (V is the keyboard shortcut when the Paste Special window is open). This will replace your formula with hardcoded date values.

[AWR]

Thanks. For some unclear reason (like Word Numbering schemes :grumble: :lol: ) it filled by day not year this time. And the day name formatting was as simple as pie.

Thanks a lot.

[bassplayinMacFiend]

I'm happy to help. Some people here are PHP geniuses, some are CSS/HTML geniuses, one is some guy named Brad (is this guy a robot? ;) ) who seems to answer multitiudes of questions, and of course, there's the Photoshop gurus.

I happen to have about 10 years of daily use (including VBA, DAO/ADO to Access, etc.) of Excel where I spend a good 90% of each day working with it. These questions are a breath of fresh air so to speak as it lets me pass on all this Excel stuff rolling around in my head. :)

I'd be totally cool with an Excel Q & A with bpMF, you've got Questions, he's got Formulas thread if there was enough interest to warrant one.

[Mac+]

Mac+,

Your solution is simple. Reference my previous example, and change = C2 <> 0 to =$C2 <> 0 .

What this does is force column C to be absolute. No matter where you copy this formula on row 2, it'll always reference column C. BUT when you copy this formula to new rows, it'll reference the correct row because the 2 is not absolute (doesn't have a dollar ($) sign in front of it).Absolute with a $ sign ... I love learning new stuff like this! And, I love Excel - but obviously don't spend as much of my day in front of it as you. ;)

Thanks again bpMF. Cheers! :)

[Banana]

Does anyone else have problems with copying and pasting formulas between spreadsheets? Every time I try to copy a formula, I cannot use Paste Special, and pasting it only changes the original cell that I copied. :err:

Stupid MS.

[bassplayinMacFiend]

Could you explain your problem a little more clearly?

[Banana]

Ok-

I have several identical spreadsheet (or is it called workbook), for each month of the year. When I need a formula, I go to previous month's spreadsheet, pick a cell, copy it, then paste it in other spreadsheet. The result? The cell I picked to copy from has its value changed. I ended up having to manually type in formula. Really infuritating.

[bassplayinMacFiend]

Try this. Instead of copying the cell from the spreadsheet, highlight the formula in the formula bar (above the spreadsheet) and copy that. Then click on the cell you want to paste the formula into and click in the formula bar then click paste.

If you're saying that you copy a cell, then paste the cell into a new worksheet (tab), then the output in the original cell is modified then that says to me there could be an error/circular reference in your formula.