AboutTom Ogilvy Expertise Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
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.
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
