EXCEL MACRO HELP

[Steph]

So i'm new at working with macros.
I have an excel sheet that allows a user to select a state and a city from
that state. Each city has a website that is identified to it. The goal is
to be able to click a button that will open the hyperlinked website for a
choosen city. At the moment I have a macro that is assigned to a button and
opens the hyperlink and imports the data, but the website will not change
when I change the city. Any suggestions?

As clarification, the website I want to be opened is run through a lookup
with a hyperlink, so by changing the city, the website changes and you can
click on the link and it will open up the website. So that portion works
correctly.

 

[Shaka215]

So i'm new at working with macros.
I have an excel sheet that allows a user to select a state and a city from
that state.  Each city has a website that is identified to it.  The goal is
to be able to click a button that will open the hyperlinked website for a
choosen city.  At the moment I have a macro that is assigned to a buttonand
opens the hyperlink and imports the data, but the website will not change
when I change the city.  Any suggestions?

As clarification, the website I want to be opened is run through a lookup
with a hyperlink, so by changing the city, the website changes and you can
click on the link and it will open up the website.  So that portion works
correctly.

Your hyperlink isn't changing because it's assuming the text is all
that's changing... you may want to consider using the santax below...

Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink = Range("A1").Value

End Sub

Range A1 would contain the URL of the webaddress your trying to get
to. This way your not dependant on the URL constantly being refreshed.
I suppose you could have the URL change in range A1 if you combined a
few cells...

Range A1 Formula
="Http://www.MyCoolCity.Com/"&A2&"/"&A2&".htm"

Range A2 = City Name either by a Vlookup statement or by the end user
typing something in.

Your message is some what confusing so I hope that this helps you out.
You can also send me your workbook

 

[Steph]

--
THANKS
~Steph

Your hyperlink isn't changing because it's assuming the text is all
that's changing... you may want to consider using the santax below...

Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink = Range("A1").Value

End Sub

Range A1 would contain the URL of the webaddress your trying to get
to. This way your not dependant on the URL constantly being refreshed.
I suppose you could have the URL change in range A1 if you combined a
few cells...

Range A1 Formula
="Http://www.MyCoolCity.Com/"&A2&"/"&A2&".htm"

Range A2 = City Name either by a Vlookup statement or by the end user
typing something in.

Your message is some what confusing so I hope that this helps you out.
You can also send me your workbook

Thanks for the help, but I don't think it is working correctly still due to
the fact that my hyperlink I want it to follow is first a VLOOKUP command.

Here is what my macro looks like that works correctly except not changing
the web address when the city is changed.

Sub data_finder()
'
' data_finder Macro
'

'
Range("G3").Select
Workbooks.Open Filename:= _
"http://www.columbus.com"
ActiveWindow.Visible = False
Windows("724339TY.csv").Visible = True
Cells.Select
Selection.Copy
Windows("Dataorganization.xlsm").Activate
Sheets("Data").Select
Cells.Select
ActiveSheet.Paste
Range("B5").Select
Sheets("Instructions").Select
Windows("724339TY.csv").Activate
ActiveWindow.Close
Sheets("Instructions").Select
End Sub

G3 is the position of the web address which looks like...
=HYPERLINK(LOOKUP(D3,O3:Q15,3,FALSE))

All values in the Q column are web address, but they are not hyperlinked
until after the LOOKUP command has run.

Does this make more sense?

 

[Shaka215]

--
THANKS
~Steph















Thanks for the help, but I don't think it is working correctly still due to
the fact that my hyperlink I want it to follow is first a VLOOKUP command.

Here is what my macro looks like that works correctly except not changing
the web address when the city is changed.

Sub data_finder()
'
' data_finder Macro
'

'
    Range("G3").Select
    Workbooks.Open Filename:= _
        "http://www.columbus.com"
    ActiveWindow.Visible = False
    Windows("724339TY.csv").Visible = True
    Cells.Select
    Selection.Copy
    Windows("Dataorganization.xlsm").Activate
    Sheets("Data").Select
    Cells.Select
    ActiveSheet.Paste
    Range("B5").Select
    Sheets("Instructions").Select
    Windows("724339TY.csv").Activate
    ActiveWindow.Close
    Sheets("Instructions").Select
End Sub

G3 is the position of the web address which looks like...
 =HYPERLINK(LOOKUP(D3,O3:Q15,3,FALSE))

All values in the Q column are web address, but they are not hyperlinked
until after the LOOKUP command has run.

Does this make more sense?- Hide quoted text -

- Show quoted text -

Step,

Your making this way to complicated -- I'm an extremely advanced
Excel programmer (enough so to the point where if I dream it -- I can
make it a reality). I don't undestand how your getting from point A to
point B.

Your code is also a bit messy -- I noticed that you had the
following...

Workbooks.Open Filename:= _
"http://www.columbus.com"

Try and replace it with this:

Activeworkbook.FollowHyperlink = "Http://www.columbus.com"

If your still stuck feel free to send me the workbook --
(e-mail address removed)

 

[Steph]

--
THANKS
~Steph

Step,

Your making this way to complicated -- I'm an extremely advanced
Excel programmer (enough so to the point where if I dream it -- I can
make it a reality). I don't undestand how your getting from point A to
point B.

Your code is also a bit messy -- I noticed that you had the
following...


Try and replace it with this:

Activeworkbook.FollowHyperlink = "Http://www.columbus.com"

If your still stuck feel free to send me the workbook --
(e-mail address removed)

I finally figured out what I was doing.