Subtract values from cells relative to ActiveCell

[Rob*]

Hi.

Go stuck on this, what I thought was simple to do, but how can I subtract between two value relative to the active cell?
Let's say i'm in B2 I want to put the value from A2-A1.

I tried this but get some sort of Error 13 "Incompatible types" (bad translation??)

Please Login or Register  to view this content.

Hope you understand what I want here.

[Norie]

Do either A2 or A1 contain text or error values?

[Rob*]

Do either A2 or A1 contain text or error values?

nope, timestamps with milliseconds.
Like:
A1: 14:26:24,605
A2: 14:26:25,637

[Rob*]

It works well if i just in cell B2 write function: =A2-A1 it subtract as I want to.
But I don't want the formula in the cell just the actual value: 00:00:01,032

[ragulduy]

I'd guess the problem is to do with variable types, you could try setting variables as date types and then assigning the values in the cells to the variables and doing the subtraction. I haven't being able to get this to work exactly right though. Probably the easiest thing is to write the function in the cell and then copy and pastespecial as a value.

[Rob*]

I tried

Please Login or Register  to view this content.

And variants of:

Please Login or Register  to view this content.

without any success.

Was also thinking about the PasteSpecial as a workaround.

[Norie]

The only way I can cause a type mismatch is if there is text or an error in either or both cells.

Works fine with the values you posted too, as long as the worksheet they are on is active and the ActiveCell is B2.

Actually, why are you using ActiveCell?

[ragulduy]

http://www.excelforum.com/excel-new-...llisecond.html

That seems to be the same problem - you could try the solution there but to be honest I would stick with the pastespecial.

[Rob*]

Actually, why are you using ActiveCell?

Don't know. Mostly, I think because it's where I end up after finding first empty row.
And second because I don't know what else to use?

[Norie]

Rob

Can you check if the times are being recognised?

You can do that with a formula like this.

Please Login or Register  to view this content.

This will return TRUE if Excel is recognising the values as time, FALSE otherwise.

[Rob*]

Rob

Can you check if the times are being recognised?

You can do that with a formula like this.

Please Login or Register  to view this content.

This will return TRUE if Excel is recognising the values as time, FALSE otherwise.

It return: "FALSE"

Is there any solution for that?

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Rob*]

So I'm working on a lap timer thingie.
So whenever the one i'm tracking passes the finish line I just hit Lap button.
That way I can log the lap times.

I attached my solution.

The workaround from my original question with PasteSpecial is included and I just set text color to white in column A and B to hide it.

[Norie]

This will return a string.

Please Login or Register  to view this content.

The rest of the code is pretty confusing with all the selecting, offsettin etc, in fact it's making me dizzy trying to figure out what's happening.

What, in words, should the code do?

You mention lap times, but surely you would need a start time for that.

I changed the code to this and changed the declaration of LapTime to Double.

Please Login or Register  to view this content.

That part appears to work to some extent

[Rob*]

I know the code ain't too clean!
All and all the code should just make timestamps and calculate the time between to set that as a lap time. Each new lap overmost, e.g. in C3. And also name that lap.

I have a start time:
As you hit the Lap-button first time you see the text "Complete Lap 1" in order to wait for the first lap to be completed, then just to hit the Lap-button. After that it's just to hit Lap-button every lap around for new times.
I will look at the code you posted here Norie, thanks for the time, helping me out here.

[Rob*]

I changed the code to this and changed the declaration of LapTime to Double.

What does this do? I'm a bit confused to all these declarations...

[Norie]

In Excel times are numeric, you had declared LapTime as a String.

[Rob*]

In Excel times are numeric, you had declared LapTime as a String.

Yeah, I googled it and found info about different declarations. To be honest, I found the timestamp code somewhere else and modified it to suit me. I didn't even look at the declarations.
Thanks for all your help!