Hi
in this case you could try (using Tom's corection - thanks for that)

with activecell.offset(0,-1)
.FormulaR1C1 = "=RC[2] & ""-"" & RC[3]" _
& "& ""-"" & RC[4]"
.Value = .Value 'to convert to a value
end with


--
Regards
Frank Kabel
Frankfurt, Germany


CLR wrote:
Thanks Frank...........

That seems to be along the right lines, but not exactly what I'm
looking for.......probably my explanation was insufficient.

I want to be able to highlight a cell, anywhere, and the macro will
insert the concatenation formula of the cell to the left of that
selection and the three cells to the right of the selection, and then
extract only the text value of that formula to leave in the cell, so
my existing macro can turn that text value into a link. No other
cells are to be affected, only the selected one.

Your code seems to treat F1:F10 all at once, regardless of where the
selected cell is.

TIA
Vaya conDios,
Chuck, CABGx3



"Frank Kabel" wrote in message
...
Hi
use the formulaR1C1 property. e.g.
if you enter the formula in column F try

for i = 1 to 10
cells(i,6).formular1c1="=R[0]C1 & ""-"" & R[0]C3 & ""-"" &
R[0]C4 & ""-"" & R[0]C5"
cells(i,6).value=cells(i,6).value 'to convert to a value
next i




--
Regards
Frank Kabel
Frankfurt, Germany


CLR wrote:
Hi All.......

I need a macro, if you please, that will put a Concatenation
formula in the active cell that will Concatenate the cell to the
left of the active cell and the three cells to the right of the
active cell, with _underscore_ separations.

Like in B1 =A1&"_"&C1&"_"&D1&"_"&E1 except when I try it I get
hardcoded cell addresses.

I want to be able to highlight any cell in the column, run the
macro, and get the desired results from that row.

Then, I would like the result to be as though it was Copy Paste
special Value (ie text only, no formula)

I then have a macro that will turn that text into a link, (it works
fine but leaves the cell value as a formula if it was one to begin
with). I would like to merge the two into one macro.

Sub makeLinks()
'Takes whatever text is in ActiveCell and turns it into a link
Dim lnk As String
lnk = ActiveCell.Text
ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:=lnk
End Sub



TIA
Vaya con Dios,
Chuck, CABGx3