Macro Question #10

Hi,

I am working with a text file that I imported into Excel. 
It is a transactional report of sales by customer.  The 
data will have the customer number and name spread out 
over three cells.  What I want to do is to create a macro 
that will contatenate all three cell and then perform a 
paste special and paste them into another cell in another 
column.  Since the number of transactions by customer can 
vary the customer name and number can appear anywhere on 
the page.  

I created a macro to do this using the first customer 
which appears in cell B5 through D5. The next customer 
appears in B26 through D26 and the third in B91 through 
D91.  When I tried running the macro in row 26 and row 91 
it kept reverting back to row 5.  Basically I want the 
macro to run in whatever row I tell it to rather than row 
5 which is where I set it up.  Can anyone help?

Thanks,

Chuck
0
anonymous (74722)
3/5/2004 2:23:48 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
316 Views

Similar Articles

[PageSpeed] 42

Hi Chuck
you may post your code (probably some abolute references in it) so we
can change it to your needs

--
Regards
Frank Kabel
Frankfurt, Germany

Chuck W wrote:
> Hi,
>
> I am working with a text file that I imported into Excel.
> It is a transactional report of sales by customer.  The
> data will have the customer number and name spread out
> over three cells.  What I want to do is to create a macro
> that will contatenate all three cell and then perform a
> paste special and paste them into another cell in another
> column.  Since the number of transactions by customer can
> vary the customer name and number can appear anywhere on
> the page.
>
> I created a macro to do this using the first customer
> which appears in cell B5 through D5. The next customer
> appears in B26 through D26 and the third in B91 through
> D91.  When I tried running the macro in row 26 and row 91
> it kept reverting back to row 5.  Basically I want the
> macro to run in whatever row I tell it to rather than row
> 5 which is where I set it up.  Can anyone help?
>
> Thanks,
>
> Chuck

0
frank.kabel (11126)
3/5/2004 2:33:17 PM
Frank,

I am not very familiar with this. I am a bit of a novice 
user.  How do I post the code?  Is this VBA code?

Thanks,

Chuck
>-----Original Message-----
>Hi Chuck
>you may post your code (probably some abolute references 
in it) so we
>can change it to your needs
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>Chuck W wrote:
>> Hi,
>>
>> I am working with a text file that I imported into 
Excel.
>> It is a transactional report of sales by customer.  The
>> data will have the customer number and name spread out
>> over three cells.  What I want to do is to create a 
macro
>> that will contatenate all three cell and then perform a
>> paste special and paste them into another cell in 
another
>> column.  Since the number of transactions by customer 
can
>> vary the customer name and number can appear anywhere 
on
>> the page.
>>
>> I created a macro to do this using the first customer
>> which appears in cell B5 through D5. The next customer
>> appears in B26 through D26 and the third in B91 through
>> D91.  When I tried running the macro in row 26 and row 
91
>> it kept reverting back to row 5.  Basically I want the
>> macro to run in whatever row I tell it to rather than 
row
>> 5 which is where I set it up.  Can anyone help?
>>
>> Thanks,
>>
>> Chuck
>
>.
>
0
anonymous (74722)
3/5/2004 2:59:36 PM
Hi
yes this is VBA code. Try the following
- open your workbook
- goto the macro menu and open your macro for editing
- copy this code

You may also have a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Regards
Frank Kabel
Frankfurt, Germany

anonymous@discussions.microsoft.com wrote:
> Frank,
>
> I am not very familiar with this. I am a bit of a novice
> user.  How do I post the code?  Is this VBA code?
>
> Thanks,
>
> Chuck
>> -----Original Message-----
>> Hi Chuck
>> you may post your code (probably some abolute references in it) so
we
>> can change it to your needs
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> Chuck W wrote:
>>> Hi,
>>>
>>> I am working with a text file that I imported into Excel.
>>> It is a transactional report of sales by customer.  The
>>> data will have the customer number and name spread out
>>> over three cells.  What I want to do is to create a macro
>>> that will contatenate all three cell and then perform a
>>> paste special and paste them into another cell in another
>>> column.  Since the number of transactions by customer can
>>> vary the customer name and number can appear anywhere on
>>> the page.
>>>
>>> I created a macro to do this using the first customer
>>> which appears in cell B5 through D5. The next customer
>>> appears in B26 through D26 and the third in B91 through
>>> D91.  When I tried running the macro in row 26 and row 91
>>> it kept reverting back to row 5.  Basically I want the
>>> macro to run in whatever row I tell it to rather than row
>>> 5 which is where I set it up.  Can anyone help?
>>>
>>> Thanks,
>>>
>>> Chuck
>>
>> .

