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

[Kremti]

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

-K

 

[Toppers]

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

 

[Charlie]

Try this:

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

 

[Bernie Deitrick]

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

 

[Charlie]

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]

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

 

[Kremti]

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

 

[Bernie Deitrick]

Kremti,

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

HTH,
Bernie
MS Excel MVP

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

 

[Kremti]

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