Sleeper: Loop through range to copy/paste hyperlink

[pdeshazier]

For all cells in a column, i need to make a hyperlink and i wrote the following macro to copy the 'display as text' property to the 'hyperlink' property (hope i'm using correct lingo). Now, I need to put this in a loop so the macro will go through the entire column and make the appropriate hyperlinks. Have tried to do simple loops (but i really have no experience w/loops in VBA) and keep getting error messages, so can someone please help??? Thanks.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.meditech.com/dts/srupd/Re...AR&ReqNum=9524" _
, TextToDisplay:= _
"www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524"

[Bob Phillips]

For all cells in a column, i need to make a hyperlink and i wrote the following macro to copy the 'display as text' property to the 'hyperlink' property (hope i'm using correct lingo). Now, I need to put this in a loop so the macro will go through the entire column and make the appropriate hyperlinks. Have tried to do simple loops (but i really have no experience w/loops in VBA) and keep getting error messages, so can someone please help??? Thanks.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.meditech.com/dts/srupd/Re...AR&ReqNum=9524" _
, TextToDisplay:= _
"<A href="http://www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524"[/QUOTE" target=_blank>www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524"

Dim iLastRow As Long
Dim i As Long 
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To iLastRow
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), _
                                   Address:=Cells(i, "A").Value
    Next i

[pdeshazier]

This code works (THANKS)! However, once the hyperlinks are created (which point to a certain website) and I click on them, i get an excel error "cannot open the specified file." Any idea why? thanks.

[pdeshazier]

I think it's because the code above doesn't put the http:// in the hyperlink, but when I manually right click the cell, click hyperlink, CTRL C the 'text to display' and CTRL V into the 'hyperlink', it DOES put http:// in front. How can i incorporate this into the code above? Thanks.

[Bob Phillips]

This code works (THANKS)! However, once the hyperlinks are created (which point to a certain website) and I click on them, i get an excel error "cannot open the specified file." Any idea why? thanks.

Sorry, didn't spot that. Try this version

Dim iLastRow As Long
Dim sURL As String
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To iLastRow
        sURL = Cells(i, "A").Value
        If Left(sURL, 7) <> "http://" Then
            sURL = "http://" & sURL
        End If
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), _
                                   Address:=sURL
    Next i

[pdeshazier]

You're awesome. It worked. Just one more thing, I was an RPG IV programmer, but VBA is very different. Can you suggest how I should get started learning it? Thanks sooooooo much!!!

[pdeshazier]

One more question:
Can you tell me exactly how this statement is working to determine the last row? thanks.

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

[Bob Phillips]

I was an RPG IV programmer, but VBA is very different. Can you suggest how I should get started learning it? Thanks sooooooo much!!!

Sorry I don't know your name, so I can't address you properly

If you have programmed in RPG IV you must have the basic concepts of programming understood, loops, functions, variables, etc. (I say must, but as I have no idea of RPG IV that should really be assume ).

As such, the thing you will probably find the hardest is Object Orientatied coding (does RPG IV work on objects?) and the Excel object model. Once you have gotten to grips with these, you will be well on your way.

To help with this I would recommend

'-----------------------------------------------------------------
Excel VBA Programming For Dummis



Author: John Walkenbach

ISBN: 0-7645-7412-4

Publisher: Wiley, 2004, 390 pp., List price $24.99 (US)

Status: Available

Target Reader:This book is intended for experienced Excel users who want to learn the basics of VBA programming. It's good for Excel 2000, 2002, and 2003.

Comments: This is a light-hearted guide to learning VBA programming. Despite the title, you'll find lots of useful information, tips, and techniques. The book doesn't have a CD, but the sample files are available to download from the Web.

Publisher's Web Site: Contains downloads and a sample chapter.
'-----------------------------------------------------------------

Don't be put off by the 'For Dummies' title, it is quality stuff. I append a chapter list at the end for you to see the topics covered.

After that, when you have achieved some proficiency, you might want to move onto

'-----------------------------------------------------------------
Excel 2002 VBA Programmer's Reference



Author(s): John Green Rob Bovey Stephen Bullen Robert Rosenberg]
Format: Paperback, 2nd ed., 600pp.
ISBN: 1-861005-70-9
Publisher: Wrox
'-----------------------------------------------------------------

Be careful with this one though. There is a revised 2003 version. Do NOT buy the 2003 version, the revision has been badly done, not a patch on the 2002 version.

You might also think this covers much of the same ground as John's book, so check out for yourself.

The other thing to do is to visit newsgroups and/or forums like this and check out what questions are being asked and the responses, and have a go at answering some yourself. Even if your reply gets kicked back (hopefully nicely), you will learn something.

And most important of all, get yourself a meaningful project. something you want to automate, that will allow you to learn productively.

Anyway, the ToC as promised




Table of Contents

Part I: Introducing VBA

• Chapter 1. What is VBA?
• Chapter 2. Jumping Right In

Part II: How VBA Works With Excel

• Chapter 3. Introducing the Visual Basic Editor
• Chapter 4. Introducing Excel's Object Model
• Chapter 5. VBA Sub and Function Procedures
• Chapter 6. Using the Excel Macro Recorder

Part III: Programming Concepts