0
frank.kabel (11126)
3/5/2004 3:04:27 PM
Frank,

Thanks for your help.  I appreciate it.  Here is my code. 
My current macro seems to be going back to a specific 
cell (a261).  What I want to do is to click on a specific 
cell and have it concatenate the three cells to the left 
of it and then paste special it into another cell which 
is one row down from where I started and about four cells 
to the right.  It keeps going back to A261 instead.

Thanks,

Chuck

----------------------------------------------
Sub Concatenate()
'
' Concatenate Macro
' Macro recorded 3/5/2004 by Chuck Wildeman
'
' Keyboard Shortcut: Ctrl+a
'
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-3])"
    Selection.Copy
    Range("A261").Select
    Selection.PasteSpecial Paste:=xlPasteValues, 
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "ANTAL000  "
    Selection.Copy
    Range("A262").Select
    ActiveSheet.Paste
    Range("A261:A262").Select
End Sub
--------------------------------------------------



>-----Original Message-----
>Hi
>yes this is VBA code. Try the following
>- open your workbook
>- goto the macro menu and open your macro for editing
>- copy this code
>
>You may also have a look at
>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>anonymous@discussions.microsoft.com wrote:
>> Frank,
>>
>> I am not very familiar with this. I am a bit of a 
novice
>> user.  How do I post the code?  Is this VBA code?
>>
>> Thanks,
>>
>> Chuck
>>> -----Original Message-----
>>> Hi Chuck
>>> you may post your code (probably some abolute 
references in it) so
>we
>>> can change it to your needs
>>>
>>> --
>>> Regards
>>> Frank Kabel
>>> Frankfurt, Germany
>>>
>>> Chuck W wrote:
>>>> Hi,
>>>>
>>>> I am working with a text file that I imported into 
Excel.
>>>> It is a transactional report of sales by customer.  
The
>>>> data will have the customer number and name spread 
out
>>>> over three cells.  What I want to do is to create a 
macro
>>>> that will contatenate all three cell and then 
perform a
>>>> paste special and paste them into another cell in 
another
>>>> column.  Since the number of transactions by 
customer can
>>>> vary the customer name and number can appear 
anywhere on
>>>> the page.
>>>>
>>>> I created a macro to do this using the first customer
>>>> which appears in cell B5 through D5. The next 
customer
>>>> appears in B26 through D26 and the third in B91 
through
>>>> D91.  When I tried running the macro in row 26 and 
row 91
>>>> it kept reverting back to row 5.  Basically I want 
the
>>>> macro to run in whatever row I tell it to rather 
than row
>>>> 5 which is where I set it up.  Can anyone help?
>>>>
>>>> Thanks,
>>>>
>>>> Chuck
>>>
>>> .
>
>.
>
0
anonymous (74722)
3/5/2004 4:53:38 PM
Hi Chuck
if I understood you correctly you only want to 'copy' the concatenate
value. Try the following macro (just a simple concatenation and cell
value change - no copy/paste needed):

Sub Concatenate()
Dim rng As Range
Dim concat_value
Set rng = ActiveCell
With rng
    concat_value = .Offset(0, -3).Value & .Offset(0, -2).Value & _
        .Offset(0, -1).Value
    .Offset(1, 4).Value = concat_value

