|
TRANSPOSE & DELETE
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 |
TRANSPOSE & DELETE
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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
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 -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
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 On Sat, 12 Jan 2008 05:44:00 -0800, Rudy wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
You got it right<g
I was still stuck on column C from OP's original post. Gord On Sat, 12 Jan 2008 10:05:02 -0800, Gary''s Student wrote: 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 |
TRANSPOSE & DELETE
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 "Gord Dibben" wrote: 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 On Sat, 12 Jan 2008 05:44:00 -0800, Rudy wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
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://mvp.support.microsoft.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 On Sat, 12 Jan 2008 18:16:00 -0800, Rudy wrote: 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 "Gord Dibben" wrote: 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 On Sat, 12 Jan 2008 05:44:00 -0800, Rudy wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
TRANSPOSE & DELETE
Yeah I did, thanks....
rgds, rudy "Gord Dibben" wrote: 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://mvp.support.microsoft.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 On Sat, 12 Jan 2008 18:16:00 -0800, Rudy wrote: 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 "Gord Dibben" wrote: 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 On Sat, 12 Jan 2008 05:44:00 -0800, Rudy wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200765 "Rudy" wrote: 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 |
| All times are GMT +1. The time now is 08:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com