Write a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dasmithjones - 19 Jan 2006 23:22 GMT

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,

Signature

DASJ

Reply to this Message

David McRitchie - 19 Jan 2006 23:30 GMT

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

> write macro that copies any numbers in a cell & appends those numbers
> proceeded by a "." to the contents of another cell, same row?
[quoted text clipped - 16 lines]
>
> Thanks,

Reply to this Message

dasmithjones - 19 Jan 2006 23:51 GMT

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.

Signature

DASJ

> Why don't you just use a worksheet formula concatenation.
> You will have the same number of used cells either way from your description.
[quoted text clipped - 29 lines]
> >
> > Thanks,

Reply to this Message

David McRitchie - 20 Jan 2006 05:39 GMT

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

> 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
[quoted text clipped - 37 lines]
> > >
> > > Thanks,

Reply to this Message

dasmithjones - 20 Jan 2006 18:03 GMT

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?

Signature

DASJ

> Sorry, I misread the question. and it looks like a self-study question.
>
[quoted text clipped - 100 lines]
> > > >
> > > > Thanks,

Reply to this Message

David McRitchie - 20 Jan 2006 20:10 GMT

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/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

> 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
[quoted text clipped - 105 lines]
> > > > >
> > > > > Thanks,

Reply to this Message

Dave Peterson - 20 Jan 2006 17:44 GMT

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.

> write macro that copies any numbers in a cell & appends those numbers
> proceeded by a "." to the contents of another cell, same row?
[quoted text clipped - 19 lines]
> --
> DASJ

Signature

Dave Peterson

Reply to this Message

dasmithjones - 20 Jan 2006 19:19 GMT

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 !!

Signature

DASJ

> Select the rows you want to work with (multiple rows are ok)
>
[quoted text clipped - 56 lines]
> > --
> > DASJ

Reply to this Message

Dave Peterson - 20 Jan 2006 19:28 GMT

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.

> Dave-
>
[quoted text clipped - 71 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message