I am looking for help to create a macro to do the below. I have an
idea how to do it with an if then statement, but that would require
dragging the formula all the way down, and the data can very from 500
rows, to a few thousand. Column D contains ID numbers. We have the
page subtotaled counting the ID numbers so it says ..... count.
Column S are payments. Obviously when it is subtotaled, it adds a
line, and lists ....count. I would like a macro to go through column
D and for cells that contain the word count, for it to enter the word
overpayment in column S (same row) and sum (or subtotal) all of the id
numbers values in column t. I know I could do that using sumif
command outside of a macro, but like I said, it can contain thousands
of rows. I thought about non macro ways of doing it, If then
statements about putting the word overpayment in column S, but
everything i did would wipe out the values in the rows that don't
contain the word count. Also,again, could be thousands of rows,
would suck to manually look for them all :(



Column D Column S column t
ab3s0 17.99 5.18
ab3s0 17.99 3.27
ab3s0 count overpayment Sum of the above

abs1 15.87 5.38
abs1 23.85 5.20
abs1 5.24 1.07
abs1 count overpayment Sum of the above.

vid Guru,

Select Columns S and T, then run this macro - see the in-line comment about constants versus
formulas....

Sub PutInOVerPayments()
Dim myA As Range
For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas
'Comment out the above line and uncomment the next line if
'you have formulas instead of constants
'For Each myA In Selection.SpecialCells(xlCellTypeFormulas, 23).Areas
With myA.Cells(myA.Rows.Count + 1, 1)
.Value = "Over-Payment"
.Offset(0, 1).Formula = _
"=SUM(" & myA.Columns(2).Address(False, False) & ")"
End With
Next myA
End Sub


--
HTH,
Bernie
MS Excel MVP


wrote in message
...
I am looking for help to create a macro to do the below. I have an
idea how to do it with an if then statement, but that would require
dragging the formula all the way down, and the data can very from 500
rows, to a few thousand. Column D contains ID numbers. We have the
page subtotaled counting the ID numbers so it says ..... count.
Column S are payments. Obviously when it is subtotaled, it adds a
line, and lists ....count. I would like a macro to go through column
D and for cells that contain the word count, for it to enter the word
overpayment in column S (same row) and sum (or subtotal) all of the id
numbers values in column t. I know I could do that using sumif
command outside of a macro, but like I said, it can contain thousands
of rows. I thought about non macro ways of doing it, If then
statements about putting the word overpayment in column S, but
everything i did would wipe out the values in the rows that don't
contain the word count. Also,again, could be thousands of rows,
would suck to manually look for them all :(



Column D Column S column t
ab3s0 17.99 5.18
ab3s0 17.99 3.27
ab3s0 count overpayment Sum of the above

abs1 15.87 5.38
abs1 23.85 5.20
abs1 5.24 1.07
abs1 count overpayment Sum of the above.

Works like a charm! Thank you!