Concat Macro help...

[newsgroups.jd]

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]

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

 

[Dave Peterson]

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".

 

[Guest]

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

 

[Guest]

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

Vaya con Dios,
Chuck, CABGx3