TRANSPOSE & DELETE

  • Thread starter Thread starter Rudy
  • Start date Start date
R

Rudy

Hi,

Can someone assist pls. Here's the scenario:

IF C2=C, the value in N2 is to be transpose to O2 then delete N2 value.

thanks,

rudy
 
Put the following sub in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
If Target.Value <> "C" Then Exit Sub
Application.EnableEvents = False
Range("N2").Copy Range("O2")
Range("N2").Clear
Application.EnableEvents = True
End Sub

REMEMBER: the worksheet code area, not a standard module.
 
Thanks but to be honest, I am not yet too familiar with VBE. A simple formula
would do fine with me. BTW, I made a mistake in saying that I will requote
again:

If E12:E500 = "C", the value in N12:N500 are to be transpose to O12:O500,
then delete N12:N500 value. Sorry about that.

rgds,

rudy
 
You need a macro to do the clearing. Here is the new macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E12:E500")) Is Nothing Then Exit Sub
If Target.Value <> "C" Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 9).Copy Target.Offset(0, 10)
Target.Offset(0, 9).Clear
Application.EnableEvents = True
End Sub

You don't need to be an expert on VBE.
Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Rudy

A formula cannot delete anything, only return values.

The term "Transpose" is not correct in this case.

Transpose in Excel is to change orientation from east-west to north-south or
opposite.

You just want cells in N12:N500 cut and pasted to cells in O12:O50 which is a
"transfer"

Change the code to..................

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
If Intersect(Target, Range("C12:C500")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In Target
If Target.Value = "C" And Target.Offset(0, 11) _
.Value <> "" Then
With Target
.Offset(0, 12).Value = Target.Offset(0, 11).Value
.Offset(0, 11).ClearContents
End With
End If
Next
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into the
module that opens.

Alt + q to return to Excel.

With data in N12:N500 start entering letters in column C.

Whenever you enter a "C" the data in N will go to O.


Gord Dibben MS Excel MVP
 
Hi Gord,

It did the trick. Thanks a lot. Same goes to Gary for your time & effort.
This site is very helpful for "DUMMIES" like myself. I am really beginning to
enjoy this. It's kinda hard for me to learn all this things especially if I
didn't have a formal training on VBA/VBE. Any particular books or hand outs
you guys might share that I can ponder about just to hone my skills on this.
It will greatly help me especially on my job.

rgds,

rudy
 
 
Yeah I did, thanks....

rgds,

rudy

Gord Dibben said:
Happy to see you must have corrected my typo on the Range("C12:C500")

As far as VBA goes, stick around the excel.programming group for lots of
pointers.

Here are a few on-line sites in no particular order of importance or knowledge
offered.

http://www.appspro.com/
http://www.excelforum.com/

http://www.mvps.org/
http://www.andrewsexceltips.com/menu.htm
http://mcgimpsey.com/
http://www.oaltd.co.uk/
http://home.pacbell.net/beban/
http://www.xldynamic.com/source/xld.html

CODESITES:
http://www.codesites.com/
http://www.contextures.com/
http://www.cpearson.com/excel.htm
http://www.j-walk.com/ss/excel/links/
http://www.mvps.org/dmcritchie/excel/excel.htm
http://edc.bizhosting.com/english/index.htm
http://www.oaltd.co.uk/Excel/Default.htm
http://www.vbapro.com/


Gord
Click to expand...
 
Back
Top