Macro Question

[Chuck W]

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

 

[Frank Kabel]

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

 

[Guest]

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

 

[Frank Kabel]

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

 

[Chuck W]

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

 

[Frank Kabel]

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

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



.