Hello everyone, my problem is a very simple one.. probably easy for al
you programmers

I have a similar table in a spreadsheet like so:
Ordered........Shipped
50......................30
20......................20
10.......................5
20......................10

How would I take the difference of the two columns and have the valu
put in the first column, then have the value in the 'Shipped' colum
deleted. If the two values are identical to begin with, they would bot
be deleted.

For example in the first two rows:

Ordered........Shipped
50......................30
20......................20

Becomes...

Ordered........Shipped
20......................
......................

Thanks for any contributions!

--
grahamhurlbur
-----------------------------------------------------------------------
grahamhurlburt's Profile: http://www.excelforum.com/member.php...fo&userid=2987
View this thread: http://www.excelforum.com/showthread.php?threadid=49582

Graham,

You could try this, which will work as long as you have the shipped values in column B and don't
have any blanks:

Sub GrahamSubtract()
With Columns("B:B").SpecialCells(xlCellTypeConstants, 1)
.Copy
.Offset(0, -1).Resize(, 2).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlSubtract
End With
End Sub

Though this will leave 0s and not blanks.

If you do have blanks and/or want blanks, then use

Sub GrahamSubtract2()
Dim myCell As Range
For Each myCell In Range("B:B").SpecialCells(xlCellTypeConstants, 1)
myCell(1, 0).Value = myCell(1, 0).Value - myCell.Value
If myCell(1, 0).Value = 0 Then myCell(1, 0).ClearContents
myCell.ClearContents
Next myCell
End Sub


HTH,
Bernie
MS Excel MVP


"grahamhurlburt" <grahamhurlburt.20ibmm_1135365002.5284@excelforu m-nospam.com wrote in message
news:grahamhurlburt.20ibmm_1135365002.5284@excelfo rum-nospam.com...

Hello everyone, my problem is a very simple one.. probably easy for all
you programmers

I have a similar table in a spreadsheet like so:
Ordered........Shipped
50......................30
20......................20
10.......................5
20......................10

How would I take the difference of the two columns and have the value
put in the first column, then have the value in the 'Shipped' column
deleted. If the two values are identical to begin with, they would both
be deleted.

For example in the first two rows:

Ordered........Shipped
50......................30
20......................20

Becomes...

Ordered........Shipped
20......................
.....................

Thanks for any contributions!!


--
grahamhurlburt
------------------------------------------------------------------------
grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878
View this thread: http://www.excelforum.com/showthread...hreadid=495825

Thank you Bernie!! It worked perfectly

Sigh, I'm so helpless when it comes to macros..haha


--
grahamhurlburt
------------------------------------------------------------------------
grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878
View this thread: http://www.excelforum.com/showthread...hreadid=495825