End With
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Chuck W wrote:
> Frank,
>
> Thanks for your help.  I appreciate it.  Here is my code.
> My current macro seems to be going back to a specific
> cell (a261).  What I want to do is to click on a specific
> cell and have it concatenate the three cells to the left
> of it and then paste special it into another cell which
> is one row down from where I started and about four cells
> to the right.  It keeps going back to A261 instead.
>
> Thanks,
>
> Chuck
>
> ----------------------------------------------
> Sub Concatenate()
> '
> ' Concatenate Macro
> ' Macro recorded 3/5/2004 by Chuck Wildeman
> '
> ' Keyboard Shortcut: Ctrl+a
> '
>     ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-3])"
>     Selection.Copy
>     Range("A261").Select
>     Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone, SkipBlanks _
>         :=False, Transpose:=False
>     Application.CutCopyMode = False
>     ActiveCell.FormulaR1C1 = "ANTAL000  "
>     Selection.Copy
>     Range("A262").Select
>     ActiveSheet.Paste
>     Range("A261:A262").Select
> End Sub
> --------------------------------------------------
>
>
>
>> -----Original Message-----
>> Hi
>> yes this is VBA code. Try the following
>> - open your workbook
>> - goto the macro menu and open your macro for editing
>> - copy this code
>>
>> You may also have a look at
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> anonymous@discussions.microsoft.com wrote:
>>> Frank,
>>>
>>> I am not very familiar with this. I am a bit of a novice
>>> user.  How do I post the code?  Is this VBA code?
>>>
>>> Thanks,
>>>
>>> Chuck
>>>> -----Original Message-----
>>>> Hi Chuck
>>>> you may post your code (probably some abolute references in it) so
>>>> we can change it to your needs
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>>
>>>> Chuck W wrote:
>>>>> Hi,
>>>>>
>>>>> I am working with a text file that I imported into Excel.
>>>>> It is a transactional report of sales by customer. The
>>>>> data will have the customer number and name spread out
>>>>> over three cells.  What I want to do is to create a macro
>>>>> that will contatenate all three cell and then perform a
>>>>> paste special and paste them into another cell in another
>>>>> column.  Since the number of transactions by customer can
>>>>> vary the customer name and number can appear anywhere on
>>>>> the page.
>>>>>
>>>>> I created a macro to do this using the first customer
>>>>> which appears in cell B5 through D5. The next customer
>>>>> appears in B26 through D26 and the third in B91 through
>>>>> D91.  When I tried running the macro in row 26 and row 91
>>>>> it kept reverting back to row 5.  Basically I want the
>>>>> macro to run in whatever row I tell it to rather than row
>>>>> 5 which is where I set it up.  Can anyone help?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Chuck
>>>>
>>>> .
>>
>> .

0
frank.kabel (11126)
3/5/2004 6:58:02 PM
Reply:

Similar Artilces:

Saving Question
Why does Excel sometimes ask me if I want to save my spreadhseet even though I haven't made any changes? For example, I just brought it up, made no changes, did nothing, and when I close it out I'm asked if I want to save. Never understood that. Some functions are "volatile" and force a recalculation upon opening the workbook. These commands include TODAY(), NOW(), AREAS() and some others I can't think of right now. Once the workbook is recalced, Excel assumes that it has changed, even if you didn't really change anything. -- Cordially, Chip Pearson Microsoft...

Switchboard hangs when running a macro
Access 2003: I've recently discovered the Switchboard - a grand concept; wish it wasn't so hidden... When running a macro from a form, no problem. But when running the same macro from the Switchboard, Access hangs. It seems to run all of the steps in the macro, and finish, but then Access hangs. Any help would be appreciated. Thanks, Mike in Sugar Land, TX On Mon, 30 Nov 2009 21:28:03 -0800, Switchboard newbie <Switchboard newbie@discussions.microsoft.com> wrote: My crystal ball is broken. Can you post some code, stripped to the bare essentials? -Tom. ...

Tab control question about updates
Searched but couldn't see this discussed. I have a form with 5 tabs. Data can be loaded three ways, manually entered, imported from a text file, and imported from a Word template. On one of the tabs (the second) when the user enters information such as a social sec. number (SSN) that matches a file already in the system, a flag goes off and alerts the user. Works fine. On the import functions, the data is loaded and appears correctly, but when the user moves to the second tab where the SSN number appears, it does not set off the flag when the page opens. I can make it happen with a c...

Trigger Macro on Open Spreadsheet
How can we force a macro to execute whenever a spreadsheet is first opened? Hi do you mean the file? If yes either name your macro Auto_Open() or put your code in the workbook event Workbook_Open() -- Regards Frank Kabel Frankfurt, Germany Beckie Davis wrote: > How can we force a macro to execute whenever a spreadsheet > is first opened? ...

Balancing Question
As posted before, I never got my July/August statement from my bank. I know my statement is up to date, and when I tried to balance it with september, it left me with 200 bucks in bank charges as a descrepency because the starting/ending balance was different. My bank charges me 5.00 for each statement I need because they don't offer it on their webpage. My question is this: Do I have any other option to fix my account/balancing problem besides paying 10 dollars for old statements? Would reinstalling money and starting a new money file with just my September purchases/withdrawls, and ba...

outlook 2007 cached mode question
With the cached mode a copy of the mailbox is stored locally. Does this mean that a user can go to another user's cached copy and open/read it? Each of my users has to be a local administrator because of a records management system we use, so each person's "private" folders are mapped to a shared network drive where security settings restrict access. If a local administrator would have access to the cached copies of mailboxes I would have to manually change the storage location. If I disable the cached mode what, if any, functionality do I lose? Bill Bill <trai...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

