Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appropriate use of cell reference when creating macros
I have two questions: 1-i'm trying to create a very simple macro that will
extract certain text from a cell and move it to the cell to the right. The full text is L+300...i want to copy just the 300, and move it. I want to do this for a column of cells that contain L+###. I've read posts that say the edit function doesn't work with macros (which, by the way is NEVER mentioned on excel help...), so how do you "get into" the cell to do this? Also, i've tried to use the cell reference tool on the 'stop record' toolbar with no success. HOw does this work, exactly? do you reference the target cell or the original? neither worked... 2- (stupid question) is there a way to enter this discussion area directly w/o going thru excel's help function (like, is there a direct web address?) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appropriate use of cell reference when creating macros
Hi,
If all you want to do is extract data in a cell to the right then I wouldn't use a macro, it's overkill. With your data in Column D put this in the column to the right and drag down. =MID(D1,SEARCH("+",D1)+1,LEN(D1)) As to how to get here, use a newsreader or as I do this address http://www.microsoft.com/office/comm...ang=e n&cr=US Mike "L'il Ginny" wrote: I have two questions: 1-i'm trying to create a very simple macro that will extract certain text from a cell and move it to the cell to the right. The full text is L+300...i want to copy just the 300, and move it. I want to do this for a column of cells that contain L+###. I've read posts that say the edit function doesn't work with macros (which, by the way is NEVER mentioned on excel help...), so how do you "get into" the cell to do this? Also, i've tried to use the cell reference tool on the 'stop record' toolbar with no success. HOw does this work, exactly? do you reference the target cell or the original? neither worked... 2- (stupid question) is there a way to enter this discussion area directly w/o going thru excel's help function (like, is there a direct web address?) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appropriate use of cell reference when creating macros
Select the column and try this macro:
Sub ginny() Set r = Intersect(Selection, ActiveSheet.UsedRange) For Each cell In r If Left(cell.Value, 2) = "L+" Then cell.Offset(0, 1).Value = Right(cell.Value, 3) End If Next End Sub It will ignore any values in the column that do not begin with "L+" -- Gary''s Student - gsnu200804 "L'il Ginny" wrote: I have two questions: 1-i'm trying to create a very simple macro that will extract certain text from a cell and move it to the cell to the right. The full text is L+300...i want to copy just the 300, and move it. I want to do this for a column of cells that contain L+###. I've read posts that say the edit function doesn't work with macros (which, by the way is NEVER mentioned on excel help...), so how do you "get into" the cell to do this? Also, i've tried to use the cell reference tool on the 'stop record' toolbar with no success. HOw does this work, exactly? do you reference the target cell or the original? neither worked... 2- (stupid question) is there a way to enter this discussion area directly w/o going thru excel's help function (like, is there a direct web address?) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appropriate use of cell reference when creating macros
yes, it's overkill, agreed - i was trying to just learn how to use the macro
function because my list is probably 600 rows long...i'll try your formula, tho...do you have any advice on the cell reference in general? there's no guidance whatsoever on excel help...thx! "Mike H" wrote: Hi, If all you want to do is extract data in a cell to the right then I wouldn't use a macro, it's overkill. With your data in Column D put this in the column to the right and drag down. =MID(D1,SEARCH("+",D1)+1,LEN(D1)) As to how to get here, use a newsreader or as I do this address http://www.microsoft.com/office/comm...ang=e n&cr=US Mike "L'il Ginny" wrote: I have two questions: 1-i'm trying to create a very simple macro that will extract certain text from a cell and move it to the cell to the right. The full text is L+300...i want to copy just the 300, and move it. I want to do this for a column of cells that contain L+###. I've read posts that say the edit function doesn't work with macros (which, by the way is NEVER mentioned on excel help...), so how do you "get into" the cell to do this? Also, i've tried to use the cell reference tool on the 'stop record' toolbar with no success. HOw does this work, exactly? do you reference the target cell or the original? neither worked... 2- (stupid question) is there a way to enter this discussion area directly w/o going thru excel's help function (like, is there a direct web address?) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a reference from one cell to several other relavent cells | Excel Discussion (Misc queries) | |||
macros creating | Excel Discussion (Misc queries) | |||
creating a range reference from cell values | Excel Discussion (Misc queries) | |||
creating macros in excel | Excel Discussion (Misc queries) | |||
Macros-creating new sheets | Excel Worksheet Functions |