subtracting dates results in a big number when a date field is blank

Hello all,

How can I change the

Please Login or Register  to view this content.

formula in the "Number of Days" column to simply put a "-" (as text, not a number) for any cell in the "Number of Days" column whenever there's no date in the "End Date" column?

Please Login or Register  to view this content.

Currently, the code brings up numbers like "-39840"

2. How can I stop this formula from calculating once there's no more data in the "Start Date" column?

thanks much

Hi,

Change the formula in column C to

=IF(B2="","",B2-A2) then copy it down

Please Login or Register  to view this content.

Do you mean a column header

Can we change the code to look for the 3 columns by name rather than by column number? These columns will never be in a fixed position

If you do, it is possible - I did a code for something similar a few weeks ago
Will the headers always be in the same row e.g row 1

The code I provided previously shows how to find the column with the title you need:

Please Login or Register  to view this content.

All those lines do is go through each cell of row 1 and compare the value in the cell to the string you are searching for; namely, the "date opened".

You must note that if the string is not found, then the control counter ctrl is incremented by one to the next column.

When the string is found DateCol is set to the column number of the cell where the string was found and the loop terminates.

I used 256 in the loop only because xl2000 sheets only have 256 columns.

A better way (I didn't think of at the time) is as follows:

Please Login or Register  to view this content.

Hi,
Previously you said:

1. Can we change the code to look for the 3 columns by name rather than by column number? These columns will never be in a fixed position.

The code that I posted was only meant to show how you can capture the column number by comparing the data in the cell with the title you are looking for.

Since you said that you don't know which column the titles will be in, you will have to look at each cell in row one.

The line:

Set HeaderRow = Range("1:1")

essentially, loads a list of cell addresses into the variable HeaderRow. Range("1:1") says ALL cells in row 1 [it's equal to: Range("A1:IV1")].

The code:

Please Login or Register  to view this content.

is just a loop:
1. TestHeader is loaded with the info in the first cell of the list contained in HeaderRow.
2. This info/data is compared with the title you are looking for (held in Hdr2Find).
3. If the two match, then HdrColumn is loaded with the column number
This is the whole purpose of the loop. You now have the column number you need. In this case it finds "start date". You need to use this in the code that was provided by MudRaker and OldChippy.

For example, if the start date was in cell G1, then after the code is run, HdrColumn will contain 7 since column G is the seventh column. So, instead of using "Range(G1"), you would use Cells(1,7).

Since you want to search for the column number of three different titles, you will need to have three blocks of code at the top of your macro:

Please Login or Register  to view this content.

At this point you will have three variables, namely, HdrColumn1, HdrColumn2 & HdrColumn3 locating the data you need. But, you will need to use them elsewhere in your macro...

Q1: When you run your code in VBE do you have the Immediate Window and the Watches window open?
Q2: Do You know how to use Debug.Print commands?

The Immediate and Watch windows (View menu) are a couple of debugging tools. For instance, given the code loop below:

Please Login or Register  to view this content.

1. Set the cursor on the variable K, right click the mouse and select "Add Watch", then do the same for L and R. Hi-lite "R.Value" and add another watch.

Press F8 to step through the code one line at a time. Notice in the Watch Window that as the variables change, the results are shown in the Watch window.

Also notice that the variable R has a "+" sign to the left. Click it and you will see many, many properties that are available to range variables and most can be accessed with VBA code.

In the Immediate window, notice that every time the debug.Print line is executed it displays the current info stored in cell A1. You could have had a line such as: Debug.Print k;L;R.Value (it would print all values on the same line, though). To print each variable on its own line would require separate Debug.Print lines for each desired variable.

Also very useful is the setting of BreakPoints (see Help)

Option 1
Place the command Stop at the point you want the code to stop for you - this puts you into dubug mode

Option 2
Select or place cursur in the command you want to go into debug mode on and press F9
- F9 toggles breakpoint

Option 3
Click in the narrow border to the left of you command - this does the same as option 2

Below is a most basic answer:
NOTE: I had to change the parameter LookIn:=xlFormulas to xlValues to get your macro to run.

1. When you use the macro recorder, it records every key press and scroll adjustment (no matter how small). Recorded macros that don't do very much get very big, very fast and they are very hard to read and make heads or tails of.
2. In your code, you have XL search row one for the word drink, but if that word is not found, then the macro crashes, using VB you can test for success or failure.
3. The line after the search/find line selects cell F2. Anyone reading this would not know for sure if the word "drink" was found in cell F1 or not. XL simply recorded that you clicked F2 after the find. You could have clicked on any cell and typed in "water". For instance, with "drink" in N1:

Please Login or Register  to view this content.

Here, I clicked G28 and typed "water" and pressed enter, which moved the selection right one cell. Clearly, G28 and H20 had nothing to do with "Drink" in N1.

Most importantly, however, you must still edit the macro in order to capture the address of the found item for use later, in say a loop of some kind. I.e., you will still have to Dim some variables and enter statements like:

var1= ActiveCell.Address(0, 0)
or, var2 = ActiveCell.Column
or, var3 = ActiveCell.Row

But, for such a small macro the difference between the recorder and VB isn't much

Please Login or Register  to view this content.

You can replace the 2 (the row number) in the above line with some loop counter to move up/down in the column.
Also, you don't have to select the cell in order to write a value to it. The only way to get the macro recorder to enter a value in a cell is to click the cell and type the value since you are working directly in the worksheet.

Incidently if you have a lot of columns, then using .Find as the recorder did is generally quicker than cycling through each cell checking for a matching value.

The correct form of the .Find version would be as below:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If any of the HeaderCol variables are 0 then it means they weren't found.

No problem, you mentioned earlier that you often need to find a column by it's header text, so the function may be useful to you in the future