Keeping track of which listbox has focus: Best approach question
Access 2007 working in compatibility mode with a 2003 database. I have three list boxes on a tab page. I also have buttons that operate based on which list box has focus. So my approach has been to keep track of which list box has focus by having the following kind of code in the On Got Focus function of each list box: Private Sub lbx_suspendedIndefinitely_manageMyTasks_GotFocus() If Not (IsNull(Me.lbx_suspendedIndefinitely_manageMyTasks)) Then Set suspendedListBoxWithFocus = Me.lbx_suspendedIndefinitely_manageMyTasks End If End Sub in which suspendedListBoxW...

excel 06-05-10
I have a spread sheet and in it i have data validation tables when i finish picking from them and email the sheet i then want to clear the info in the tables but not the data validation table and not usr macro I'm not exactly sure about what you want to do, Is the whole range filled in with data validation in the cells to create your table or only part. If you can select all the cells with data validation then you can press delete on the keyboard to clear all the contents, but the data validation remains. Or if this is difficult because the data validation is dotted about the...

Bank Recon GP 10 problem
Hi We are a VAR that's upgraded one client to GP 10 and installed GP 10 for another new client, both on SQL 2000. In both cases there's an incorrect balance showing for Cashbook Balance field (cashbook maintenance screen) using the Bank Recon module. Also the cashbook balance is not re-calculated (recon screen) when "Incl Trans To Date" field is changed, for both clients. We can update the balance in Cashbook Master table (CM00100) in SQL but this doesn't solve the problem. Any suggestions? ...

Outlook 2007 question
I installed outlook 2007 and now I try to customise it but I notice that the Tools/Opions and Tools/Customize menus are disabled. Why and how to enable them? thanks I notice now that the problem is more severe. I cannot reply to messages, save is not working. It seems that most of the functionality is disabled. I can receive emails but cannot answer them. Any clue? this is very bothering. Thanks! "YLY" wrote: > I installed outlook 2007 and now I try to customise it but I notice that the > Tools/Opions and Tools/Customize menus are disabled. Why and how to enable > t...

Question #10
If possible... How do I get excel to change the color of a "grid" if I enter say specfic letter or word in it. Example. 800 columns by 800 rows All columns and rows are small as possible Each letter would mean something, and I would need the grid with tha letter to be a specifc color. To allow easy viewing of the information V = red R = grey H = blue So instead of filling in each color per grid myself, I would get exce to auto-fill Any help would be greatly appreciat -- dirtytongu ----------------------------------------------------------------------- dirtytongue's Profi...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

2 Formatting Questions
Date Fields: Can we, in the CRM forms, format dates to read into the “15 Aug 2005” format? This is possible in reports, but by default, the forms use only numbers for dates, but in any format. Currency: Any ideas of how to track multiple currencies in the same application - specifically USD and Canadian? I know a Microsoft CRM installation currently supports one language. But aside from using a picklist to specify "USD", "CAN", "MEX", or other I'm concerned about how their forecasting. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.c...

Macro or VBA
I am dumb, but would like to automatically trim down reports that we get from our IT dept. We have to delete redundant columns (A,B,C,D,E,G,H,J,K)from each report. In other words, the only data we want to view is in F,I,J,P. Can this done more effciently with a macro or VBA/ Could someone give me an example of either so that I could work from and learn? Thank-You start a new workbook open one of those report workbooks record a macro into that new workbook (not the report workbook) when you delete the columns you don't want. stop recording back to that new workbook save it as a nice...