• Chapter 7. Essential VBA Language Elements
• Chapter 8. Working With Range Objects
• Chapter 9. Using VBA and Worksheet Functions
• Chapter 10. Controlling Program Flow and Making Decisions
• Chapter 11. Automatic Procedures and Events
• Chapter 12. Error-Handling Techniques
• Chapter 13. Bug Extermination Techniques
• Chapter 14. VBA Programming Examples

Part IV: Developing Custom Dialog Boxes

• Chapter 15. Custom Dialog Box Alternatives
• Chapter 16. Custom Dialog Box Basics
• Chapter 17. Using Dialog Box Controls
• Chapter 18. Dialog Boxes Techniques and Tricks

Part V: Creating Custom Menus and Toolbars

• Chapter 19. Customizing the Excel Toolbars
• Chapter 20. When the Normal Excel Menus Aren't Good Enough

Part VI: Putting It All Together

• Chapter 21. Creating Worksheet Functions - And Living to Tell About It
• Chapter 22. Creating Excel Add-Ins
• Chapter 23. Interacting With Other Office Applications

Part VII: The Part of Tens

• Chapter 24. Top Ten VBA Questions (and Answers)
• Chapter 25. (Almost) Ten Excel Resources

[pdeshazier]

Another complication:
This code will need to run for every sheet I have in my workbook. The column will always be the same, but how do I incorporate into the code the ability to run it on every sheet? My goal is to have a command button on the very last sheet that when clicked will run the macro for every spreadsheet in the workbook.

[Bob Phillips]

Can you tell me exactly how this statement is working to determine the last row? thanks.
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Rows.Count is a built-in constant that tells VBA how many rows there are ina worksheet.

This is then used as a start point in the deterimination
Cells(Rows.Count,"A")
effectively starts at the last row in column A.

It then works its way up until the last non-empty cell (that is the first one it meets on the way up)
.End(xlUp)

and returns the row number
.Row

which it uses then on.

An alternative way is to look down for the row before the first empty cell
Range("A1").End(xlDown).Row

I prefer the former method in case there are any blank lines. Consider this scenario

A1: Value 1
A2: Value 2
A3: blank
A4: Value 3

the xlUp method returns 2. the xlDown methid returns 2, so Value 3 would not get processed.

[pdeshazier]

Thanks for suggesting the above tools for learning. Will go get a couple of them

[pdeshazier]

Ingenious. Thanks so much for making sense of it. i was thinking of going down rather than up, but you make a great point about the blank cells.

[pdeshazier]

Missed the name part. My name is Pam and I am soooo glad to meet you.

[Bob Phillips]

Another complication:
This code will need to run for every sheet I have in my workbook. The column will always be the same, but how do I incorporate into the code the ability to run it on every sheet? My goal is to have a command button on the very last sheet that when clicked will run the macro for every spreadsheet in the workbook.

Dim sh As Worksheet
Dim iLastRow As Long
Dim sURL As String
Dim i As Long
For Each sh In ThisWorkbook.Worksheets
        With sh
            iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 1 To iLastRow
                sURL = .Cells(i, "A").Value
                If Left(sURL, 7) <> "http://" Then
                    sURL = "http://" & sURL
                End If
                .Hyperlinks.Add Anchor:=.Cells(i, "A"), Address:=sURL
            Next i
        End With
    Next sh

[Bob Phillips]

Ingenious. Thanks so much for making sense of it. i was thinking of going down rather than up, but you make a great point about the blank cells.

There is a somewhat analagous situation when inserting or deleting rows.

If you start at the top and work you way down, the pointers can get confused, and so you may not process some lines. So it better to work bottom up in these cases.

So for instance, put these values in A1:A8 - 17,1,17,2,17,17,17,3, then run this code

Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub

We were supposed to insert a blank row abover every 17, not quite what we achieved ).

Restore the data, make this minor change to the code

Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub

and run it again. Success!

[Bob Phillips]

Missed the name part. My name is Pam and I am soooo glad to meet you.

Pleased to meet you too Pam, and welcome to VBAX.

Best Regards

Bob

[pdeshazier]

Thanks a million times for your help. You've made me and my coworkers very happy! I hope to work w/you again in the future as I get more experience.

[pdeshazier]

Dim sh As Worksheet
Dim iLastRow As Long

I open my workbook and run this code, but it only executes on the worksheet my cursor is on. How do I get it to execute for all sheets?

Dim sURL As String
Dim i As Long
For Each sh In ThisWorkbook.Worksheets
With sh
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
sURL = .Cells(i, "A").Value
If Left(sURL, 7) <> "http://" Then
sURL = "http://" & sURL
End If
.Hyperlinks.Add Anchor:=.Cells(i, "A"), Address:=sURL
Next i
End With
Next sh

[Bob Phillips]

I open my workbook and run this code, but it only executes on the worksheet my cursor is on. How do I get it to execute for all sheets?

Pam,

It should execute on every sheet. What it doesn't do is to activate those sheets, so you won't see it happening. But if you go to the other sheets, you should see that they have been linked okay.

[pdeshazier]

It's not executing on every sheet. Only the sheet the cursor happens to be on. Any way I can attach my file? I uploaded it, but don't see that it's attached so you can see it..