if, then, replace text

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

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
 
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
 
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---
 
Thank you for the quick reply and help.
Pam

FSt1 said:
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
Click to expand...
 
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.
 
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
 
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?
 
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?
 
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
 
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
 
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
 
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
 
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
 
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.
 
Works great - thank you.

Rick Rothstein said:
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
Click to expand...
 
Back
Top