Concatenate formula with relative cells

[CLR]

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

 

[Frank Kabel]

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

 

[Bernie Deitrick]

CLR,

To make the hyperlink, select the cell and run this:

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Think Frank meant to post code like this:

Sub Tester2()
For i = 1 To 10
Cells(i, 6).FormulaR1C1 = "=RC[-1] & ""-"" & RC[1]" _
& "& ""-"" & RC[2] & ""-"" & RC[3]"
Cells(i, 6).Value = Cells(i, 6).Value 'to convert to a value
Next i

End Sub

--
Regards,
Tom Ogilvy

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

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

 

[CLR]

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

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

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

 

[Frank Kabel]

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

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

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

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

 

[Tom Ogilvy]

Think Frank was just demonstrating the concept - not offering a turnkey
solution.
Sub Tester2()
ActiveCell.FormulaR1C1 = "=RC[-1] & ""-"" & RC[1]" _
& "& ""-"" & RC[2] & ""-"" & RC[3]"
ActiveCell.Formula = ActiveCell.Value
End Sub

--
Regards,
Tom Ogilvy

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

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

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

 

[Bernie Deitrick]

Did you try this, which incorporates your macro?

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

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

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

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

 

[CLR]

Thanks Bernie..........

I'm a little slow, and actualy wanted to include some additional text in the
final Concatenation, but finally muddled through it and your solution worked
perfectly!!!.......

This is what I wound up with, which is just a tweaking of what you gave me
but with my added text.

Sub ConcatenateLink()
With ActiveCell
.Value = "\\Surfer\Approved Drawings\" & .Offset(0, -1).Value & "_Rev" &
..Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "of" & .Offset(0, 3).Value & ".dwg"
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub


Many, many thanks to you, and to all you guys for your offerings.........the
time and effort you guys put into answering these posts are really
appreciated.

Vaya con Dios,
Chuck, CABGx3

Did you try this, which incorporates your macro?

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

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

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

 

[CLR]

Hi Bernie...........

Just had to thank you again for that nifty macro. I went ahead and used it
the rest of the day yesterday , and believe it or not, I estimate it saved
me more than 25 minutes in just that part of the day!!!

It's really amazing how some little macro like that can consolidate so many
manual steps and significantly improve a persons productivity. And, not
only that, by not having to "switch off" to that train of thinking, I am
able to stay more focused on my larger problems..........immesurable
benefits!

Thanks ever so much again and again............

Vaya con Dios,
Chuck, CABGx3

Did you try this, which incorporates your macro?

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

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

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

 

[Bernie Deitrick]

Chuck,

Glad to hear it, and you're quite welcome. My main Excel application saves
me - truthfully - weeks of time each time I need to use it, compared to what
I used to have to do 15 years ago <vbg>

When you reach weeks of time saved, you can sign over a paycheck or two ;-)

Bernie

 

[Tom Ogilvy]

You might also want to look up the Hyperlink worksheet function.

--
Regards,
Tom Ogilvy

Hi Bernie...........

Just had to thank you again for that nifty macro. I went ahead and used it
the rest of the day yesterday , and believe it or not, I estimate it saved
me more than 25 minutes in just that part of the day!!!

It's really amazing how some little macro like that can consolidate so many
manual steps and significantly improve a persons productivity. And, not
only that, by not having to "switch off" to that train of thinking, I am
able to stay more focused on my larger problems..........immesurable
benefits!

Thanks ever so much again and again............

Vaya con Dios,
Chuck, CABGx3

Did you try this, which incorporates your macro?

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

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



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

 

[CLR]

Okey doke.........I'll give it a look........thanks for the tip Tom........

Vaya con Dios,
Chuck, CABGx3

You might also want to look up the Hyperlink worksheet function.

--
Regards,
Tom Ogilvy

Hi Bernie...........

Just had to thank you again for that nifty macro. I went ahead and used it
the rest of the day yesterday , and believe it or not, I estimate it saved
me more than 25 minutes in just that part of the day!!!

It's really amazing how some little macro like that can consolidate so many
manual steps and significantly improve a persons productivity. And, not
only that, by not having to "switch off" to that train of thinking, I am
able to stay more focused on my larger problems..........immesurable
benefits!

Thanks ever so much again and again............

Vaya con Dios,
Chuck, CABGx3

Did you try this, which incorporates your macro?

Sub TryNow()
With ActiveCell
.Value = .Offset(0, -1).Value & "_" & .Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "_" & .Offset(0, 3).Value
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

HTH,
Bernie
MS Excel MVP

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



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