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