Concat Macro help...
 

Fist - I have no idea about macros - just recorded one and am trying to
modify it to fit my needs... so thank you for any help.

I have en excel spread sheet and I need the following

A2... First Names Given
B2... Last Names Given
H2... Needed First Initial + Last Name
L2... Needed First Name, Space, Last Name

Used the following macro... when I use the formulas alone, they
work... the macro changes the formula (adds some ' around the cells
which break the formulas and give me some #NAME? errors)


Sub Concat()
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"),
Type:=xlFillDefault
Range("H2:H30").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L30"),
Type:=xlFillDefault
Range("L2:L30").Select
End Sub



Also I have a column of number and would like to add 5 common letter
before each numbers


example the column has "1234" - I want the macro to make it
ASDFG1234

the letters will be the same for all the effected cells....


Thanks
JD

Concat Macro help...
 

If you're going to use .formular1c1, then you have to use R1C1 reference style
in your formulas--not A2 and B2.

with activesheet
.range("H2:H30").formulaR1C1 = "=left(rc[-7],1)&rc[-6]"
.range("l2:L30").formulaR1C1 = "=rc[-11]&"" ""&rc[-10]"
end with

rc[-7]
means same row, 7 columns to the left

And I plopped the formulas into the range all at once--instead of using
Autofill.

And if you wanted values:

with activesheet
with .range("H2:H30")
.formulaR1C1 = "=left(rc[-7],1)&rc[-6]"
.value = .value
end with

with .range("l2:L30")
.formulaR1C1 = "=rc[-11]&"" ""&rc[-10]"
.value = .value
end with

end with

wrote:

Fist - I have no idea about macros - just recorded one and am trying to
modify it to fit my needs... so thank you for any help.

I have en excel spread sheet and I need the following

A2... First Names Given
B2... Last Names Given
H2... Needed First Initial + Last Name
L2... Needed First Name, Space, Last Name

Used the following macro... when I use the formulas alone, they
work... the macro changes the formula (adds some ' around the cells
which break the formulas and give me some #NAME? errors)

Sub Concat()
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"),
Type:=xlFillDefault
Range("H2:H30").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L30"),
Type:=xlFillDefault
Range("L2:L30").Select
End Sub

Also I have a column of number and would like to add 5 common letter
before each numbers

example the column has "1234" - I want the macro to make it
ASDFG1234

the letters will be the same for all the effected cells....

Thanks
JD

--

Dave Peterson

Concat Macro help...
 

I didn't notice the second question...

Dim myRng as range
dim myCell as range
set myrng = activesheet.range("g1:G20")

for each mycell in myrng.cells
mycell.value = "asdfg" & format(mycell.value,"0000")
next mycell

An alternative would be to change the number formatting.
Select the range
format|Cells|Number tab|Custom Category
"asdfg"0000

The value of the cell is still a number--it just makes it look "nicer".

wrote:

Fist - I have no idea about macros - just recorded one and am trying to
modify it to fit my needs... so thank you for any help.

I have en excel spread sheet and I need the following

A2... First Names Given
B2... Last Names Given
H2... Needed First Initial + Last Name
L2... Needed First Name, Space, Last Name

Used the following macro... when I use the formulas alone, they
work... the macro changes the formula (adds some ' around the cells
which break the formulas and give me some #NAME? errors)

Sub Concat()
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"),
Type:=xlFillDefault
Range("H2:H30").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L30"),
Type:=xlFillDefault
Range("L2:L30").Select
End Sub

Also I have a column of number and would like to add 5 common letter
before each numbers

example the column has "1234" - I want the macro to make it
ASDFG1234

the letters will be the same for all the effected cells....

Thanks
JD

--

Dave Peterson

Concat Macro help...
 

Take the R1C1 references off your formula lines......
Change
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
To
ActiveCell.Formula = "=CONCATENATE(LEFT(A2,1),B2)"

and change
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
To
ActiveCell.Formula = "=CONCATENATE(A2,"" "",B2)"


Should be ok then.....
hth
Vaya con Dios,
Chuck, CABGx3



" wrote:

Fist - I have no idea about macros - just recorded one and am trying to
modify it to fit my needs... so thank you for any help.

I have en excel spread sheet and I need the following

A2... First Names Given
B2... Last Names Given
H2... Needed First Initial + Last Name
L2... Needed First Name, Space, Last Name

Used the following macro... when I use the formulas alone, they
work... the macro changes the formula (adds some ' around the cells
which break the formulas and give me some #NAME? errors)


Sub Concat()
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"),
Type:=xlFillDefault
Range("H2:H30").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L30"),
Type:=xlFillDefault
Range("L2:L30").Select
End Sub



Also I have a column of number and would like to add 5 common letter
before each numbers


example the column has "1234" - I want the macro to make it
ASDFG1234

the letters will be the same for all the effected cells....


Thanks
JD

Concat Macro help...
 

Sorry, I missed the second question also, but I see Dave has already answered
it.......

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Take the R1C1 references off your formula lines......
Change
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
To
ActiveCell.Formula = "=CONCATENATE(LEFT(A2,1),B2)"

and change
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
To
ActiveCell.Formula = "=CONCATENATE(A2,"" "",B2)"


Should be ok then.....
hth
Vaya con Dios,
Chuck, CABGx3



" wrote:

Fist - I have no idea about macros - just recorded one and am trying to
modify it to fit my needs... so thank you for any help.

I have en excel spread sheet and I need the following

A2... First Names Given
B2... Last Names Given
H2... Needed First Initial + Last Name
L2... Needed First Name, Space, Last Name

Used the following macro... when I use the formulas alone, they
work... the macro changes the formula (adds some ' around the cells
which break the formulas and give me some #NAME? errors)


Sub Concat()
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(A2,1),B2)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"),
Type:=xlFillDefault
Range("H2:H30").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(A2,"" "",B2)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L30"),
Type:=xlFillDefault
Range("L2:L30").Select
End Sub



Also I have a column of number and would like to add 5 common letter
before each numbers


example the column has "1234" - I want the macro to make it
ASDFG1234

the letters will be the same for all the effected cells....


Thanks
JD