Works great - thank you.

"Rick Rothstein" wrote in message
...
This code should do that (only one line of code was added to the previous
code I posted)...

Sub FindCCCopyMarginToMerch()
Dim C As Range
Dim FirstAddress As String
With Worksheets("Sheet1")
Set C = .Columns("P").Find("CC", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
.Cells(C.Row, "I").Value = .Cells(C.Row, "K").Value
.Cells(C.Row, "K").Value = 0
Set C = .Columns("P").FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With
End Sub

--
Rick (MVP - Excel)


"Pam" wrote in message
...
I think so - I'm getting dizzy trying to figure all this out.

Can we just reset Margin (Col K) to zero (0) after copying the amount in
Col K to Col I in rows where Col P = CC?

I'm sorry for the confusion and I appreciate your continued help with
this.
Pam


"Rick Rothstein" wrote in message
...
Wait a minute... it just occurred to me... if you do the calculation
AFTER the copy, then (for the given row), I and K will have the same
value, so your If..Then test condition...

Merch (Col I) - Cost (Col J) = Margin (Col K)

will only be true if Column J's value is 0; and, for that condition,
your division...

Margin (Col K) / Merch (Col I)

will always be 1 (unless the Merch value is 0 in which case and error
will result because you can't divide by 0). Given this, your test and
calculation would become (in pseudo-code)...

If Col(J) = 0 Then Col(M) = 1

Do you agree?

--
Rick (MVP - Excel)


"Pam" wrote in message
...
Yes, only on rows where col P has cc and after copying data from K to I
with CC in col P.


"Rick Rothstein" wrote in message
...
Is that calculation to take place only on rows where Column P has a CC
in it? Or is this calculation to take place for all data rows? Also,
when should it be performed... before the copying of data from Column
K to Column I for those rows with CC in Column P or after?

--
Rick (MVP - Excel)


"Pam" wrote in message
...
Now that I look at it, would it also be possible to calculate this
sheet in the same procedure.
As it is now, this is just a sheet with data imported from another
program and there are no calculations. I would like to have the
following:

Merch (col I) - Cost (Col J) = Margin (col K) then
Margin (col K) / Merch (col I) = GM% (col M)

I do okay with Access, but Excel code is over my head.

Thanks in advance for any solutions you may have.
Pam


"Rick Rothstein" wrote in
message ...
Give this macro a try (change my Sheet1 reference in the With
statement to your worksheet's actual name)...

Sub FindCCCopyMarginToMerch()
Dim C As Range
Dim FirstAddress As String
With Worksheets("Sheet1")
Set C = .Columns("P").Find("CC", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
.Cells(C.Row, "I").Value = .Cells(C.Row, "K").Value
Set C = .Columns("P").FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With
End Sub

--
Rick (MVP - Excel)


"Pam" wrote in message
...
Hi,

I have a worksheet with columns "Merch" (Column I), "Margin"
(Column K) and "Terms" (Column P). What I want to do is, if Terms =
CC, replace Merch $ amount with Margin $ amount? Is this possible
with code?

Any help would be greatly appreciated.
Thanks in advance,
Pam