Argh, trying to put " and & into the formula is driving me nuts!

So, this is the formula I'd like to put into cells:

=HYPERLINK("[combined.xls]Sheet1!N"&TEXT(MATCH(dStyle,C1:C294,0),"0"),
Notes)

Where dStyle and Notes are String Variables defined in the VBA.

That formula itself is working fine if I type it in myself into the
cells by hand (dStyle and Notes are picked out by hand from the
existing cells), but I'm trying to automate and write a macro to do all
of them at once. But when I try to go

Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 = "=HYPERLINK ...

I can't seem to get the " and & straight. I know I'm suppose to go ""
and && if I want to see single " and & inside the formula, but I also
need to add " and & to include dStyle and Notes into the formula, it
all gets messed up.

This has been a bane of my existance. Any help would be appreciated :D

-K

Try this:

Sub Test()

Dim MyForm As String

Const MyFormula As String = "=HYPERLINK(""[combined.xls]Sheet1!N"" &
TEXT(MATCH(dStyle,C1:C294,0),""0""),Notes)"

MyForm = Replace(MyFormula, "dStyle", "A2") ' Replace dStyle with cell
reference
MyForm = Replace(MyForm, "Notes", "A3") ' Replace Notes with Cell Reference

Range("A1").Formula = MyForm
End Sub

HTH

"Kremti" wrote:

> So, this is the formula I'd like to put into cells:
>
> =HYPERLINK("[combined.xls]Sheet1!N"&TEXT(MATCH(dStyle,C1:C294,0),"0"),
> Notes)
>
> Where dStyle and Notes are String Variables defined in the VBA.
>
> That formula itself is working fine if I type it in myself into the
> cells by hand (dStyle and Notes are picked out by hand from the
> existing cells), but I'm trying to automate and write a macro to do all
> of them at once. But when I try to go
>
> Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 = "=HYPERLINK ...
>
> I can't seem to get the " and & straight. I know I'm suppose to go ""
> and && if I want to see single " and & inside the formula, but I also
> need to add " and & to include dStyle and Notes into the formula, it
> all gets messed up.
>
> This has been a bane of my existance. Any help would be appreciated :D
>
> -K
>
>

Try this:

Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 =
"=HYPERLINK(""[combined.xls]Sheet1!N""" & "TEXT(MATCH(" & dStyle &
",C1:C294,0),""0"")," & Notes & ")"


"Kremti" wrote:

> So, this is the formula I'd like to put into cells:
>
> =HYPERLINK("[combined.xls]Sheet1!N"&TEXT(MATCH(dStyle,C1:C294,0),"0"),
> Notes)
>
> Where dStyle and Notes are String Variables defined in the VBA.
>
> That formula itself is working fine if I type it in myself into the
> cells by hand (dStyle and Notes are picked out by hand from the
> existing cells), but I'm trying to automate and write a macro to do all
> of them at once. But when I try to go
>
> Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 = "=HYPERLINK ...
>
> I can't seem to get the " and & straight. I know I'm suppose to go ""
> and && if I want to see single " and & inside the formula, but I also
> need to add " and & to include dStyle and Notes into the formula, it
> all gets messed up.
>
> This has been a bane of my existance. Any help would be appreciated :D
>
> -K
>
>

K,

It can get a bit hairy.... but you came to the right place for bane removal:

