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

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

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

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

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

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

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

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

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