Subtraction macro request ...

**rocket1406** · 07-12-2007, 04:50 PM

Hey guys.

I have Time Data in one column, and Values in the next, and then a blank column (for calculations, like subtraction/time).

Is there any way to automate the =Cell2-Cell1 command for regions of blue cells and have it paste the value in the blank column lined up with the first blue cell of the region?

The attachment will make more sense, but this is what I'm talking about:

Before subtraction macro:

BlueTime1 Value Blank BlueTime4 Value Blank
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value Blank BlueTime10 Value Blank
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank

After subtraction macro:

BlueTime1 Value BlueTime3-BT1 BlueTime4 Value BlueTime6-BT4
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value BlueTime9-BT7 BlueTime10 Value BlueTime12-BT10
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank

Sample attached.

Thanks so much guys!

**rylo** · 07-12-2007, 08:18 PM

Hi

This is pretty clunky but should get you there.

Please Login or Register  to view this content.

You will have to make sure that the breaks in the driving columns (A,D,G) are blanks, not the spaces that you currently have in some of the cells. Also, row 2 will also have to be blank. As an example A13 is blank, but A9 has a space.

HTH

rylo

**rocket1406** · 07-12-2007, 11:02 PM

Hey.

Cool, I'll try it. Now is this going to work for a spreadsheet that has more of the same stuff (more rows and more columns), with the same format?

Thanks

**rylo** · 07-12-2007, 11:14 PM

Hi

Number of rows doesn't matter, but it will only operate on the set number of columns.

If you are likely to have a variable number of columns, let me know and I'll rejig things a bit.

rylo

**rocket1406** · 07-12-2007, 11:45 PM

Yeah, my columns usually follow the same format all the way through to AR

So like

AB, DE, GH, JK, ... AR, with blank columns in between (C, F, etc...)

-----------------------

Also is the only way to turn my spaces into blanks to manually highlight AB empty cells and do "Right Click + n" clear contents command?

Or is there a command to turn any space cell into a blank (would be so much easier)?

------------------------

And I did get it to work on that sample worksheet after I did a right click clear contents command on all the spaces/blanks ... if a converter command cannot be done easily don't worry about it, I'll manually go through my worksheets and "Right click + n" them ... it's just that most of my worksheets are 3000 rows and have columns from A:AZ or A:AR

**rylo** · 07-12-2007, 11:56 PM

Hi

1) Use the find / replace commands to turn the spaces into blanks. Make sure you use the Match Entire Cell Contents option when actioning.

2) I'll review the macro and repost.

rylo

**rylo** · 07-13-2007, 12:10 AM

Hi

revised code.

Please Login or Register  to view this content.

rylo

**rocket1406** · 07-13-2007, 12:52 AM

Hey thanks, but one more thing.

It is not working in my multi-sheet worksheet... anyway to specify this to a worksheet named "Final"?

**rylo** · 07-13-2007, 01:06 AM

Hi

At the beginning add the line

Please Login or Register  to view this content.

This will select the sheet before actioning.

rylo

**rocket1406** · 07-13-2007, 01:12 AM

Thanks so much.

One last thing. The new macro works when I do clear contents manually.

But I've tried for 10 minutes trying to do the Find Replace of a space cell to a blank cell.

It says it makes replacements, but then your macro doesn't work properly.

Can you try it on the sample? and tell me step by step what you did?

I am a "noob" at this hehe

Thanks so much.

**rylo** · 07-13-2007, 01:26 AM

Hi

Using the file you have attached to the original post, I opened the file and selected A1.

Edit, Replace, Find What: [press the space key once], Replace with: blank, Options, select the Match entire cell contents, Replace All.

It came back with 24 replacements.

rylo