Worksheets("Sheet1").Cells(i, 14).Formula = _
"=HYPERLINK(""[combined.xls]Sheet1!N"" & " & _
"TEXT(MATCH(""" & dStyle & """,C1:C294,0),""0"")" & _
",""" & Notes & """)"

HTH,
Bernie
MS Excel MVP


"Kremti" <[email protected]> wrote in message
news:[email protected]...
> So, this is the formula I'd like to put into cells:
>
> =HYPERLINK("[combined.xls]Sheet1!N"&TEXT(MATCH(dStyle,C1:C294,0),"0"),
> Notes)
>
> Where dStyle and Notes are String Variables defined in the VBA.
>
> That formula itself is working fine if I type it in myself into the
> cells by hand (dStyle and Notes are picked out by hand from the
> existing cells), but I'm trying to automate and write a macro to do all
> of them at once. But when I try to go
>
> Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 = "=HYPERLINK ...
>
> I can't seem to get the " and & straight. I know I'm suppose to go ""
> and && if I want to see single " and & inside the formula, but I also
> need to add " and & to include dStyle and Notes into the formula, it
> all gets messed up.
>
> This has been a bane of my existance. Any help would be appreciated :D
>
> -K
>

Sorry, I always post too fast, I think I got the &TEXT(MATCH part wrong...
how about this:

Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 =
"=HYPERLINK(""[combined.xls]Sheet1!N""&TEXT(MATCH(" & dStyle &
",C1:C294,0),""0"")," & Notes & ")"


"Kremti" wrote:

> So, this is the formula I'd like to put into cells:
>
> =HYPERLINK("[combined.xls]Sheet1!N"&TEXT(MATCH(dStyle,C1:C294,0),"0"),
> Notes)
>
> Where dStyle and Notes are String Variables defined in the VBA.
>
> That formula itself is working fine if I type it in myself into the
> cells by hand (dStyle and Notes are picked out by hand from the
> existing cells), but I'm trying to automate and write a macro to do all
> of them at once. But when I try to go
>
> Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 = "=HYPERLINK ...
>
> I can't seem to get the " and & straight. I know I'm suppose to go ""
> and && if I want to see single " and & inside the formula, but I also
> need to add " and & to include dStyle and Notes into the formula, it
> all gets messed up.
>
> This has been a bane of my existance. Any help would be appreciated :D
>
> -K
>
>

Bernie Deitrick wrote:
> K,
>
> It can get a bit hairy.... but you came to the right place for bane
removal:
>
> Worksheets("Sheet1").Cells(i, 14).Formula = _
> "=HYPERLINK(""[combined.xls]Sheet1!N"" & " & _
> "TEXT(MATCH(""" & dStyle & """,C1:C294,0),""0"")" & _
> ",""" & Notes & """)"
>
> HTH,
> Bernie
> MS Excel MVP

Odd, from google group, my original post took hours to show up.

Bernie,

That didn't work. Thanks for trying though. From my point of view,
yours 'look' right to me.

None of the above help didn't work either :/

-K

Charlie wrote:
> Sorry, I always post too fast, I think I got the &TEXT(MATCH part
wrong...
> how about this:
>
> Worksheets("Sheet1").Cells(i, 14).FormulaR1C1 =
> "=HYPERLINK(""[combined.xls]Sheet1!N""&TEXT(MATCH(" & dStyle &
> ",C1:C294,0),""0"")," & Notes & ")"

Hmm, this one didn't work either. Thanks for the help though.

Note that in the formula, string Notes must be surrounded by " so there
has to be buncha "" and possibly """ around Notes there...

-K

Kremti,

It worked correctly for me. I can send you a working example if you want.

HTH,
Bernie
MS Excel MVP


"Kremti" <[email protected]> wrote in message
news:[email protected]...
> Bernie Deitrick wrote:
> > K,
> >
> > It can get a bit hairy.... but you came to the right place for bane
> removal:
> >
> > Worksheets("Sheet1").Cells(i, 14).Formula = _
> > "=HYPERLINK(""[combined.xls]Sheet1!N"" & " & _
> > "TEXT(MATCH(""" & dStyle & """,C1:C294,0),""0"")" & _
> > ",""" & Notes & """)"
> >
> > HTH,
> > Bernie
> > MS Excel MVP
>
> Odd, from google group, my original post took hours to show up.
>
> Bernie,
>
> That didn't work. Thanks for trying though. From my point of view,
> yours 'look' right to me.
>
> None of the above help didn't work either :/
>
> -K
>

Toppers wrote:
> Try this:
>
> Sub Test()
>
> Dim MyForm As String
>
> Const MyFormula As String = "=HYPERLINK(""[combined.xls]Sheet1!N"" &
> TEXT(MATCH(dStyle,C1:C294,0),""0""),Notes)"
>
> MyForm = Replace(MyFormula, "dStyle", "A2") ' Replace dStyle with
cell
> reference
> MyForm = Replace(MyForm, "Notes", "A3") ' Replace Notes with Cell
Reference
>
> Range("A1").Formula = MyForm
> End Sub
>
> HTH
Sorry Topper, this one didn't work either. Note that dStyle and Notes
are not cell references, but they are strings extracted from cells. I
tried:

Dim Notes As String
Dim dStyle As String
Dim MyForm As String
Const MyFormula As String = "=HYPERLINK(""[combined.xls]Sheet1!N""
_
& TEXT(MATCH(dS,C1:C294,0),""0""),Ns)"

MyForm = Replace(MyFormula, "dS", """" & dStyle & """")
MyForm = Replace(MyForm, "Ns", """" & Notes & """")

Worksheets("Sheet1").Cells(i, 14).Formula = MyForm

But that didn't work either :/

-K