Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or sub (add-in)
Hi! I have a very easy sub that I use in many worksheets. I use the sub like
this: I select certain cells and then I press Alt+F8. The sub then removes blanks that are used as thousand separators (1 000 000 becomes 1000000) so that Excel can read the values as numbers and not as text. I need to use this because I paste data from another program into Excel. Now I want to be able to use this in all my workbooks. I know how to make an add-in for FUNCTIONS. The problem is I dont know how to run my sub when saving it as an add-in. Do I have to use a function instead? Is it possible to rewrite my sub into a function instead? I woulöd very much appreciate help with this. Thanks alot! Code for my sub: Sub Makro1() Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or sub (add-in)
Unless I am missing something... do you really need to use a macro for this?
What about selecting the cells the way you do now, press Ctrl+H (or click on Edit/Replace on Excel's menu bar), type a single space character (this will go into the selected first "Find What" field and will stay there for any subsequent calls to this dialog box unless replaced by a subsequent search) and then press Alt+A (or click the Replace All button). Press the Enter key to remove the MessageBox that appears and then the Esc key to close down the dialog box (or click the appropriate buttons). Rick "Arne Hegefors" wrote in message ... Hi! I have a very easy sub that I use in many worksheets. I use the sub like this: I select certain cells and then I press Alt+F8. The sub then removes blanks that are used as thousand separators (1 000 000 becomes 1000000) so that Excel can read the values as numbers and not as text. I need to use this because I paste data from another program into Excel. Now I want to be able to use this in all my workbooks. I know how to make an add-in for FUNCTIONS. The problem is I dont know how to run my sub when saving it as an add-in. Do I have to use a function instead? Is it possible to rewrite my sub into a function instead? I woulöd very much appreciate help with this. Thanks alot! Code for my sub: Sub Makro1() Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or sub (add-in)
Hi Rick! Thanks for your answer. You are quite right in that I can use the
way that you described. The problem is that it is just one example of very many such procedures that I have to do. Thus any manul solution will be too time consuming. I have tried to figure out how to fix this by using an add-in (which seems reasonable to me at least..) but so far I have not managed to get it to work. Can you please answer me IFT IS POSSIBLE or am I just wasting my time here? I need to make a reference to a selection (which can be a problem using add-ins as I understand it) and I need to have it as a sub (or is it possible to do changes like that using a function) or at least sometihng so I can run it when I want. I would very much appreciate help with this. Again many thanks for your answer. "Rick Rothstein (MVP - VB)" skrev: Unless I am missing something... do you really need to use a macro for this? What about selecting the cells the way you do now, press Ctrl+H (or click on Edit/Replace on Excel's menu bar), type a single space character (this will go into the selected first "Find What" field and will stay there for any subsequent calls to this dialog box unless replaced by a subsequent search) and then press Alt+A (or click the Replace All button). Press the Enter key to remove the MessageBox that appears and then the Esc key to close down the dialog box (or click the appropriate buttons). Rick "Arne Hegefors" wrote in message ... Hi! I have a very easy sub that I use in many worksheets. I use the sub like this: I select certain cells and then I press Alt+F8. The sub then removes blanks that are used as thousand separators (1 000 000 becomes 1000000) so that Excel can read the values as numbers and not as text. I need to use this because I paste data from another program into Excel. Now I want to be able to use this in all my workbooks. I know how to make an add-in for FUNCTIONS. The problem is I dont know how to run my sub when saving it as an add-in. Do I have to use a function instead? Is it possible to rewrite my sub into a function instead? I woulöd very much appreciate help with this. Thanks alot! Code for my sub: Sub Makro1() Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or sub (add-in)
Perhaps this will be of some help...
http://office.microsoft.com/en-us/ex...872961033.aspx Rick "Arne Hegefors" wrote in message ... Hi Rick! Thanks for your answer. You are quite right in that I can use the way that you described. The problem is that it is just one example of very many such procedures that I have to do. Thus any manul solution will be too time consuming. I have tried to figure out how to fix this by using an add-in (which seems reasonable to me at least..) but so far I have not managed to get it to work. Can you please answer me IFT IS POSSIBLE or am I just wasting my time here? I need to make a reference to a selection (which can be a problem using add-ins as I understand it) and I need to have it as a sub (or is it possible to do changes like that using a function) or at least sometihng so I can run it when I want. I would very much appreciate help with this. Again many thanks for your answer. "Rick Rothstein (MVP - VB)" skrev: Unless I am missing something... do you really need to use a macro for this? What about selecting the cells the way you do now, press Ctrl+H (or click on Edit/Replace on Excel's menu bar), type a single space character (this will go into the selected first "Find What" field and will stay there for any subsequent calls to this dialog box unless replaced by a subsequent search) and then press Alt+A (or click the Replace All button). Press the Enter key to remove the MessageBox that appears and then the Esc key to close down the dialog box (or click the appropriate buttons). Rick "Arne Hegefors" wrote in message ... Hi! I have a very easy sub that I use in many worksheets. I use the sub like this: I select certain cells and then I press Alt+F8. The sub then removes blanks that are used as thousand separators (1 000 000 becomes 1000000) so that Excel can read the values as numbers and not as text. I need to use this because I paste data from another program into Excel. Now I want to be able to use this in all my workbooks. I know how to make an add-in for FUNCTIONS. The problem is I dont know how to run my sub when saving it as an add-in. Do I have to use a function instead? Is it possible to rewrite my sub into a function instead? I woulöd very much appreciate help with this. Thanks alot! Code for my sub: Sub Makro1() Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |