Quote within a formula VBA

[HWL]

okay, a real simple way to insert code using VBA is done like this:

sheet1.range("a1").formula = "=b1+c1"

However I need to do something more complex that requires double quotes.

Cells(curcnt, 6).Formula = "=hyperlink(chr(34) & [URL]http://www.google.com/finance/converter?a=1&from[/URL]= & chr(34) & UCase(Application.Trim(Cells(x, 5))) & chr(34) & &to=USD & chr(34), chr(34) & UCase(Application.Trim(Cells(x, 5))) & chr(34))"

This is supposed to make a hyperlink to google's currency conversion link for a dynamic currency to USD. For example, EUR to USD.

For testing purposes see:
http://www.google.com/finance/converter?a=1&from=EUR&to=USD

I thought I was on the right track with chr(34) but still get errors.
Any help is greatly appreciated.
Excel 2007

 

[Jerry Sullivan]

Hi HWL,

For your primary question about entering quotes within a VBA string, the easiest way to do that is to double up the quotes. VBA interprets two consecutive quotes as a single literal quote.

The process of writing a formula to a VBA String is usually easier if you start with a formula that is working correctly in a worksheet, then convert it to a VBA-acceptable string.

For your example if we have the string: eur in Cell E18, we can put this formula in Cell F18 and it will create the hyperlink you want....

=HYPERLINK("http://www.google.com/finance/converter?a=1&from="
    & UPPER(TRIM($E18)) & "&to=USD",UPPER(TRIM($E18)))

Translating that to a comparable VBA statement might look like this....

Dim curcnt As Long
curcnt = 18
Cells(curcnt, 6).FormulaR1C1 = _
"=HYPERLINK(""http://www.google.com/finance/converter?a=1&from="" & UPPER(TRIM(RC5)) & ""&to=USD"",UPPER(TRIM(RC5)))"

 

[HWL]

Hi HWL,

For your primary question about entering quotes within a VBA string, the easiest way to do that is to double up the quotes. VBA interprets two consecutive quotes as a single literal quote.

The process of writing a formula to a VBA String is usually easier if you start with a formula that is working correctly in a worksheet, then convert it to a VBA-acceptable string.

For your example if we have the string: eur in Cell E18, we can put this formula in Cell F18 and it will create the hyperlink you want....

=HYPERLINK("http://www.google.com/finance/converter?a=1&from="
    & UPPER(TRIM($E18)) & "&to=USD",UPPER(TRIM($E18)))

Translating that to a comparable VBA statement might look like this....

Dim curcnt As Long
curcnt = 18
Cells(curcnt, 6).FormulaR1C1 = _
"=HYPERLINK(""http://www.google.com/finance/converter?a=1&from="" & UPPER(TRIM(RC5)) & ""&to=USD"",UPPER(TRIM(RC5)))"

Thanks Jerry, I think it is almost there. But where you have UPPER(TRIM(RC5))) I need the actual cur code, such as EUR and that is being generated from the loop/cycle -- the (x,5).

 

[HWL]

SOLUTION:

Okay here was the solution, works perfectly -- thanks to Jerry pointing me in the right direction.

hyplk = "=hyperlink(" & Chr(34) & "http://www.google.com/finance/converter?a=1&from=" & UCase(Application.Trim(Cells(x, 5))) & "&to=USD" & Chr(34) & "," & Chr(34) & UCase(Application.Trim(Cells(x, 5))) & Chr(34) & ")"
Cells(curcnt, 6).Formula = hyplk

 

[Jerry Sullivan]

Good to see you figured out a solution.

Just to clarify two of the differences....

The code I suggested was intended to provide a reference to the Cell in Column E of the same Row instead of placing that Value into the formula. This would be more dynamic if you want to be able to change the values in Column E and have the hyperlinks automatically update. The formula you arrived at would be better if you want the ability to delete the values in Column E and have the hyperlinks continue to function.

The use of the Range.FormulaR1C1= Property allows you to apply a formula to a Range in a single statement using Absolute and Relative references instead of having to step through each Row to build an A1 formula string to be used by Range.Formula=