Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet)
Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
Examples of before/after
-- Don Guillett Microsoft MVP Excel SalesAid Software "Stephen Ford" wrote in message ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
I'm not sure if I completely understand your question... does this code do
what you want? With ActiveCell .Offset(0, -1).Value = Left(.Value, Len(.Value) - 3) .Value = Right(.Value, 3) End With -- Rick (MVP - Excel) "Stephen Ford" wrote in message ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
Seems my answer didn't make it to the group, this is the second try.
(just changed the befoer value of the StartCell to XXX, seems M$ gets offended by x_y_z_ without the underscores) I agree with Rick, I don't quite understand what you want to do. As Don wrote, you should provide examples of how your data looks before and how it should be after. That's how I understand it: LeftCell StartCell befo ABCDE XXX after: AB CDE Is that what you want? Helmut. "Stephen Ford" schrieb im Newsbeitrag ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
It's always worth another try... :-)
Yes that's it exactly "Helmut Meukel" wrote in message ... Seems my answer didn't make it to the group, this is the second try. (just changed the befoer value of the StartCell to XXX, seems M$ gets offended by x_y_z_ without the underscores) I agree with Rick, I don't quite understand what you want to do. As Don wrote, you should provide examples of how your data looks before and how it should be after. That's how I understand it: LeftCell StartCell befo ABCDE XXX after: AB CDE Is that what you want? Helmut. "Stephen Ford" schrieb im Newsbeitrag ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
Yes, I think that's it. I'll check... back soon.
"Rick Rothstein" wrote in message ... I'm not sure if I completely understand your question... does this code do what you want? With ActiveCell .Offset(0, -1).Value = Left(.Value, Len(.Value) - 3) .Value = Right(.Value, 3) End With -- Rick (MVP - Excel) "Stephen Ford" wrote in message ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
This works
With ActiveCell .Value = Right(.Offset(0, -1).Value, 3) .Offset(0, -1).Value = Left(.Offset(0, -1).Value, Len(.Offset(0, -1).Value) - 3) End With ActiveCell.Offset(1, 0).Activate It changes CellX CellY A03001 XXX (before) A03 001 (after) The code ".Offset(0, -1).Value" is repeated a lot. Can a variable / object or whatever be assigned and used instead? CellY is text. If is gets a value like "001" the cells shows an error. I can use the mouse to Ignore Error. Can this be programmed or can the error be avoided another way? "Stephen_Ford net" <stephen_fordNO@SPAMuwclub<dot wrote in message ... Yes, I think that's it. I'll check... back soon. "Rick Rothstein" wrote in message ... I'm not sure if I completely understand your question... does this code do what you want? With ActiveCell .Offset(0, -1).Value = Left(.Value, Len(.Value) - 3) .Value = Right(.Value, 3) End With -- Rick (MVP - Excel) "Stephen Ford" wrote in message ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
Need VB code to edit cell values
The latest version uses a variable for the text in the cell to left of the
active cell-: Dim LocTxt As String With ActiveCell LocTxt = .Offset(0, -1).Value .Value = Right(LocTxt, 3) .Offset(0, -1).Value = Left(LocTxt, Len(LocTxt) - 3) End With ActiveCell.Offset(1, 0).Activate But I would like to know how to stop the error checking for Number as Text in the active cell. "Stephen_Ford net" <stephen_fordNO@SPAMuwclub<dot wrote in message ... Yes, I think that's it. I'll check... back soon. "Rick Rothstein" wrote in message ... I'm not sure if I completely understand your question... does this code do what you want? With ActiveCell .Offset(0, -1).Value = Left(.Value, Len(.Value) - 3) .Value = Right(.Value, 3) End With -- Rick (MVP - Excel) "Stephen Ford" wrote in message ... I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell |
All times are GMT +1. The time now is 03:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com