converting constant/text to formula and evaluate it

[tsuoying]

Hi, it's my first post here, i did a quick search on the forum but couldn't find what i was looking for, so hope someone can offer advice to the following problem:

1. I'm importing data into excel using a db query. One of the columns returned is a string that, if evaluated as a formula in Excel, would produce a cell with a hyperlink, i.e:

=HYPERLINK("http://www.yahoo.com", "Yahoo Website")

2. The problem is that when i import the data, Excel shows the above exactly as is and doesn't treat it as a formula. This is fairly logical as Excel shouldn't know that that is what i want. So my question is, how do I program Excel now to look at this column, convert it from a constant to a formula, and then evaluate so it becomes just (actively linked):

Yahoo Website

and thus allows me to click on that cell to get to the link.

3. If I went into the cell individually and pressed enter at the end of the string (in step 1), Excel then treats it as a formula and I get the result I want. However, I have hundreds of rows and would like to evaluate the entire column in one shot.

Anyone have ideas on how to do this? Thanks a lot!!!

paul

[Tom Ogilvy]

This will do it. Assume column B:

Sub ReevaluateColumnB()
Columns(2).Replace _
What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

change the 2 to reflect the column to be processed.

--
Regards,
Tom Ogilvy





"tsuoying" <[email protected]> wrote in message
news:[email protected]...
>
> Hi, it's my first post here, i did a quick search on the forum but
> couldn't find what i was looking for, so hope someone can offer advice
> to the following problem:
>
> 1. I'm importing data into excel using a db query. One of the columns
> returned is a string that, if evaluated as a formula in Excel, would
> produce a cell with a hyperlink, i.e:
>
> =HYPERLINK("http://www.yahoo.com", "Yahoo Website")
>
> 2. The problem is that when i import the data, Excel shows the above
> exactly as is and doesn't treat it as a formula. This is fairly
> logical as Excel shouldn't know that that is what i want. So my
> question is, how do I program Excel now to look at this column, convert
> it from a constant to a formula, and then evaluate so it becomes just
> (actively linked):
>
> Yahoo Website
>
> and thus allows me to click on that cell to get to the link.
>
> 3. If I went into the cell individually and pressed enter at the end
> of the string (in step 1), Excel then treats it as a formula and I get
> the result I want. However, I have hundreds of rows and would like to
> evaluate the entire column in one shot.
>
> Anyone have ideas on how to do this? Thanks a lot!!!
>
> paul
>
>
> --
> tsuoying
> ------------------------------------------------------------------------
> tsuoying's Profile:
http://www.excelforum.com/member.php...o&userid=20110
> View this thread: http://www.excelforum.com/showthread...hreadid=346810
>

[tsuoying]

Tom, thank you for that script, it works perfectly!

Another question.... is it possible to make the result look like a link, instead of just text? After I run the function, the cell contents are still black, not the 'expected' blue. It's still a link but could be made more intuitive by changing its color. Any way to do that? Thanks again!

paul

[Tom Ogilvy]

Sub ReevaluateColumnB()
Columns(2).Replace _
What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Columns(2).specialCells(xlFormulas).Font.ColorIndex = 5
End Sub

--
Regards,
Tom Ogilvy


"tsuoying" <[email protected]> wrote in message
news:[email protected]...
>
> Tom, thank you for that script, it works perfectly!
>
> Another question.... is it possible to make the result look like a
> link, instead of just text? After I run the function, the cell
> contents are still black, not the 'expected' blue. It's still a link
> but could be made more intuitive by changing its color. Any way to do
> that? Thanks again!
>
> paul
>
>
> --
> tsuoying
> ------------------------------------------------------------------------
> tsuoying's Profile:
http://www.excelforum.com/member.php...o&userid=20110
> View this thread: http://www.excelforum.com/showthread...hreadid=346810
>