VBA Range error - How does this work and what am I doing wrong?

[Guest]

I am stuck. I have a spreadsheet that I want to be able to select certain
stocks in col A and have the charts in col b loop through. An example of my
sheet is:
COL A COL B
BBBY http://finance.yahoo.com/q?s=BBBY
BGP http://finance.yahoo.com/q?s=BGP
BKS http://finance.yahoo.com/q?s=BKS

My code follows:Sub Count_Selection()
Dim cell As Object
Dim chart As String
Dim count As Integer

count = 0
For Each cell In Selection
If cell = " " Then End

chart = ActiveCell.Offset(0, 1)

Range("chart").Select

count = count + 1

Next cell

MsgBox count & " item(s) selected"
End Sub
I get a "Methond Range of Worksheet failed" error, but I don't understand if
I should define col B as a range, and if so how to do that.

Any help you can lend someone trying to learn VBA would be greatly
appreciated.

 

[Stopher]

I am stuck. I have a spreadsheet that I want to be able to select certain
stocks in col A and have the charts in col b loop through. An example of my
sheet is:
COL A COL B
BBBY http://finance.yahoo.com/q?s=BBBY
BGP http://finance.yahoo.com/q?s=BGP
BKS http://finance.yahoo.com/q?s=BKS

My code follows:Sub Count_Selection()
Dim cell As Object
Dim chart As String
Dim count As Integer

count = 0
For Each cell In Selection
If cell = " " Then End

chart = ActiveCell.Offset(0, 1)

Range("chart").Select

count = count + 1

Next cell

MsgBox count & " item(s) selected"
End Sub
I get a "Methond Range of Worksheet failed" error, but I don't understand if
I should define col B as a range, and if so how to do that.

Any help you can lend someone trying to learn VBA would be greatly
appreciated.

Not real sure, but you have a If without end if in there as well. If
you are going to do a for next loop and you are selecting the cells
manually, is there a real need for the:

If cell = "" then End

I'm guessing that the code simple opens the links on the right hand
side? I don't think simply selecting these cells will open the links
either. You need

ActiveWorkbook.FollowHyperlink Address:="http://" &
ActiveCell.Offset(0, 1).value

or something similar to that.

Hope this help some.

 

[Stopher]

Not real sure, but you have a If without end if in there as well. If
you are going to do a for next loop and you are selecting the cells
manually, is there a real need for the:

If cell = "" then End

I'm guessing that the code simple opens the links on the right hand
side? I don't think simply selecting these cells will open the links
either. You need

ActiveWorkbook.FollowHyperlink Address:="http://" &
ActiveCell.Offset(0, 1).value

or something similar to that.

Hope this help some.

Stepped away from keyboard and had a thought. You don't really need the
links in Col B because all the links look very similar ie all same
front end with Col A tacked on the end.

Why not just use:

ActiveWorkbook.FollowHyperlink Address:="http://finance.yahoo.com/q?s="
& ActiveCell.value

This will just use the values of the cells you click on in col A.

Regards
Stopher

 

[Mike Fogleman]

Cell needs to be a Range. Also avoid variable names that Excel uses as
objects ie, Cells, charts, range, sheets, etc.
Try this. It will open each hyperlink in a new IE window for each of the
cells selected in column A.

Sub Count_Selection()
Dim c As Range
Dim rng As Range
Dim chrt As String
Dim count As Integer

chrt = "http://finance.yahoo.com/q?s="
Set rng = Selection
count = 0
For Each c In Selection
ActiveWorkbook.FollowHyperlink Address:=chrt & c.Value
count = count + 1
Next

MsgBox count & " item(s) selected"
End Sub

Mike F

 

[Mike Fogleman]

One more thing, since the hyperlink address is in the code, you don't even
need column B.

Mike F

 

[Don Guillett]

This will open a separate for each symbol in the list such as bbby(k1) &
bgp(k2)
Sub getyahoochart()
For Each c In Range("k1:k2")
ActiveWorkbook.FollowHyperlink Address:= _
"http://finance.yahoo.com/q?s=" & c
Next
End Sub

 

[Guest]

You guys rock.

Thank you so much for all the comments, code and ideas. You got me going
again....

I really appreciate it!