0

Given a sample plain excel file here, what is the VBA code to copy value, formation and hyperlink of a cell?

i.e. At cell B5 we will call =myCopyCellFunction(B2) and we will get the exact cell value at B2

enter image description here

Community
  • 1
  • 1
Nam G VU
  • 33,193
  • 69
  • 233
  • 372
  • 1
    you want to copy a cell's value and format or formula? Have you tried recording some code? Please show us the code you're having trouble with – Our Man in Bananas May 13 '13 at 15:28
  • 2
    I don't think you can use a User Defined Function to do anything in Excel other than return value(s). If I understand your question correctly you want it to insert a hyperlink? You will have to do this using a macro rather than a UDF (i.e. change your UDF from a Function to a Sub). – steveo40 May 13 '13 at 15:32
  • why do you think you need a UDF? Tell us what you are trying to accomplish, and show us your code so we can help – Our Man in Bananas May 13 '13 at 16:00
  • @Philip THe B2 cell is the content copied from wikipedia with hyperlink and nice text formation. I just want to bring the original cell to another table where I manupilate the wiki data. It would be lovely if Excel have another operator, says `f=`, besiade `=' operator – Nam G VU May 13 '13 at 20:33
  • @NamG.VU try my answer below – Our Man in Bananas May 14 '13 at 09:00

1 Answers1

1

try this in a normal code module 

Function copyIt(ByVal vRange As Range) As String
Dim sAddress As String
Dim sText As String
'
Application.EnableEvents = False

Application.ThisCell.Hyperlinks.Delete
' handle non hyperlinks in source cell
If vRange.Hyperlinks.Count < 1 Then
    copyIt = vRange.Text
    Exit Function
End If
'
' get values for new hyperlink in target cell
sAddress = vRange.Hyperlinks(1).Address
sText = vRange.Hyperlinks(1).TextToDisplay
'
' apply link to target cell
Application.ThisCell.Hyperlinks.Add Anchor:=Application.ThisCell, Address:=sAddress, TextToDisplay:=sText
' return string value 
copyIt = sText
'    
Application.EnableEvents = True

End Function

then you use it in a cell like this:

=copyIt(B2)

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
  • I tried yours but not working for me as this sample https://dl.dropboxusercontent.com/u/6194904/2013/copy%20excel%20cell%20value%20and%20formation.xlsm – Nam G VU May 15 '13 at 16:20