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

K

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 :D

-K
 
T

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
 
C

Charlie

Try this:

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

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
 
C

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 & ")"
 
K

Kremti

Bernie said:
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
 
K

Kremti

Charlie said:
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
 
B

Bernie Deitrick

Kremti,

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

HTH,
Bernie
MS Excel MVP


Kremti said:
Bernie said:
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
 
K

Kremti

Toppers said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top