Copy paste value formula need to be validated for it to display the result

[nicnad]

Hi,

I have a macro that concatenate some cells and then copy paste value the result in another cell. The result in that cell is =hyperlink("c:\test.xls";"test").
The thing is, for this formula to actually show the result I have to manually double click in all cell and press enter. Is there a way to automatically display the result and not the formula?

Just to let you know the cell format is general not text, so this is not the issue.

Thank you.​

<!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[cb12]

Can you adapt this? It works for me.

Sub test()

Dim s

    s = Range("A1").Value + Range("A2").Value
    Range("A3").Value = "=HYPERLINK(" & s & ")"
    
End Sub

 

[cb12]

Slight edit as it just occurred to me that, unlike me, you probably don't have the comma inside one of the cells.

Sub test()

Dim s

    s = Range("A1").Value & "," & Range("A2").Value
    Range("A3").Value = "=HYPERLINK(" & s & ")"
    
End Sub

 

[nicnad]

I get an error on the line Range("A3").Value = "=HYPERLINK(" & s & ")"

Do you have any idea how to solve it?

 

[cb12]

I get an error on the line Range("A3").Value = "=HYPERLINK(" & s & ")"

Do you have any idea how to solve it?

Are the values in A1 and A2 enclosed in " "? Also if the end of A1 or the beginning of A2 isn't a comma then try the second piece of code I posted.

HTH

 

[nicnad]

The value in A1 and A2 are not enclosed in " " and I used the second piece of your code but it still doesn't work. It seems like vba won't let me put an "=function" (i.e. =hyperlink) with a Range("**).Value

Anyone got any idea why this isn't working?

 

[cb12]

The values in A1 and A2 need to be in "". Apologies for not making that clear last night. I'm not sure there's any other way to do it.

<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=239><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 169pt; mso-width-source: userset; mso-width-alt: 8228" width=225><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 11pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=14></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 169pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=225>

A​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>"c:\test.xls"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>"test"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>=HYPERLINK("c:\test.xls","test")</TD></TR></TBODY></TABLE>​

For web links for might need to use Char(34) [chr(34) in vba] to place the " " around the link.​