Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
write macro that copies any numbers in a cell & appends those numbers
proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Why don't you just use a worksheet formula concatenation.
You will have the same number of used cells either way from your description. If you want a macro you will need to be more specific as to what you need "another cell" just does not carry much meaning. Is it one cell at the top, on another sheet; or is does the cell change for each row and is found in a particular column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Well " I just canna' do it captain". I are a Newbie! I don't know what a
Formula Concatenation is. The reference I was reading recommended macros for repetitive operations. To be more accurate, the example I showed is exactly what I am trying to accomplish (numbers found in cell 2 appended to cell 1 preceeded by a period on that single row). I will look in the reference book for info on how to write a Formula thingamajiggy since that would probably be easier. Thanks for your interest in attempting to help me with this. -- DASJ "David McRitchie" wrote: Why don't you just use a worksheet formula concatenation. You will have the same number of used cells either way from your description. If you want a macro you will need to be more specific as to what you need "another cell" just does not carry much meaning. Is it one cell at the top, on another sheet; or is does the cell change for each row and is found in a particular column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Sorry, I misread the question. and it looks like a self-study question.
Most people here use Column Letters instead of numbers, so most of use would use Option, General (tab), settings: (uncheck) R1C1 style In which case your R1C1 would be A1 and the R1C2 would be B1 Your example in the cells look so related I thought you were making one from the other. Lookup the word "concatenation" in wikipedia and in your Excel Help, you won't get very far in Excel, or in VBA or in any programming language unless your are familiar with the term. so if you have a cell A1 with "ABC" and a cell B1 with "123" and you want to connect the two with a space between then you have a concatenation of a cell, a space and a second cell, the formula might be in cell C1. Example: A1: ABC B1: 123 C1: =A1 & " " & B1 note the quotes enclose a single space as a text constant You were asked to write a macro and from your example you were asked essentially, I think, to concatenate "Boxholder " in front of each cell with a value in a selection (specifically in a selected row). You were asked to assign a specific shortcut to your macro -- a shortcut that nobody working with a PC would assign to a macro if they had a sound mind because Ctrl+C is specifically used to copy a selection to the clipboard in many PC applications and certainly in Excel. So Part 1 of your course working with spreadsheet formulas you skipped over, and were thrown into Part 2 programming, after this exercise, Excel's own Ctrl+C will no longer work and you will have to figure out why --- duh. As for writing a subroutine to do what you want try using Google or Google Groups to find some examples to get you started in the right direction along with your textbook or class instruction, Excel specialcells text concatenation sub selection You will be using a loop of some kind to do repetitive operations, but anytime you are using a loop you have to know what the limits are, and it is unlikely that you should be checking all 256 columns so you have to find out what you are supposed to do, or make some assumptions, or use SpecialCells. When you ask a question, you are supposed to do as much of a problem as you can, whether for yourself, for work, or homework and just ask for some aspect that you do not understand, and demonstrate that you have worked on attempting to solve a problem rather than asking people to solve an entire problem or write an entire application. If read more about the parts above that you haven't done, you can probably look at my pages . http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/join.htm http://www.mvps.org/dmcritchie/excel/shortx2k.htm then again you may find exactly what you want from the Google search hopefully, nothing that you would be able to just copy and use, but something to get you started in the correct direction. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... Well " I just canna' do it captain". I are a Newbie! I don't know what a Formula Concatenation is. The reference I was reading recommended macros for repetitive operations. To be more accurate, the example I showed is exactly what I am trying to accomplish (numbers found in cell 2 appended to cell 1 preceeded by a period on that single row). I will look in the reference book for info on how to write a Formula thingamajiggy since that would probably be easier. Thanks for your interest in attempting to help me with this. -- DASJ "David McRitchie" wrote: Why don't you just use a worksheet formula concatenation. You will have the same number of used cells either way from your description. If you want a macro you will need to be more specific as to what you need "another cell" just does not carry much meaning. Is it one cell at the top, on another sheet; or is does the cell change for each row and is found in a particular column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Select the rows you want to work with (multiple rows are ok)
and run this: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim LastSpacePos As Long Dim myStr As String With ActiveSheet Set myRng = Intersect(.Range("a:a"), Selection.EntireRow) End With For Each myCell In myRng.Cells myStr = myCell.Offset(0, 1).Value LastSpacePos = 0 For iCtr = Len(myStr) To 1 Step -1 If Mid(myStr, iCtr, 1) = " " Then LastSpacePos = iCtr Exit For End If Next iCtr If LastSpacePos = 0 Then 'no spaces, skip it Else myCell.Value = myCell.Value & "." & Mid(myStr, LastSpacePos + 1) End If Next myCell End Sub Assign it to the shortcutkey of your choice. dasmithjones wrote: write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Thanks for the answer. I had no idea learning Excel was going to be so
exoteric. By the way, do you know of a link that has the actual VBA language "words" and syntax where I can look up the ingredients to write macros from scratch? -- DASJ "David McRitchie" wrote: Sorry, I misread the question. and it looks like a self-study question. Most people here use Column Letters instead of numbers, so most of use would use Option, General (tab), settings: (uncheck) R1C1 style In which case your R1C1 would be A1 and the R1C2 would be B1 Your example in the cells look so related I thought you were making one from the other. Lookup the word "concatenation" in wikipedia and in your Excel Help, you won't get very far in Excel, or in VBA or in any programming language unless your are familiar with the term. so if you have a cell A1 with "ABC" and a cell B1 with "123" and you want to connect the two with a space between then you have a concatenation of a cell, a space and a second cell, the formula might be in cell C1. Example: A1: ABC B1: 123 C1: =A1 & " " & B1 note the quotes enclose a single space as a text constant You were asked to write a macro and from your example you were asked essentially, I think, to concatenate "Boxholder " in front of each cell with a value in a selection (specifically in a selected row). You were asked to assign a specific shortcut to your macro -- a shortcut that nobody working with a PC would assign to a macro if they had a sound mind because Ctrl+C is specifically used to copy a selection to the clipboard in many PC applications and certainly in Excel. So Part 1 of your course working with spreadsheet formulas you skipped over, and were thrown into Part 2 programming, after this exercise, Excel's own Ctrl+C will no longer work and you will have to figure out why --- duh. As for writing a subroutine to do what you want try using Google or Google Groups to find some examples to get you started in the right direction along with your textbook or class instruction, Excel specialcells text concatenation sub selection You will be using a loop of some kind to do repetitive operations, but anytime you are using a loop you have to know what the limits are, and it is unlikely that you should be checking all 256 columns so you have to find out what you are supposed to do, or make some assumptions, or use SpecialCells. When you ask a question, you are supposed to do as much of a problem as you can, whether for yourself, for work, or homework and just ask for some aspect that you do not understand, and demonstrate that you have worked on attempting to solve a problem rather than asking people to solve an entire problem or write an entire application. If read more about the parts above that you haven't done, you can probably look at my pages . http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/join.htm http://www.mvps.org/dmcritchie/excel/shortx2k.htm then again you may find exactly what you want from the Google search hopefully, nothing that you would be able to just copy and use, but something to get you started in the correct direction. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... Well " I just canna' do it captain". I are a Newbie! I don't know what a Formula Concatenation is. The reference I was reading recommended macros for repetitive operations. To be more accurate, the example I showed is exactly what I am trying to accomplish (numbers found in cell 2 appended to cell 1 preceeded by a period on that single row). I will look in the reference book for info on how to write a Formula thingamajiggy since that would probably be easier. Thanks for your interest in attempting to help me with this. -- DASJ "David McRitchie" wrote: Why don't you just use a worksheet formula concatenation. You will have the same number of used cells either way from your description. If you want a macro you will need to be more specific as to what you need "another cell" just does not carry much meaning. Is it one cell at the top, on another sheet; or is does the cell change for each row and is found in a particular column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
Dave-
You are a genius! This worked like a champ. Thanks for showing me how it is done. Can you recommend any web resources that have online courses that would teach me from a novice standpoint and then move up from there? Again, thanks for be so dag nab good at writing macros for Excel !! -- DASJ "Dave Peterson" wrote: Select the rows you want to work with (multiple rows are ok) and run this: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim LastSpacePos As Long Dim myStr As String With ActiveSheet Set myRng = Intersect(.Range("a:a"), Selection.EntireRow) End With For Each myCell In myRng.Cells myStr = myCell.Offset(0, 1).Value LastSpacePos = 0 For iCtr = Len(myStr) To 1 Step -1 If Mid(myStr, iCtr, 1) = " " Then LastSpacePos = iCtr Exit For End If Next iCtr If LastSpacePos = 0 Then 'no spaces, skip it Else myCell.Value = myCell.Value & "." & Mid(myStr, LastSpacePos + 1) End If Next myCell End Sub Assign it to the shortcutkey of your choice. dasmithjones wrote: write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
I like the newsgroups. You can see lots of suggestions and lots of corrections
for real life problems. But there are tons of free sites to help. http://cpearson.com/excel/links.htm has a bunch listed. And if you go to any/most of them, they have their own list. (So we'll see you in a couple of years!) And google has lots of stuff you can search through, too. dasmithjones wrote: Dave- You are a genius! This worked like a champ. Thanks for showing me how it is done. Can you recommend any web resources that have online courses that would teach me from a novice standpoint and then move up from there? Again, thanks for be so dag nab good at writing macros for Excel !! -- DASJ "Dave Peterson" wrote: Select the rows you want to work with (multiple rows are ok) and run this: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim LastSpacePos As Long Dim myStr As String With ActiveSheet Set myRng = Intersect(.Range("a:a"), Selection.EntireRow) End With For Each myCell In myRng.Cells myStr = myCell.Offset(0, 1).Value LastSpacePos = 0 For iCtr = Len(myStr) To 1 Step -1 If Mid(myStr, iCtr, 1) = " " Then LastSpacePos = iCtr Exit For End If Next iCtr If LastSpacePos = 0 Then 'no spaces, skip it Else myCell.Value = myCell.Value & "." & Mid(myStr, LastSpacePos + 1) End If Next myCell End Sub Assign it to the shortcutkey of your choice. dasmithjones wrote: write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a macro
In addition to those I already gave you which would link to the additional
http://www.mvps.org/dmcritchie/excel/getstarted.htm to help you install your macros. I would look at the links under http://www.mvps.org/dmcritchie/excel....htm#tutorials The videos are probably the quickest way to pick up new things. Since others have actually posted macros, you might want to also look at http://www.mvps.org/dmcritchie/excel/join.htm#trimall and figure out what you want to use from different macros and why. I would certainly turn off screen updating and calculation during the macro if you will be doing more than one row at a time. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... Thanks for the answer. I had no idea learning Excel was going to be so exoteric. By the way, do you know of a link that has the actual VBA language "words" and syntax where I can look up the ingredients to write macros from scratch? -- DASJ "David McRitchie" wrote: Sorry, I misread the question. and it looks like a self-study question. Most people here use Column Letters instead of numbers, so most of use would use Option, General (tab), settings: (uncheck) R1C1 style In which case your R1C1 would be A1 and the R1C2 would be B1 Your example in the cells look so related I thought you were making one from the other. Lookup the word "concatenation" in wikipedia and in your Excel Help, you won't get very far in Excel, or in VBA or in any programming language unless your are familiar with the term. so if you have a cell A1 with "ABC" and a cell B1 with "123" and you want to connect the two with a space between then you have a concatenation of a cell, a space and a second cell, the formula might be in cell C1. Example: A1: ABC B1: 123 C1: =A1 & " " & B1 note the quotes enclose a single space as a text constant You were asked to write a macro and from your example you were asked essentially, I think, to concatenate "Boxholder " in front of each cell with a value in a selection (specifically in a selected row). You were asked to assign a specific shortcut to your macro -- a shortcut that nobody working with a PC would assign to a macro if they had a sound mind because Ctrl+C is specifically used to copy a selection to the clipboard in many PC applications and certainly in Excel. So Part 1 of your course working with spreadsheet formulas you skipped over, and were thrown into Part 2 programming, after this exercise, Excel's own Ctrl+C will no longer work and you will have to figure out why --- duh. As for writing a subroutine to do what you want try using Google or Google Groups to find some examples to get you started in the right direction along with your textbook or class instruction, Excel specialcells text concatenation sub selection You will be using a loop of some kind to do repetitive operations, but anytime you are using a loop you have to know what the limits are, and it is unlikely that you should be checking all 256 columns so you have to find out what you are supposed to do, or make some assumptions, or use SpecialCells. When you ask a question, you are supposed to do as much of a problem as you can, whether for yourself, for work, or homework and just ask for some aspect that you do not understand, and demonstrate that you have worked on attempting to solve a problem rather than asking people to solve an entire problem or write an entire application. If read more about the parts above that you haven't done, you can probably look at my pages . http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/join.htm http://www.mvps.org/dmcritchie/excel/shortx2k.htm then again you may find exactly what you want from the Google search hopefully, nothing that you would be able to just copy and use, but something to get you started in the correct direction. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... Well " I just canna' do it captain". I are a Newbie! I don't know what a Formula Concatenation is. The reference I was reading recommended macros for repetitive operations. To be more accurate, the example I showed is exactly what I am trying to accomplish (numbers found in cell 2 appended to cell 1 preceeded by a period on that single row). I will look in the reference book for info on how to write a Formula thingamajiggy since that would probably be easier. Thanks for your interest in attempting to help me with this. -- DASJ "David McRitchie" wrote: Why don't you just use a worksheet formula concatenation. You will have the same number of used cells either way from your description. If you want a macro you will need to be more specific as to what you need "another cell" just does not carry much meaning. Is it one cell at the top, on another sheet; or is does the cell change for each row and is found in a particular column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "dasmithjones" wrote in message ... write macro that copies any numbers in a cell & appends those numbers proceeded by a "." to the contents of another cell, same row? example: r1c1 r1c2 Boxholder PO Boxholder 123 the macro would copie the number value of r1c2 (123) and append the contents of r1c1 preceded by a period. Results desired: Boxholder.123 PO Boxholder 123 Macro would have a control+c shortcut and would execute only once for the selected row. Thanks, -- DASJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am trying to write a macro using If...Then | Excel Discussion (Misc queries) | |||
How to write a macro?? | Excel Discussion (Misc queries) | |||
How do i write a Macro that does following | New Users to Excel | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
How do I write a macro for... | Excel Discussion (Misc queries) |