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

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

hi
yes.
Sub transferitforPam()
Dim c As Long
c = Cells(Rows.Count, "P").End(xlUp).Row
For Each cell In Range("P2:P" & c)
If cell = "CC" Then
cell.Offset(0, -7).Value = cell.Offset(0, -5).Value
End If
Next cell
MsgBox "done"
End Sub

this can go into a standard module.

regards
FSt1

"Pam" wrote:

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

I just got help with something a little similar to this yesterday. I made a
few modifications. What do you think?
Sub Analyze()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 16).End(xlUp).Row

For Each c In Sheets("Sheet1").Range("P2:P" & lr)
If c.Value = "CC" Then
c.Offset(0, -7).Value = c.Offset(0, -5).Value
End If
Next c
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

Wow! That was quick and perfect!
Thanks so much for your help.

"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

Thank you for the quick reply and help.
Pam

"FSt1" wrote in message
...
hi
yes.
Sub transferitforPam()
Dim c As Long
c = Cells(Rows.Count, "P").End(xlUp).Row
For Each cell In Range("P2:P" & c)
If cell = "CC" Then
cell.Offset(0, -7).Value = cell.Offset(0, -5).Value
End If
Next cell
MsgBox "done"
End Sub

this can go into a standard module.

regards
FSt1

"Pam" wrote:

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

The only problem I see with your code (on a quick look) is that if there are
20,000 rows of data, your loop will execute 20,000 times looking for the
CC's in the column even if there are only, say, 5 CC's in total... the loop
in the code I provided would only execute 5 times.

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
I just got help with something a little similar to this yesterday. I made
a
few modifications. What do you think?
Sub Analyze()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 16).End(xlUp).Row

For Each c In Sheets("Sheet1").Range("P2:P" & lr)
If c.Value = "CC" Then
c.Offset(0, -7).Value = c.Offset(0, -5).Value
End If
Next c
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

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

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

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

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

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

an excelent insight. I learned something. i guess that is why you are an MVP
and we are just the "other guys".
but we do try.

with respects
FSt1

"Rick Rothstein" wrote:

The only problem I see with your code (on a quick look) is that if there are
20,000 rows of data, your loop will execute 20,000 times looking for the
CC's in the column even if there are only, say, 5 CC's in total... the loop
in the code I provided would only execute 5 times.

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
I just got help with something a little similar to this yesterday. I made
a
few modifications. What do you think?
Sub Analyze()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 16).End(xlUp).Row

For Each c In Sheets("Sheet1").Range("P2:P" & lr)
If c.Value = "CC" Then
c.Offset(0, -7).Value = c.Offset(0, -5).Value
End If
Next c
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

Will Your code below be done before or after resetting Margin (Col K) to
zero (0) ?

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

If before resetting Margin (Col K) to zero (0), then Margin (Col K) /
Merch (Col I) will always be 1 or #DIV/0!

If after resetting Margin (Col K) to zero (0), then Margin (Col K) /
Merch (Col I) will always be 0 or #DIV/0!

Is this what you want?

Keiji

Pam wrote:
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

I don't totally agree with Rick's comment. For example, if all cells in
column P is filled with data and 30000 cells are filled with "CC", then
the loop in Rick's code execute 30000 times and the loop in your code
execute 65536 times (XL 2003). In this case, Rick's code might take more
time than your code. In my thought, the time of procedure depend on
cases. Of course Rick's code looks smarter than yours.

Keiji

FSt1 wrote:
an excelent insight. I learned something. i guess that is why you are an MVP
and we are just the "other guys".
but we do try.

with respects
FSt1

"Rick Rothstein" wrote:

The only problem I see with your code (on a quick look) is that if there are
20,000 rows of data, your loop will execute 20,000 times looking for the
CC's in the column even if there are only, say, 5 CC's in total... the loop
in the code I provided would only execute 5 times.

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
I just got help with something a little similar to this yesterday. I made
a
few modifications. What do you think?
Sub Analyze()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 16).End(xlUp).Row

For Each c In Sheets("Sheet1").Range("P2:P" & lr)
If c.Value = "CC" Then
c.Offset(0, -7).Value = c.Offset(0, -5).Value
End If
Next c
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

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

I'm not so sure my code would take more time than iterating each cell in a
column, even for 30,000 cells out of 65536. While Find does have extra
overhead in that it has multiple filters to apply, I would expect the
underlying code to be relatively optimized to carry out its functions. With
that said, the OP's original post, while not stated, did not give me the
impression that a high percentage of the cells in Column P would have
entries of CC in them, so I would expect my posted code to be an efficient
way to process the OP's request.

--
Rick (MVP - Excel)


"keiji kounoike" <"kounoike A | T ma.Pikara.ne.jp" wrote in message
...
I don't totally agree with Rick's comment. For example, if all cells in
column P is filled with data and 30000 cells are filled with "CC", then the
loop in Rick's code execute 30000 times and the loop in your code execute
65536 times (XL 2003). In this case, Rick's code might take more time than
your code. In my thought, the time of procedure depend on cases. Of course
Rick's code looks smarter than yours.

Keiji

FSt1 wrote:
an excelent insight. I learned something. i guess that is why you are an
MVP and we are just the "other guys". but we do try.

with respects
FSt1

"Rick Rothstein" wrote:

The only problem I see with your code (on a quick look) is that if there
are 20,000 rows of data, your loop will execute 20,000 times looking for
the CC's in the column even if there are only, say, 5 CC's in total...
the loop in the code I provided would only execute 5 times.

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
I just got help with something a little similar to this yesterday. I
made a
few modifications. What do you think?
Sub Analyze()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 16).End(xlUp).Row

For Each c In Sheets("Sheet1").Range("P2:P" & lr)
If c.Value = "CC" Then
c.Offset(0, -7).Value = c.Offset(0, -5).Value
End If
Next c
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"Rick Rothstein" wrote:

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

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