Using a variable in a formula

[Fred]

Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I need to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month, the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred

 

[Don Guillett]

Not tested but something like

Dim nws as string
For Each x In activeworkbook.Worksheets
x.Range("AA1:AA1").Formula = "='F:\[Contacts 2008.xls]" & x.name &
"'!$A1"
Next x

 

[Rick Rothstein \(MVP - VB\)]

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you want it
to be a variable, it must be concatenated onto the other text parts so that
it is not inside a pair of quote marks.

Rick

 

[Fred]

Thanks Rick. Works great with quote marks, doesn't work without them.

Fred

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you want it
to be a variable, it must be concatenated onto the other text parts so that
it is not inside a pair of quote marks.

Rick

Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month, the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred

 

[Rick Rothstein \(MVP - VB\)]

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick

Thanks Rick. Works great with quote marks, doesn't work without them.

Fred

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you want
it
to be a variable, it must be concatenated onto the other text parts so
that
it is not inside a pair of quote marks.

Rick

Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each
month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I
need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read
the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month,
the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference
the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred

 

[Fred]

I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the ampersands,
then it prompts me for the workbook to use each time. If I leave all quotes
in, it works, although it is slow.

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick

Thanks Rick. Works great with quote marks, doesn't work without them.

Fred

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you want
it
to be a variable, it must be concatenated onto the other text parts so
that
it is not inside a pair of quote marks.

Rick



Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each
month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I
need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read
the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month,
the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference
the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred

 

[Rick Rothstein \(MVP - VB\)]

Okay, then the code I gave you is working... I wasn't completely sure what
you are saying in your last posting. As for the quote marks... yes, the
quote marks, as I listed them, are required by syntax... you can't remove or
modify them as they delineate the non-changing portions of your text.

Rick

I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the
ampersands,
then it prompts me for the workbook to use each time. If I leave all
quotes
in, it works, although it is slow.

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick

Thanks Rick. Works great with quote marks, doesn't work without them.

Fred


:

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you
want
it
to be a variable, it must be concatenated onto the other text parts so
that
it is not inside a pair of quote marks.

Rick



Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each
month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I
need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to
read
the
info in cell A1 on the Jan 08 Contacts worksheet. For the next
month,
the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference
the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred

 

[Fred]

Yes, it is working. Thank you for your help.
Fred

Okay, then the code I gave you is working... I wasn't completely sure what
you are saying in your last posting. As for the quote marks... yes, the
quote marks, as I listed them, are required by syntax... you can't remove or
modify them as they delineate the non-changing portions of your text.

Rick

I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the
ampersands,
then it prompts me for the workbook to use each time. If I leave all
quotes
in, it works, although it is slow.

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick


Thanks Rick. Works great with quote marks, doesn't work without them.

Fred


:

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you
want
it
to be a variable, it must be concatenated onto the other text parts so
that
it is not inside a pair of quote marks.

Rick



Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each
month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I
need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to
read
the
info in cell A1 on the Jan 08 Contacts worksheet. For the next
month,
the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference
the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred