How to perform two actions ?

M

morph000

Bit of a newbie with Excel I'm afraid, and need some help with this
one.

What I need to do is this (by way of example):

Scan column A in a worksheet and for each cell in that column that
contains "Y", move (not copy) the contents of the cell in that row but
in column B to the cell in column C AND then delete the old contents of
the B cell.

ie if An="Y" then Cn=Bn AND Bn="" where "n" is any number.

Any ideas on this welcome ! No doubt there's a fairly easy way to do
it.
 
K

Kassie

Hi morph000

Try the following code. Press <Alt><F11>, select Insert|Module and paste

Option Explicit

Dim iLastRow As Long
Dim iText As Variant
Dim iRow As Long

Sub Testing()
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iRow = 1
iText = InputBox("What criteria do you want to use", "Enter Criteria",
vbOKCancel)
For iRow = iRow To iLastRow
If Range("A" & iRow).Value = iText Then
With Worksheets("Sheet1")
.Range("B" & iRow).Copy
.Range("C" & iRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
.Range("B" & iRow).ClearContents
End With
End If
Next iRow

End Sub
 
K

Kassie

Hi morph

Did you copy and paste my code, or did you type it in?

On my side it works perfectly. If you typed it, I suggest you copy my code,
and paste it into your module?

Let me know!
 
M

morph000

Nope. No go, tried 2 P4 3GHz pc's with the same result. Both using Win
XP SP2 and Office XP Pro. Cut and pasted as I normally would. :(
 
P

Peo Sjoblom

Type of PC doesn't matter, most likely you have line wrapping, for instance

vbOKCancel)

should be on the same line as "Enter Criteria"

SkipBlanks _

should be on the same line as xlNone,

--
Regards,

Peo Sjoblom

Portland, Oregon
 
Top