VBA date question
Why isn't this working? I have a form which has a text box for an attribute of type DATE. This attribute, foo, is normally null when the form is loaded. I tried setting the default value for the text box to "Date()" but it doesn't show up (i'm thinking it's because I'm actually running a join query to generate the record for the form so the form never gets the opportunity to display the default value for foo since it thinks it's supposed to be null, the result from the query) So I added some code like this: Private Sub Form_Open(Cancel As Integer) If (I...

Access 2007 Rich Text Question 04-28-07
Hi All In the plan text version of a Memo field you can easily add a date by pressing Ctrl+Colon which makes life easy for the end user. However, if the Memo field is set to the Rich Text Property this no longer works. Or am I missing something? Tom gave me a part answer last week but I cannot see a way of inserting a date on a Rich Text Memo field by using keystrokes Thanks ...

Setting Up Windows Mail 01-05-10
I am having trouble setting up windows mail. This is the message I get "The connection to the server has failed. Subject 'Hello', Account: 'Work', Server: 'pop3', Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 10060, Error Number: 0x800CCC0E" I don't know what to enter when I go into accounts & properties. I have Time Warner Cable internet and I am working off of a laptop. "NormaH" <NormaH@discussions.microsoft.com> wrote in message news:B119C5BC-0455-4A2B-8346-FD86236D0F9C@microsoft.com... >I am having ...

A Question about CTreeCntrl
I'm writing an application from an example that uses a CTreeCtrl. I'm don't understand the usage of the InsertItem methods and its parameters. HTREEITEM InsertItem( LPTVINSERTSTRUCT lpInsertStruct ); HTREEITEM InsertItem( UINT nMask, LPCTSTR lpszItem, int nImage, int nSelectedImage, UINT nState, UINT nStateMask, LPARAM lParam, HTREEITEM hParent, HTREEITEM hInsertAfter ); HTREEITEM InsertItem( LPCTSTR lpszItem, HTREEITEM hParent = TVI_ROOT, HTREEITEM hInsertAfter = TVI_LAST ); HTREEITEM InsertItem( LPCTSTR lpszItem, int nImage, int nS...

RPC/HTTP Question
I have two questions about implementing RPC over HTTP: I need to setup about 4 users to work from home over broadband..... I am considering the use of RPC/HTTP but have some questions about it. 1) Can RPC/HTTP be implemented on a single Exchange 2003 server? 2) Once implemented, will the data be downloaded on the user's remote desktops (i.e, PF,? Thank you , Rafael Rafael wrote: > I have two questions about implementing RPC over HTTP: > > I need to setup about 4 users to work from home over broadband..... I > am considering the use of RPC/HTTP but have some questions ab...

Vlookup question #8
Hi- I have a spreadsheet that looks at individuals and the amount they paid within a certain year for healthcare expenditures. Based upon this level of expenditure, I would like Excel to spit out the Category that the individual falls into. Here is what the table for looking up the category looks like: A B C Category Min Max 00 $0 $20 01 $20 $165 Thus, if the expenditure is $17, they would fall into category 00. How do I do this in Excel? I know its probably an easy VLOOKUP function but I'm not sure how to do the VLOOKUP function when telling Excel th...

my question did not post
I've posted a question that didn't show up in the excel community so I posted it again. Although I did find both when I clicked on my name and recent posts by user...what am I doing wrong? Where will this on go... :) Rene, These newsgroups will die in a few days. We will all meet again at http://social.answers.microsoft.com/Forums/en-US/category/officeexcel best wishes -- Bernard Liengme Microsoft Excel MVP "Rene" <Rene@discussions.microsoft.com> wrote in message news:D606C0F8-CBB8-46A8-818D-4151615980A9@microsoft.com... > I've posted a ques...

Macro #12
Why would a macro behave differently attached to a command button than when event driven through calculate worksheet? What does "behave differently" mean to you? It might help for you to post both a description of what you're concerned about, and relevant parts of your code in a reply message. A couple of things different: Macros in a regular code module may have different default behavior than macros stored in a worksheet code module - for instance Range("A1") in a regular code module is equivalent to ActiveSheet.Range("A1"). In a worksheet module, t...

RAID 1 Question on Dell SC 1420 Poweredge
I have a RAID 1 array on a Poweredge SC 1420 that recently had on drive fail. The controller is an onboard CERC SATA 1.5/2s. I have read that it is possible to upgrade to a different controller using the same disks without loosing data providing the new controller is of the same family since the RAID configuration is stored on the HD. What controllers would be considered "of the same family" as the CERC SATA 1.5/2s? I would like to use the current functional drive along with a new larger drive and after the new drive has been rebuilt replace the smaller drive with a new...

How do I assign a macro I have created to a Command Button in Exc.
I believe on previous versions of excel you right-clicked on the command button and then clicked on assign marco, but this does not seem to appear in the 2003 version of excel. Any help is appreciated. If you add a button from the Forms toolbar, you'll see this option. I think you added a commandbutton from the control toolbox toolbar. Andrew7675 wrote: > > I believe on previous versions of excel you right-clicked on the command > button and then clicked on assign marco, but this does not seem to appear in > the 2003 version of excel. Any help is appreciated. -- D...