How to perform two actions ?
 

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.


--
morph000
------------------------------------------------------------------------
morph000's Profile: http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434

How to perform two actions ?
 

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

"morph000" wrote:


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.


--
morph000
------------------------------------------------------------------------
morph000's Profile: http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434

How to perform two actions ?
 

No go I'm afraid. Has multiple compile errors.. :(


--
morph000
------------------------------------------------------------------------
morph000's Profile: http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434

How to perform two actions ?
 

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!

"morph000" wrote:


No go I'm afraid. Has multiple compile errors.. :(


--
morph000
------------------------------------------------------------------------
morph000's Profile: http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434

How to perform two actions ?
 

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. :(


--
morph000
------------------------------------------------------------------------
morph000's Profile: http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434

How to perform two actions ?
 

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




"morph000" wrote in
message ...

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. :(


--
morph000
------------------------------------------------------------------------
morph000's Profile:
http://www.excelforum.com/member.php...o&userid=30490
View this thread: http://www.excelforum.com/showthread...hreadid=501434