#REF when creating an Addin

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stewdizzle - 04 Jan 2007 02:19 GMT

I am creating and addin that when opened will copy itself into an
existing workbook. From there the copied worksheet is supposed to pull
data from another worksheet and allow the user to manipulate it. I
created the worksheet in the same workbook that it will be used in.
Made sure all the formulas worked as desired and that everything was
the way i wanted it to be. However, when I copy the addin worksheet to
a new workbook to actually make it an addin I loose all my references.
How do I change the formulas so that when the worksheet is copied into
the existing workbook it references the cells properly.

Reply to this Message

Niek Otten - 04 Jan 2007 06:04 GMT

Post your code and the type of formulas.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am creating and addin that when opened will copy itself into an
| existing workbook. From there the copied worksheet is supposed to pull
[quoted text clipped - 5 lines]
| How do I change the formulas so that when the worksheet is copied into
| the existing workbook it references the cells properly.

Reply to this Message

stewdizzle - 05 Jan 2007 11:38 GMT

The existing workbook contains two sheets Sabourin and Jagiello. The
addin file contains a sheet called What If. The addin file contains
formulas that directly refrence the data in "Jagiello". Here is the
code that I use to copy the addin to the existing workbook:

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy
after:=ActiveWorkbook.Sheets("jagiello")
End Sub

This process works fine. It is when the sheet copies I can't get it to
refrence the sheet "Jagiello". It appears to be trying to lookback at
the addin file. Here is a sample of one of my folmulas.

=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))

#REF pops up in place of Jagiello!(cell#)

Let me know if you need anything else.

PS Sorry about the double post.

> Post your code and the type of formulas.
>
[quoted text clipped - 12 lines]
> | How do I change the formulas so that when the worksheet is copied into
> | the existing workbook it references the cells properly.

Reply to this Message

Dave Peterson - 05 Jan 2007 12:40 GMT

Maybe you could try:

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
application.calculate
End Sub

If that doesn't work...

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
activesheet.usedrange.replace what:="=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
End Sub

And if that doesn't work...

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
application.wait now + timeserial(0,0,2) 'wait a couple of seconds
activesheet.usedrange.replace what:="=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
End Sub

All untested and all uncompiled--watch for typos.

> The existing workbook contains two sheets Sabourin and Jagiello. The
> addin file contains a sheet called What If. The addin file contains
[quoted text clipped - 34 lines]
> > | How do I change the formulas so that when the worksheet is copied into
> > | the existing workbook it references the cells properly.

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 05 Jan 2007 12:44 GMT

Ignore that other post.

If you're copying a worksheet from the addin that points at a sheet in the
addin, then the copied worksheet will still point at the addin.

I'd try:

Sub Auto_Open()
with thisworkbook.sheets("what if")
'change all the formulas to text
.usedrange.replace what:="=",replacement:="$$$$$=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
'copy it
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
'change them back
.usedrange.replace what:="$$$$$=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false

'fix the new sheet
activesheet.usedrange.replace what:="$$$$$=",replacement:="=", lookat:=xlpart,
_
searchorder:=xlbyrows, matchcase:=false
End Sub

Signature

Dave Peterson

Reply to this Message

Niek Otten - 05 Jan 2007 12:57 GMT

I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
Or use formulas without an equals-sign, and add that after copying and pasting, but I don't feel very comfortable with that last
solution.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| The existing workbook contains two sheets Sabourin and Jagiello. The
| addin file contains a sheet called What If. The addin file contains
[quoted text clipped - 9 lines]
| refrence the sheet "Jagiello". It appears to be trying to lookback at
| the addin file. Here is a sample of one of my folmulas.

=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))

Reply to this Message

Niek Otten - 05 Jan 2007 14:32 GMT

<someone more knowledgeable>

Of course I meant "than me"; not Dave or anyone else who responded!

Niek

|I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
| If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
[quoted text clipped - 14 lines]
|| refrence the sheet "Jagiello". It appears to be trying to lookback at
|| the addin file. Here is a sample of one of my folmulas.

=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))

|| #REF pops up in place of Jagiello!(cell#)
||
[quoted text clipped - 19 lines]
|| > | How do I change the formulas so that when the worksheet is copied into
|| > | the existing workbook it references the cells properly.

Reply to this Message

Dave Peterson - 05 Jan 2007 14:47 GMT

I like a variation of the last one.

<bg>

> I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
> If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
[quoted text clipped - 46 lines]
> | > | How do I change the formulas so that when the worksheet is copied into
> | > | the existing workbook it references the cells properly.

Signature

Dave Peterson

Reply to this Message

stewdizzle - 05 Jan 2007 20:22 GMT

Dave you are a lifesaver. I don't know why I didn't try this earlier.
I was doing it manually to see if it would work just couldn't tie up
the loose ends. For reference Here is the final code.

Sub Auto_Open()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("What If")
'copy it
ThisWorkbook.Sheets("What If").Copy
after:=ActiveWorkbook.Sheets("Jagiello")
'fix the new sheet
Sheets("what if").Cells.Replace what:="$$$", replacement:="=",
lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Application.ScreenUpdating = True
End Sub

I just saved my addin with "$$$" in the place of "=" And eliminated the
step of changing it and then fixing it.

In short, thank you Dave and Niek for the help.

> I like a variation of the last one.
>
[quoted text clipped - 54 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 05 Jan 2007 20:59 GMT

That's the way I do it, too (save the formulas as text).

(I do use $$$$$=, though. It's just a bit more unique <vbg>.)

> Dave you are a lifesaver. I don't know why I didn't try this earlier.
> I was doing it manually to see if it would work just couldn't tie up
[quoted text clipped - 78 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Niek Otten - 05 Jan 2007 21:18 GMT

<I like a variation of the last one.>

OK!
I'm not sure I can oversee all the implications, like opening in a version of Excel with other list separators, R1C1 reference
style, etc. As long as they're recognized as formulas, that will be taken care of automatically, but with text, no. I even don't
know what happens in another language version. I do with worksheets, but I don't with worksheets copied from an add-in.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))

| > | #REF pops up in place of Jagiello!(cell#)
| > |
[quoted text clipped - 19 lines]
| > | > | How do I change the formulas so that when the worksheet is copied into
| > | > | the existing workbook it references the cells properly.

Reply to this Message

Dave Peterson - 05 Jan 2007 22:27 GMT

I haven't done any testing on this, but I would think that if the formulas are
kept as formulas, then converted to text, then back to formulas (not what the OP
ended up doing), I don't think that the R1C1/list separators would cause a
problem (since each workbook would be using excel's application settings).

But it does make for a good reason to keep the original formulas as formulas and
do that extra couple of steps.

> <I like a variation of the last one.>
>
[quoted text clipped - 68 lines]
> |
> | Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: