Expert: Tom Ogilvy - 7/9/2009

Question
QUESTION: Hello,

I have some data in Column A starting from Row 3. Im looking for a macro which will Run and copy A3 data and add " is from 10th class" to it using (=A3&" is from 10th class") and put that data in Column G in Same Row. Then it should go to Next Row in Column A and do the same activity and put the data in Column G in the same Row. Something like this (=A4&" is from 10th class") and continue till it finds empty cell in Column A. Then it should stop.

Can you please help me with it.

Thank you.

ANSWER: Mohd,

Sub buildformula()
Dim r As Range
Set r = Range("A3", Range("A3").End(xlDown))
r.Offset(0, 6).Formula = "=A3&"" is from 10th class"""
End Sub

Put that in a general module such as Module1 and then run it.

It worked for me.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hello,

This part is completly perfect without defect or blemish. Thanks for your quickest resolution.

If i want to make A3 value as link and also text that Hyperlink as "Link". How can i do it.

I tried it like this but it dint work.

Sub buildformula()
Dim r As Range
Set r = Range("A3", Range("A3").End(xlDown))
r.Offset(0, 6) = "=""http://www."" & A3 & "".com"""
Macro3
End Sub

Sub Macro3()
Dim linkIt As String

linkIt = Range("G3", Range("A3").End(xlDown)).Value
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
   linkIt, TextToDisplay:="Link"
End Sub


Can you help me in rectifing the error in my above code.

Thank you!!!

ANSWER: Mohd,




Sub Macro3()
Dim linkIt As String

linkIt = Range("A3").Value
  ActiveSheet.Hyperlinks.Add Anchor:=Range("A3"), Address:= _
  linkIt, TextToDisplay:="Link"
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Many thanks Tom.

But it is not doing for all the values in Column G (Sorry i didnt mention it in previous mail - My apologies). And also it is not changing the Link name to "Link". I tried this below as you said.

********************************************************************

Sub buildformula()
Dim r As Range
Set r = Range("A3", Range("A3").End(xlDown))
r.Offset(0, 6) = "=""http://www."" & A3 & "".com"""
Macro3
End Sub


Sub Macro3()
Dim linkIt As String

linkIt = Range("G3").Value
 ActiveSheet.Hyperlinks.Add Anchor:=Range("G3"), Address:= _
 linkIt, TextToDisplay:="Link"
End Sub

********************************************************************

Below is the result i got:

Google      http://www.google.com (This is a Link but TextToDisplay is not changed to "LINK")
msn      http://www.msn.com (This is a simple text)
hotmail      http://www.hotmail.com (This is a simple text)
Ibm      http://www.ibm.com (This is a simple text)
wipro      http://www.wipro.com (This is a simple text)
satyam      http://www.satyam.com (This is a simple text)

It should like this below

Google      LINK    = > (This is the Link for http://www.google.com)
msn      LINK    = > (This is the Link for http://www.msn.com)
hotmail      LINK    = > (This is the Link for http://www.hotmail.com)
Ibm      LINK    = > (This is the Link for http://www.ibm.com)
wipro      LINK    = > (This is the Link for http://www.wipro.com)
satyam      LINK    = > (This is the Link for http://www.satyam.com)

Please suggest!!!
Thanks again.

Answer
Mohd.

That isn't what you asked for.  If that is what you want:

Sub buildformula()
Dim r As Range, cell As Range
Dim linkIt As String, Linkform As String
Set r = Range("A3", Range("A3").End(xlDown))
r.clear
For Each cell In r
linkIt = cell.Value & " LINK"
Linkform = "http://www." & cell.Value & ".com"
Debug.Print cell.Address, Linkform
ActiveSheet.Hyperlinks.Add Anchor:=cell.Offset(0, 6), Address:= _
Linkform, TextToDisplay:=linkIt
Next
End Sub

That worked fine for me.

--
Regards,
Tom Ogilvy